SQLite Forum

How to address a variable within a WITH statement
Login

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é