变量由用户在运行查询时设置，Teradata

Question

我有一个查询，检查是否至少有一行存在于三个表a,B和C中，我想检查theDate=myDate。我希望能够看到表是否包含某个日期(myDate)的数据。我希望变量myDate在运行查询时由用户选择。我怎么做呢?仅供参考:我正在使用Teradata:

查询:

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

我认为动态过程/宏将最适合您的要求。编译下面的程序:

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;

在call语句后面输入你的日期(作为输入参数)。输出将是您想要的选择查询的结果。

如果你使用宏，下面的代码会给你输出:

        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);
       );

谢谢

Answer 2

您可以在单行易变表中删除"变量"，并在查询中连接到该表:

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

我发现了一个在我看来很简单的解决方案。? date是一个由用户在运行查询之前设置的变量。(对我来说，其他解决方案的问题是，我没有权限创建存储过程或宏)

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)