How To Get My Teradata Query to Prompt For Dates Between ?YYYYMMDD AND ?YYYYMMDD and

Question

HERE IS THE QUERY: SELECT

FACDTE.QTR
,FACDTE.WK
,FACDTE."DATE"
,FACDTE.DIV
,FACDTE.DST
,FACDTE.FAC
,FACDTE.DAYS
,COALESCE(OOS.SCN,0) SCN
,RANK() OVER(
            PARTITION BY FACDTE.QTR,FACDTE.WK,FACDTE."DATE"
            ORDER BY  COALESCE (CAST(OOS."OOS COUNT" AS INTEGER),'ns')) AS    DIVRANK
,CAST(OOS."OOS COUNT" AS INTEGER) "OOS COUNT"

FROM 
(
SELECT 
FAC.PARENT_OP_AREA_CD DIV
,FAC.DISTRICT_FINANCE_CD DST
,FAC.STORE_ID FAC
,DTE.QUARTER_ID QTR
,DTE.WEEK_ID WK
,DTE.D_DATE "DATE"
,COUNT(DISTINCT(DTE.D_DATE)) DAYS

FROM LU_STORE_FINANCE FAC
JOIN (
            SELECT 
            DTE_L1.D_DATE
            ,DTE_L1.WEEK_ID
            ,DTE_L1.QUARTER_ID
            FROM LU_DAY_MERGE DTE_L1
            JOIN (SELECT D_DATE  FROM LU_DAY_MERGE 
            WHERE WEEK_ID=(SELECT DISTINCT(WEEK_ID) FROM LU_DAY_MERGE WHERE D_DATE =CURRENT_DATE -2)) DTE_L0 ON DTE_L0.D_DATE =DTE_L1.D_DATE AND DTE_L1.D_DATE < CURRENT_DATE 
            ) DTE
ON DTE.D_DATE BETWEEN FAC.OPENED_DT AND  FAC.CLOSED_DT AND

THE CODE IS BELOW I NEED TO PROMPT FOR DATES BETWEEN YYYYMMDD YYYYMMDD INSTEAD of Using the current_DATE... Can I just put in ?YYYYMMDD AND YYYYMMDD in that please?

FAC.PARENT_OP_AREA_CD = '17' AND NOT FAC.STORE_ID IN (4904,3332, 1478,0412,2631,1223) GROUP BY 1,2,3,4,5,6) FACDTE

LEFT JOIN 

(SELECT 
OOS_L1.STORE_ID  FAC
,DTE_L2.WEEK_ID WK
,DTE_L2.D_DATE "DATE"
,AVERAGE(TOT_CT.TOT_OOS) AS  "OOS COUNT"
,COUNT(DISTINCT(OOS_L1.SCAN_DT)) SCN

,SUM(CASE WHEN OOS_L1.PRODUCT_SOURCE_CD = 'W' THEN CASE WHEN ITM.DEPARTMENT_ID = 314 THEN OOS_L1.OOS_STR_SCAN_CNT ELSE 0 END ELSE 0 END) AS "DAIRY"


FROM 
OOS_STORE_ITEM_DAY OOS_L1
JOIN (SELECT STORE_ID,SCAN_DT ,COUNT(UPC_ID) AS TOT_OOS FROM OOS_ITEM_DETAIL WHERE SCAN_TYP_CD = 'O' GROUP BY 1,2)TOT_CT 
ON TOT_CT.STORE_ID = OOS_L1.STORE_ID AND TOT_CT.SCAN_DT=OOS_L1.SCAN_DT AND     TOT_CT.TOT_OOS>49
JOIN LU_DAY_MERGE DTE_L2 ON OOS_L1.SCAN_DT=DTE_L2.D_DATE
JOIN LU_UPC ITM ON          OOS_L1.UPC_ID = ITM.UPC_ID AND     ITM.CORPORATION_ID = 1
GROUP BY 1,2,3) OOS
ON OOS.WK = FACDTE.WK AND OOS.FAC=FACDTE.FAC AND OOS."DATE" = FACDTE."DATE"

ORDER BY 1,2,3,4,5,6

What do you mean by prompt here? Possible duplicate question stackoverflow.com/a/7324597/3114457 — Livius
– Livius, Commented Jun 27, 2018 at 6:15

hhoeck · Accepted Answer · 2018-06-27 12:05:15Z

0

Not saure, what the question is. But if you're talking about the feature in Teradata SQL Assistant to place parameters (with the ?-Syntax):

You can define parameters in SQL Assistant with Keyword ?. Just place ? with the parameter name (no spaces) anywhere in your Editor. SQL Assistant will ask for each parameter and search&replace it before the query is send to the database. The parameters can occur multiple times in the editor, you just have to enter the value once and it will be replaced each occurrence.

SELECT *
  FROM myWebOrders
 where orderDate >= CURRENT_DATE - ?OrderHistory_Days
UNION ALL
SELECT *
  FROM myRetailOrders
 where orderDate >= CURRENT_DATE - ?OrderHistory_Days

or

SELECT TOP 50 *
  FROM ?myTable

answered Jun 27, 2018 at 12:05

hhoeck

3712 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How To Get My Teradata Query to Prompt For Dates Between ?YYYYMMDD AND ?YYYYMMDD and

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related