query giving random counts when running with others versus giving distinct count when running alone

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

query giving random counts when running with others versus giving distinct count when running alone

I have written a BTEQ script that has 10 queries to build 10 volatile tables.

The issue I am facing is when I am building the first 9 volatile either in one go or one by one the numbers of rows processed in each volatile table remains same but when I am building the 10th Volatile table in one go after the 9th volatile table is built, the rows of rows inserted in 10th volatile table changes in each run, however when I am building the 10th table separately/individually after building the other 9 volatile table, the count is consistent over the multiple runs.

 

Can some one help me understand what is happening here, why it is giving random/different count when building 10th table with other 9 tables but gives same count when building the table individually?

 

 

8 REPLIES
Highlighted
Teradata Employee

Re: query giving random counts when running with others versus giving distinct count when running al

You haven't provided much information to go on.

 

Perhaps the 10th one has some unqualified table references that resolve to one or more of the first 9, when run together?

Enthusiast

Re: query giving random counts when running with others versus giving distinct count when running al

Fred,

 

Can you please let me know what all information you need, I will try to provider that so that you can assist me?

 

Enthusiast

Re: query giving random counts when running with others versus giving distinct count when running al

Fred,

 

Can you please let me know what all information you need, I will try to provider that so that you can assist me?

 

FYI, I have checked the 10th query which is giving random counts when run together, has unique alias for each table used in query.

Teradata Employee

Re: query giving random counts when running with others versus giving distinct count when running al

When you say "in one go", do you mean a single multi-statement request or just one BTEQ session?

When you refer to "building" volatile tables, is that an INSERT/SELECT or something else.

Are there intended to be dependencies between the first 9 and the 10th?

Are any of the table references in the 10th just "tablename" instead of "databasename.tablename"?

Can you show the SQL, at least for the 10th?

Enthusiast

Re: query giving random counts when running with others versus giving distinct count when running al

When you say "in one go", do you mean a single multi-statement request or just one BTEQ session? -- Single Multi-statement request, all the create, insert and select volatile table statement for 10 tables executed by one by one in just one go.

When you refer to "building" volatile tables, is that an INSERT/SELECT or something else. -- CREATE TABLE THEN INSERT/SELECT Statement

Are there intended to be dependencies between the first 9 and the 10th? -- 10th table select query is using 2 of the 9 volatile tables built before it.

Are any of the table references in the 10th just "tablename" instead of "databasename.tablename"? -- Yes, The query has only volatile tables used so it is referenced with  just "tablename" instead of "databasename.tablename"

 

Here is the 10th volatile table query and also in the similar fashion other 9 volatile table queries are written.

 

CREATE VOLATILE TABLE PREM_SPAN
(
POLICY_NO VARCHAR(20),
EFF_BEG_DT DATE FORMAT 'YYYY/MM/DD',
EFF_END_DT DATE FORMAT 'YYYY/MM/DD',
PD_CD VARCHAR(20),
MD_WINDCHILL VARCHAR(20),
RX_WINDCHILL VARCHAR(20),
DN_WINDCHILL VARCHAR(20),
VS_WINDCHILL VARCHAR(20),
ORIG_PREM_RT_AMT DECIMAL(18,2)
)
UNIQUE PRIMARY INDEX ( POLICY_NO,PD_CD, EFF_BEG_DT,EFF_END_DT)
ON COMMIT PRESERVE ROWS;

 

.IF errorlevel <>0 THEN .quit 1

 

INSERT INTO PREM_SPAN
SELECT GRP.POLICY_NO
,GRP.INVC_COVRG_EFF_DT
,GRP.INVC_COVRG_END_DT
,GRP.PD_CD
,MAX(CASE WHEN SUBSTR(GRP.PD_VAR_CD,1,2) = 'MD' THEN GRP.PD_VAR_CD ELSE '' END) MD_WINDCHILL
,MAX(CASE WHEN SUBSTR(GRP.PD_VAR_CD,1,2) = 'RX' THEN GRP.PD_VAR_CD ELSE '' END) RX_WINDCHILL
,MAX(CASE WHEN SUBSTR(GRP.PD_VAR_CD,1,2) = 'DN' THEN GRP.PD_VAR_CD ELSE '' END) DN_WINDCHILL
,MAX(CASE WHEN SUBSTR(GRP.PD_VAR_CD,1,2) = 'VS' THEN GRP.PD_VAR_CD ELSE '' END) VS_WINDCHILL
,SUM(GRP.ORIG_PREM_AMT)

FROM
(SELECT
SUBSTR(A_M.MB_NBR,1,9) AS POLICY_NO
,A_M.PD_VAR_CD
,A_M.PD_CD
,A_M.ORIG_PREM_AMT
,A_M.INVC_COVRG_EFF_DT
,COALESCE(MAX(A_M.YMDEND_PREV) OVER (PARTITION BY A_M.MB_NBR,A_M.ORIG_PREM_AMT,A_M.PD_VAR_CD,A_M.PD_CD
ORDER BY A_M.INVC_COVRG_EFF_DT ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), A_M.YMDEND_MAX) INVC_COVRG_END_DT
FROM
(--Squish the spans based on any change in the original rate amount
SELECT
INVC1.MB_NBR
,INVC1.PD_VAR_CD
,INVC1.PD_CD
,INVC1.ORIG_PREM_AMT
,INVC1.INVC_COVRG_EFF_DT
,MIN(INVC1.INVC_COVRG_END_DT) OVER(PARTITION BY INVC1.MB_NBR,INVC1.ORIG_PREM_AMT,INVC1.PD_VAR_CD,INVC1.PD_CD
ORDER BY INVC1.INVC_COVRG_EFF_DT ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) YMDEND_PREV
,MAX(INVC1.INVC_COVRG_END_DT) OVER(PARTITION BY INVC1.MB_NBR,INVC1.ORIG_PREM_AMT,INVC1.PD_VAR_CD, INVC1.PD_CD ORDER BY INVC1.INVC_COVRG_END_DT ASC ) YMDEND_MAX
,CASE WHEN YMDEND_PREV >= INVC1.INVC_COVRG_EFF_DT -1 THEN 'N' ELSE 'Y' END AS NEW_SPAN
FROM
(--With the correctly spanned rows aggregate the original amount and
--only select rows that are appropriately split from the previous SQL
SELECT
INVC_SPAN.MB_NBR
,INVC_SPAN.PD_VAR_CD
,INVC_SPAN.PD_CD
,INVC_SPAN.INVC_COVRG_EFF_DT
,INVC_SPAN.INVC_COVRG_END_DT
,SUM(INVC_SPAN.ORIG_PREM_RT_AMT) AS ORIG_PREM_AMT
FROM
( -- Derive end dates since the mid month adjustments don't change the end dates of previous row
SELECT DISTINCT
INVC_ADJT.MB_NBR
,INVC_ADJT.PD_VAR_CD
,INVC_ADJT.PD_CD
,INVC_ADJT.ORIG_PREM_RT_AMT
,INVC_ADJT.INVC_COVRG_EFF_DT
,INVC_ADJT.INVC_COVRG_END_DT
,INVC_ADJT.INVC_DRILL_BACK_DTL_DSC
,INVC_ADJT.AMT
,(MAX(INVC_ADJT.INVC_COVRG_EFF_DT) OVER(PARTITION BY INVC_ADJT.MB_NBR,INVC_ADJT.PD_VAR_CD,PD_CD,INVC_ADJT.INVC_DRILL_BACK_DTL_DSC, INVC_ADJT.ORIG_PREM_RT_AMT
ORDER BY INVC_ADJT. INVC_COVRG_EFF_DT ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING))-1 YMDEND_NEXT
,CASE
WHEN YMDEND_NEXT IS NULL THEN INVC_ADJT.INVC_COVRG_END_DT --No more span
WHEN YMDEND_NEXT > INVC_ADJT.INVC_COVRG_END_DT THEN INVC_ADJT.INVC_COVRG_END_DT --Mid month change
ELSE YMDEND_NEXT END END_DT
FROM
(
SELECT
INVC_ADJT11.MB_NBR
,INVC_ADJT11.APPL_CNTRT_TYP_CD
,INVC_ADJT11.PD_VAR_CD
,INVC_ADJT11.PD_CD
,INVC_ADJT11.EFF_BEG_DT AS INVC_COVRG_EFF_DT
,INVC_ADJT11.EFF_END_DT AS INVC_COVRG_END_DT
,INVC_ADJT11.INVC_DRILL_BACK_DTL_DSC
,INVC_ADJT11.ORIG_PREM_RT_AMT
,INVC_ADJT11.AMT
FROM PREM_SPAN_DRIVER INVC_ADJT11

/* Remove the premium adjusted spans after the split */
INNER JOIN PREM_SPAN_TMP TMP
ON INVC_ADJT11.MB_NBR = TMP.MB_NBR
AND INVC_ADJT11.PD_VAR_CD = TMP.PD_VAR_CD
AND INVC_ADJT11.APPL_CNTRT_TYP_CD = TMP.APPL_CNTRT_TYP_CD
AND INVC_ADJT11.PD_CD = TMP.PD_CD
AND INVC_ADJT11.EFF_BEG_DT = TMP.INVC_COVRG_EFF_DT

WHERE TMP.AMT > 0
AND INVC_ADJT11.AMT > 0

) INVC_ADJT
) INVC_SPAN
WHERE
INVC_SPAN.INVC_COVRG_END_DT >= INVC_SPAN.INVC_COVRG_EFF_DT
GROUP BY
1,2,3,4,5
) INVC1
)A_M
WHERE
A_M.NEW_SPAN = 'Y'
) GRP
GROUP BY 1,2,3,4;

 

Junior Contributor

Re: query giving random counts when running with others versus giving distinct count when running al

Well, any DDL-Statement musst be immediately commited, thus you can't have a MultiStatement with all those Creates.

 

 

Teradata Employee

Re: query giving random counts when running with others versus giving distinct count when running al

So it seems "in one go" means a single BTEQ script with individual CREATE and INSERT/SELECT statements, which are being executed sequentially.

 

Then what does "separately/individually" mean? Obviously if the 10th one depends on two other volatile tables, it can't mean simply running a separate BTEQ with exactly the statements you provided here.

 

If "separately" means replacing the volatile table references with subqueries, then it's likely the difference is that at least one of those subqueries results in some entirely duplicate rows. Duplicate rows will be quietly removed during an INSERT/SELECT into a SET table (default for Teradata mode) but duplicates are retained in the spool file for a subquery.

 

 

Enthusiast

Re: query giving random counts when running with others versus giving distinct count when running al

One go means- Execute all the create, insert and select of the 10 volatile tables that are written sequentially, I just press F5 once and all the statements are executed one by one and all the 10 tables are built.

 

Spearately means - Execute all the create, insert and select of the 9 volatile tables that are written sequentialy. and then once 9 tables are ready and then within the same session now execute the 10th volatile query separately in new window.

 

please let me know if this is clear