CODEWITHSUNDEEP
sqldb2
Matthew David Jankowski
Matthew David Jankowski

Reputation: 904

Use Local Variables with a DB2 Query?

I am trying to write a DB2 query that makes use of local variables. I really just want the query to be easier to maintain and run.

Here is the base query:

WITH MYTAB (CustomerId, VisitID, Sale, DateTime) 
AS
(
VALUES
  (1, 1, 'Y', '2021-04-23 20:16:00'::TIMESTAMP)
, 
(1, 3, 'N', '2021-04-23 21:16:00'::TIMESTAMP)
, 
(2, 2, 'N', '2021-04-24 20:16:00'::TIMESTAMP)
, 
(2, 4, 'Y', '2021-04-25 20:16:00'::TIMESTAMP)
, 
(2, 6, 'N', '2021-04-25 23:16:00'::TIMESTAMP)
, 
(3, 5, 'Y', '2021-04-23 20:16:00'::TIMESTAMP)
, (
3, 7, 'N', '2021-05-23 20:16:00'::TIMESTAMP)
)



SELECT *

FROM MYTAB A
 
WHERE DateTime > '2021-04-24'

How would I write this same statement making use of variables? The following won't run but give the idea:

WITH MYTAB (CustomerId, VisitID, Sale, DateTime) 
AS
(
VALUES
  (1, 1, 'Y', '2021-04-23 20:16:00'::TIMESTAMP)
, 
(1, 3, 'N', '2021-04-23 21:16:00'::TIMESTAMP)
, 
(2, 2, 'N', '2021-04-24 20:16:00'::TIMESTAMP)
, 
(2, 4, 'Y', '2021-04-25 20:16:00'::TIMESTAMP)
, 
(2, 6, 'N', '2021-04-25 23:16:00'::TIMESTAMP)
, 
(3, 5, 'Y', '2021-04-23 20:16:00'::TIMESTAMP)
,
(3, 7, 'N', '2021-05-23 20:16:00'::TIMESTAMP)
)



WITH VAR_START(STARTDTTM) AS (VALUES(DATE('2021-01-01')))



SELECT *

FROM MYTAB A,VAR_START

WHERE DateTime > STARTDTTM

Upvotes: 0

Views: 633

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Not sure if that is what you are asking but you can have several CTE's in one query:

WITH MYTAB (CustomerId, VisitID, Sale, DateTime) AS
 ( 
     VALUES
 (1, 1, 'Y', '2021-04-23 20:16:00'::TIMESTAMP)
    
         ,  (1, 3, 'N', '2021-04-23 21:16:00'::TIMESTAMP) 
         ,  (2, 2, 'N', '2021-04-24 20:16:00'::TIMESTAMP)
) ,  VAR_START(STARTDTTM) AS ( 
     VALUES (DATE('2021-01-01') )
)
    
    
SELECT *
 
FROM MYTAB A, VAR_START
    
WHERE DateTime > STARTDTTM

If you want to use variables you can use an anonymous block or a stored procedure. Yet another option is to use a function that returns a table and takes a parameter as an argument.

Upvotes: 1

Related Questions