Variable set by user when running query, Teradata

Question

I have a query that checks if at least one row exist in the three tables A,B and C where I want to do the check that theDate=myDate. I want to be able to see if the tables contain data for a certain date (myDate). I want the variable myDate to be chosen by the user when running the query. How do I do that? FYI: I am using Teradata:

The query:

SELECT 'A' AS "Table", CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END AS "True?"
WHERE EXISTS (SELECT * FROM A WHERE theDate=myDate) 

UNION ALL

SELECT 'B', CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END
WHERE EXISTS (SELECT * FROM B WHERE theDate=myDate) 

UNION ALL

SELECT 'C', CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END
WHERE EXISTS (SELECT * FROM C WHERE theDate=myDate)

Answer 1

I think a dynamic procedure/Macro will best suite your requirement. Compile the below procedure :

REPLACE PROCEDURE your_proc (IN in_mydate DATE)
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE stmt_str VARCHAR(60000);
DECLARE RESPONSE CURSOR WITH RETURN ONLY TO CLIENT FOR STMT;

-----------------------------------------------------------------------------------------------
-------------------------------- PROGRAM LOGIC ------------------------------------------------
-----------------------------------------------------------------------------------------------

        SET stmt_str =          'SELECT ''A'' AS "Table", CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END AS "True?"'
                                ||'WHERE EXISTS (SELECT * FROM A WHERE theDate='''||in_mydate||''')' 
                                ||'UNION ALL'
                                ||'SELECT ''B'', CASE WHEN COUNT(*) = 1 THEN ''Y'' ELSE ''N'' END'
                                ||'WHERE EXISTS (SELECT * FROM B WHERE theDate='''||in_mydate||''') '
                                ||'UNION ALL'
                                ||'SELECT ''C'', CASE WHEN COUNT(*) = 1 THEN ''Y'' ELSE ''N'' END'
                                ||'WHERE EXISTS (SELECT * FROM C WHERE theDate='''||in_mydate||''');';



PREPARE STMT FROM stmt_str;
OPEN RESPONSE;
DEALLOCATE PREPARE STMT;

END;

After this in you call statement just put your date( as an input parameter ). The output will be the result of the select query you desire.

In case you are using a macro the below code would give you the output :

        REPLACE MACRO your_macro (mydate DATE)
        AS (
        SELECT 'A' AS "Table", CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END         AS "True?"
        WHERE EXISTS (SELECT * FROM A WHERE theDate=:myDate) 

        UNION ALL

        SELECT 'B', CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END
        WHERE EXISTS (SELECT * FROM B WHERE theDate=:myDate) 

        UNION ALL

        SELECT 'C', CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END
        WHERE EXISTS (SELECT * FROM C WHERE theDate=:myDate);
       );

Thanks

Answer 2

You can drop your "variable" in a single row volatile table, and join to that table in your query:

create volatile table varTable
as (select <your date value> as thedate)
with data
on commit preserve rows
;

SELECT 'A' AS "Table", 
CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END AS "True?"
from
a inner join vartable
  on a.<date column> = vartable.thedate

Answer 3

I found an in my opinion easy solution which work very well. ?theDate is a vaiable that is set by the user before running the query. (The problem for me with other solutions are that I don't have the access to create a stored procedure or macro)

SELECT 'A' AS "Table", CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END AS "True?"
WHERE EXISTS (SELECT * FROM A WHERE theDate=?theDate) 

UNION ALL

SELECT 'B', CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END
WHERE EXISTS (SELECT * FROM B WHERE theDate=?theDate) 

UNION ALL

SELECT 'C', CASE WHEN COUNT(*) = 1 THEN 'Y' ELSE 'N' END
WHERE EXISTS (SELECT * FROM C WHERE theDate=?theDate)