Volitile tables and Transactions

Teradata Debugger
Enthusiast

Volitile tables and Transactions

BEGIN
DECLARE MY_PLAN_ID1 INTEGER;
DECLARE MY_PLAN_ID2 INTEGER;
DECLARE MY_PLAN_ID3 INTEGER;
DECLARE MY_PLAN_ID4 INTEGER;
DECLARE MY_PLAN_ID5 INTEGER;

SET MY_PLAN_ID1 = THIS_PLAN_ID1;
SET MY_PLAN_ID2 = THIS_PLAN_ID2;
SET MY_PLAN_ID3 = THIS_PLAN_ID3;
SET MY_PLAN_ID4 = THIS_PLAN_ID4;
SET MY_PLAN_ID5 = THIS_PLAN_ID5;

--Volatile table to get continuous eligibility spans. This is more like a temporary table

CREATE VOLATILE TABLE ZJAC_ELIG AS
(
SELECT
PERIOD_IND
,MEMBERC_CK
,PCP_EDW_AFFILIATION_ID --this field is not in the other version of the report.
,AGE_YEARS
,BIRTH_DATE
,MIN(EFFECTIVE_DATE) AS EFFECTIVE_DATE
,MAX(END_DATE) AS END_DATE
,PLAN_ID
FROM
(
SELECT
Y.*
,CASE WHEN GAP_COUNTER > 0 THEN GAP_COUNTER
ELSE MAX(GAP_COUNTER) OVER (PARTITION BY MEMBERC_CK ORDER BY END_DATE ROWS UNBOUNDED PRECEDING) END AS PERIOD_IND
FROM
(
SELECT X.*
,CASE WHEN SPAN > 3 THEN
ROW_NUMBER() OVER (PARTITION BY MEMBERC_CK
ORDER BY END_DATE) ELSE 0 END AS GAP_COUNTER
FROM
(
SELECT
MEMBER.MEMBERC_CK
,CURR_ELIG.PCP_EDW_AFFILIATION_ID
,EFF.DATE_DATE AS EFFECTIVE_DATE
,END_DT.DATE_DATE AS END_DATE
,TRUNC(MONTHS_BETWEEN(TRUNC(CURRENT_DATE), TRUNC(DOB.DATE_DATE)) / 12) AS AGE_YEARS
,DOB.DATE_DATE AS BIRTH_DATE,
EFF.DATE_DATE - MIN(END_DT.DATE_DATE) OVER (PARTITION BY MEMBER.MEMBERC_CK
ORDER BY END_DT.DATE_DATE ROWS 1 PRECEDING) AS SPAN
,MEMBER.PLAN_ID
FROM DIM_MEM MEMBER
JOIN DIM_MEM_ELIGC CURR_ELIG
ON MEMBER.MEMBER_NBR_TRIM = CURR_ELIG.MEMBER_NBR_TRIM
AND MEMBER.PLAN_ID = CURR_ELIG.PLAN_ID
AND CURR_ELIG.DELETED_IND = 'N'
AND CURR_ELIG.ACTIVE_IND = 'Y'
JOIN DIM_MEM_ELIG_ELIGC ELIG
ON MEMBER.MEMBER_NBR_TRIM = ELIG.MEMBER_NBR_TRIM
AND MEMBER.PLAN_ID = ELIG.PLAN_ID
AND ELIG.DELETED_IND = 'N'
JOIN DIM_DATE EFF
ON EFF.DATE_ID = ELIG.ELIGIBILITY_EFF_DATE_ID
JOIN DIM_DATE END_DT
ON END_DT.DATE_ID = ELIG.ELIGIBILITY_END_DATE_ID
JOIN DIM_DATE DOB
ON DOB.DATE_ID = MEMBER.BIRTH_DATE_ID
AND DOB.DELETED_IND = 'N'
WHERE 1=1
AND MEMBER.PLAN_ID IN (:MY_PLAN_ID1, :MY_PLAN_ID2,:MY_PLAN_ID3,:MY_PLAN_ID4,:MY_PLAN_ID5)
--AND MEMBER.PLAN_ID = 112
---REMOVED AGE 20140106
--AND AGE_YEARS < 21
)X
)Y
)Z
GROUP BY
PERIOD_IND
,PCP_EDW_AFFILIATION_ID
,MEMBERC_CK
,AGE_YEARS
,BIRTH_DATE
,PLAN_ID
HAVING MAX(END_DATE) >= CURRENT_DATE
AND CURRENT_DATE >= MIN(EFFECTIVE_DATE)
)WITH DATA
UNIQUE PRIMARY INDEX (MEMBERC_CK,PLAN_ID,PCP_EDW_AFFILIATION_ID)
INDEX (MEMBERC_CK)
INDEX (PLAN_ID)
ON COMMIT PRESERVE ROWS;


COLLECT STATS ON ZJAC_ELIG INDEX (MEMBERC_CK,PLAN_ID,PCP_EDW_AFFILIATION_ID );
COLLECT STATS ON ZJAC_ELIG INDEX (MEMBERC_CK );
COLLECT STATS ON ZJAC_ELIG INDEX (PLAN_ID );

--Get last screening date.
CREATE VOLATILE TABLE ZJA_LAST_SCREENS AS
(
SELECT
E.MEMBERC_CK
,E.PCP_EDW_AFFILIATION_ID
,E.AGE_YEARS
,E.BIRTH_DATE
,E.EFFECTIVE_DATE
,E.END_DATE
,MAX(SERVICE_START_DATE.DATE_DATE) AS LAST_DATE_SCREENED
,E.PLAN_ID
FROM ZJAC_ELIG E
LEFT JOIN FT_SERVICE_TRANSACTION C
ON C.MEMBERC_CK = E.MEMBERC_CK
AND C.PLAN_ID IN (:MY_PLAN_ID1, :MY_PLAN_ID2,:MY_PLAN_ID3,:MY_PLAN_ID4,:MY_PLAN_ID5)
--AND C.PLAN_ID = 112
AND (C.PROC_CODE_1 BETWEEN '99381' AND '99385' OR C.PROC_CODE_1 BETWEEN '99391' AND '99395'
OR C.PROC_CODE_2 BETWEEN '99381' AND '99385' OR C.PROC_CODE_2 BETWEEN '99391' AND '99395')
LEFT JOIN DIM_DATE SERVICE_START_DATE
ON SERVICE_START_DATE.DATE_ID = C.SERVICE_START_DATE_ID
GROUP BY
E.MEMBERC_CK
,E.PCP_EDW_AFFILIATION_ID
,E.AGE_YEARS
,E.BIRTH_DATE
,E.EFFECTIVE_DATE
,E.END_DATE
,E.PLAN_ID
)WITH DATA
UNIQUE PRIMARY INDEX (MEMBERC_CK,PLAN_ID,PCP_EDW_AFFILIATION_ID )
INDEX (MEMBERC_CK)
INDEX (PLAN_ID)
ON COMMIT PRESERVE ROWS;

COLLECT STATS ON ZJA_LAST_SCREENS INDEX (MEMBERC_CK,PLAN_ID,PCP_EDW_AFFILIATION_ID );
COLLECT STATS ON ZJA_LAST_SCREENS INDEX (MEMBERC_CK );
COLLECT STATS ON ZJA_LAST_SCREENS INDEX (PLAN_ID );


END;

 

When I run this query as it I get the error: BEGIN Failed [3707]. Syntax error, expected something like a name or a Unicode delimited identifier or a 'Transaction' keyword between the 'BEGIN' keyword and the 'DECLARE' keyword. 

It runs when I comment out the begin keyword and remove the variable declaration and the variables from within the query. However, I want the variables and I will like suggestions on how to navigate this hurdle. Thanks in advance. 


Accepted Solutions
Junior Contributor

Re: Volitile tables and Transactions

Yes, correct.

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Volitile tables and Transactions

This source code must be wrapped in a Stored Procedure.

Enthusiast

Re: Volitile tables and Transactions

WHich means in Teradata there is no way to have variables outside Stored Procedure? 

Junior Contributor

Re: Volitile tables and Transactions

Yes, correct.