|
Вставить в формулу расчета даты значение из другой таблицы.
#39141869
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Имеется три таблицы:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
-- Table: Period
CREATE TABLE Period (
Id serial,
IdKeys INTEGER,
mDate DATE
);
INSERT INTO Period (Id, IdKeys, mDate) VALUES (50, 43, '2012-08-01 18:13:40');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (51, 44, '2013-06-07 18:14:54');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (54, 46, '2011-12-07 09:06:00');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (56, 47, '2013-05-14 09:07:21');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (59, 50, '2012-05-28 09:52:46');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (80, 71, '2013-10-16 23:27:33');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (82, 73, '2012-12-19 23:44:51');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (83, 74, '2012-10-30 23:49:49');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (84, 74, '2013-10-20 00:00:16');
INSERT INTO Period (Id, IdKeys, mDate) VALUES (85, 74, '2012-10-30 00:01:10');
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
-- Table: Project
CREATE TABLE Project (
Id serial,
Validity INT
);
INSERT INTO Project (Id, Validity) VALUES (26, 15);
INSERT INTO Project (Id, Validity) VALUES (27, 24);
INSERT INTO Project (Id, Validity) VALUES (28, 15);
INSERT INTO Project (Id, Validity) VALUES (29, 12);
INSERT INTO Project (Id, Validity) VALUES (30, 12);
INSERT INTO Project (Id, Validity) VALUES (31, 12);
INSERT INTO Project (Id, Validity) VALUES (32, 12);
INSERT INTO Project (Id, Validity) VALUES (33, 12);
INSERT INTO Project (Id, Validity) VALUES (34, 12);
INSERT INTO Project (Id, Validity) VALUES (35, 15);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
-- Table: Keys
CREATE TABLE Keys (
Id serial,
IdPro INTEGER
);
INSERT INTO Keys (Id, IdPro) VALUES (43, 26);
INSERT INTO Keys (Id, IdPro) VALUES (44, 26);
INSERT INTO Keys (Id, IdPro) VALUES (46, 27);
INSERT INTO Keys (Id, IdPro) VALUES (47, 27);
INSERT INTO Keys (Id, IdPro) VALUES (50, 27);
INSERT INTO Keys (Id, IdPro) VALUES (71, 26);
INSERT INTO Keys (Id, IdPro) VALUES (73, 26);
INSERT INTO Keys (Id, IdPro) VALUES (74, 26);
INSERT INTO Keys (Id, IdPro) VALUES (76, 26);
INSERT INTO Keys (Id, IdPro) VALUES (78, 26);
Выполняется запрос:
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT Keys.Id AS Id,
period.mdate,
Project.Validity,
to_char(period.mdate + interval '1 month', 'YYYY.MM.DD') AS Newdate
FROM Keys
LEFT JOIN Period
ON ( Keys.Id = Period.IdKeys )
LEFT JOIN Project
ON ( Keys.IdPro = Project.Id )
Хотелось бы в строку: 1.
to_char(period.mdate + interval '1 month', 'YYYY.MM.DD') AS Newdate
вместо значения '1 month' подставить значение из поля Project.Validity
Что то типа: 1.
to_char(period.mdate + interval & Project.Validity & ' month', 'YYYY.MM.DD') AS Newdate
Прошу помощи. |
|
|
