How to address a variable within a WITH statement
(1) By jose isaias cabrera (jicman) on 2021-11-16 18:58:07 [link] [source]
Greetings.
I have this WITH Statement,
WITH LastEntries (ProjID, ml_insert)
AS
(
SELECT 'PR0000019191' AS PID,
(
SELECT max(insertdate) FROM Project_Keytask_and_Milestones where projid = 'PR0000019191'
)
)
SELECT ProjID, ml_insert from LastEntries;
Which works, but I would like to use the first value like this,
WITH LastEntries (ProjID, ml_insert)
AS
(
SELECT 'PR0000019191' AS PID,
(
SELECT max(insertdate) FROM Project_Keytask_and_Milestones where projid = PID
)
)
SELECT ProjID, ml_insert from LastEntries;
But, it does not work. Any way to do this, or do I have to use the value? Thanks.
josé
(2) By Harald Hanche-Olsen (hanche) on 2021-11-16 19:17:34 in reply to 1 [link] [source]
I imagine something along the lines of
WITH
pi (projid) AS (VALUES('PR0000019191')),
LastEntries(projid,ml_insert)
AS (
SELECT projid max(insertdate)
FROM pi
JOIN Project_Keytask_and_Milestones
USING(projid)
)
SELECT ...;
might do the trick?
(3) By jose isaias cabrera (jicman) on 2021-11-16 20:12:45 in reply to 2 [source]
Thanks. This would be too complicated as there are other pieces and tables to be added to the WITH piece of the SQL code. I guess I will have to use the value instead. Thanks again.
josé