Query returns different result sets for same data - Seq No Generator

[radhabalan]

Any help regarding the below issue is highly appreciated!!!.

Trying to Accomplish is to Load data from RB_TestClone Table to RB_Test table, but should generate a Sequence Number automatically based on the group COL1, COL2.


COL1, COL2, COL3 are the columns from TestClone to be loaded to Test. COL4 is a sequence number incremented
by 1 and has to be done while loading.


To generate that number a new temp table called RB_TEMP is used. ( Used Derived table first, since there's spool space issue, decided to break up the SQL by creating a TEMP table )

Temp table is used for Generating Unique sequence numbers....
DDL, DML , and Test data is given.



Note: Logic used explained in sequence ( near to *** )


##############

CREATE SET TABLE RB_TESTCLONE ,FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL

(

COL1 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

COL2 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

COL3 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX ( COL3 );

##

CREATE SET TABLE RB_TEST ,FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL

(

COL1 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

COL2 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

COL3 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

COL4 INTEGER)

PRIMARY INDEX ( COL3 );

##

CREATE SET TABLE RB_TEMP ,FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL

(

SEQ INTEGER DEFAULT 0 ,

COL1 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

COL2 CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

INCR INTEGER DEFAULT 1 )

PRIMARY INDEX ( SEQ );

##



SELECT * FROM RB_TEST;



COL1 COL2 COL3 COL4

------------------------------------------

AAA YYY J 4

AAA YYY S 1

AAA YYY S 2

AAA YYY S 3

BBB YYY S 1

BBB YYY S 2

AAA ZZZ S 1

AAA ZZZ S 2

BBB ZZZ S 1

BBB ZZZ S 2

BBB ZZZ S 3

BBB ZZZ S 4



##



SELECT * FROM RB_TESTCLONE;



COL1 COL2 COL3

------------------------------

BBB YYY U

CCC ZZZ G

BBB YYY T

AAA YYY R

BBB YYY N

AAA YYY S

BBB ZZZ F

CCC ZZZ F





##



*** : Test Case - Taking group COL1,COL2 for eg BBB YYY has 3 records in RB_TESTCLONE

Maximum value for COL4 in RB_TEST for BBB YYY group is 2. So when loaded COL4 should be incremented from

3 producing 3,4,5 values in COL4 for group BBB YYY.



INSERT INTO RB_TEMP (SEQ, COL1,COL2 )

SELECT MAX(COL4),COL1,COL2 FROM RB_TEST GROUP BY COL1,COL2



INSERT INTO RB_TEMP ( SEQ,COL1,COL2 )

SELECT MAX( 0), COL1, COL2 FROM RB_TESTCLONE GROUP BY COL1,COL2 WHERE ( COL1, COL2 ) NOT IN ( SELECT COL1, COL2 FROM RB_TEMP )





SELECT * FROM RB_TEMP



SEQ COL1 COL2 INCR

---------------------------------------

2 BBB YYY 1

4 AAA YYY 1

0 CCC ZZZ 1

2 AAA ZZZ 1

4 BBB ZZZ 1





#####



SELECT

TMP.SEQ + CSUM ( TMP.INCR , 1 ) AS SEQNO ,

TC.COL1,

TC.COL2,

TC.COL3

FROM

RB_TEMP AS TMP,

RB_TESTCLONE AS TC

WHERE

TC.COL1 = TMP.COL1 AND

TC.COL2 = TMP.COL2

** THis query when run different times is producing different results. I am not able to find the loop hole in the Logic.

In where clause if I use one more AND condition
....AND COL1='AAA', the result is good.


SEQNO COL1 COL2 COL3

--------------------------------------

Result Set 1 :



5 AAA YYY R

4 BBB YYY U

5 BBB YYY T

6 BBB YYY N

9 BBB ZZZ F

10 AAA YYY S

7 CCC ZZZ F

8 CCC ZZZ G



Result Set 2 :



3 BBB YYY N

4 BBB YYY U

7 AAA YYY S

8 AAA YYY R

9 BBB ZZZ F

8 BBB YYY T

7 CCC ZZZ G

8 CCC ZZZ F



Result Set 3 :



3 BBB YYY U

6 AAA YYY S

7 AAA YYY R

4 CCC ZZZ F

5 CCC ZZZ G

10 BBB ZZZ F

9 BBB YYY T

10 BBB YYY N



Result Set 4 :



5 AAA YYY S

6 BBB ZZZ F

5 BBB YYY N

6 BBB YYY U

7 BBB YYY T

10 AAA YYY R

7 CCC ZZZ G

8 CCC ZZZ F



Result Set 5 :



3 BBB YYY U

4 BBB YYY N

7 BBB ZZZ F

4 CCC ZZZ F

5 CCC ZZZ G

8 BBB YYY T

11 AAA YYY S

12 AAA YYY R

 

[tdatgod]

Hi,
you have no ORDER BY. Therefore Order is not guarenteed.

Try

order by TC.COL1, TC.COL2, TC.COL3


However that doesn't explain why the CSUM numbers are not consecutive.

I think it has to do with the fact that the CSUM numbers are generated as part of the first SPOOL result set before the WHERE clause is applied and therefore some of the rows are disqualified after the numbers are generated.




--

--

 

[radhabalan]

Thanks for the reply, I am interested to know whether Teradata has got any mechanism other than CSUM to generate a sequence number like Pseudo tables used in other databases like Oracle having Sequence etc..

 

[BillDHS]

tdatgod: Although I agree that there could be a problem with a missing "order by" clause, and I have no physical evidence to prove you wrong, logically your statement about the CSUM being created in spool does not make since to me.

If CSUM were being used to track something like total sales, how could a report be created with a running total if the numbers were calclated for all rows then the rows were dropped. It makes more sence that the rows are selected and ordered and then the running SUM is applied.

P.S. I wish this message box had spell check.