Comparing two tables

Database
Enthusiast

Comparing two tables

Hi Gurus
I have two huge tables TABLE-A and TABLE-B (200 Million rows in each table)
Now I am checking to see if both the table are identical or not.

one way of testing it is
sel * from table-A
minus
sel * from table-B

and then

sel * from table-B
minus
sel * from table-A

is there a better/faster way of doing this. Each table has more than 50 columns.

Thanks

14 REPLIES
Junior Contributor

Re: Comparing two tables

MINUS: spool both tables & redistribute -> sort distinct -> exclusion merge join

Is the table SET or MULTISET? If it's MULTISET you'll need MINUS ALL, if it's SET, then MINUS ALL is more efficient, too.

MINUS ALL: spool both tables & redistribute -> sort -> minus all join

But spooling & redistributing is a large overhead, so if it's a SET table the most efficient way is probably using
NOT EXISTS: just a direct exclusion merge join

Of course it's easier to code a
SEL * FROM T1 MINUS ALL SEL * FROM T2
instead of a
SEL * FROM T1
WHERE NOT EXISTS
(SEL * FROM T2 WHERE T1.col1 = T2.col2 AND ... T1.col50 = T2.col50)
but it's probably worth the effort (and you can create the code using dbc.columns)

Dieter
Enthusiast

Re: Comparing two tables

Thank you very much!
I really appreciate it.

Enthusiast

Re: Comparing two tables

Hello,

Please i just want to verify that i well understood :  you are saying that NOT EXISTS is better  than MINUS / MINUS ALL , that's it ?

Can you give me more details please ?

Many thanks.

Ghalia

Teradata Employee

Re: Comparing two tables

As Dieter said (years ago): MINUS / MINUS ALL will always spool / redistribute / sort both tables.

If you code a NOT EXISTS, the optimizer will take advantage of the PI being the same and just do a direct AMP-local join.

Note that the results for NOT EXISTS are equivalent to MINUS, not MINUS ALL. That's fine for SET tables, but not for a MULTISET table with duplicate rows.

Enthusiast

Re: Comparing two tables

Hi Fred & Dieter,

 

I have a similar task of comparing two Teradata tables to see if they are in fact identical by running the same set of SQLs twice to create two tables: table A & table B within 10 mins. apart and using the logic similar to:

 

SELECT * FROM TABLE_A

MINUS ALL

SELECT * FROM TABLE_B

 

while the # of rows in table A is the same as table B but this produce a huge # of rows in differences: I am just puzzling why I am running the identical SQLs twice: one creates table A while the other creating table B and end up getting huge differences in content values.

 

Please note that all source tables used in creating A or B are monthly refreshed rather than time-dependent (i.e.. by the minute) tables so the source tables contents are exactly the same within the 10 mins.

 

Can you explain?

 

We are running the Teradata SQLs in a SAS application, this is the equivalent SQLs it runs:

 

CREATE SET TABLE &gbUserDataBase..&gbTablePrefix._CATO_Trans_New ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CLNT_NO DECIMAL(13,0),
CLNT_TYP SMALLINT,
PRTITION_NO BYTEINT,
SEQ_NO SMALLINT,
SESS_DT DATE FORMAT 'YYYY/MM/DD',
SESS_TM TIME(3),
SESS_TR INTEGER,
TXN_ID SMALLINT,
TXN_SESS_ID SMALLINT,
TXN_SRC_CD BYTEINT,
TXN_TYP SMALLINT,
CRNCY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
TXN_CD SMALLINT,
AMT DECIMAL(18,2),
TR_NO INTEGER,
ACCT_ID INTEGER,
ar_id VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
arngmnt_ar_id CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
txn_cat VARCHAR(21) CHARACTER SET UNICODE NOT CASESPECIFIC,
ATM_Limit SMALLINT,
BadTransaction VARCHAR(45) CHARACTER SET UNICODE NOT CASESPECIFIC,
TotalTrans INTEGER,
GoodTrans INTEGER,
PaymentTrans INTEGER,
BusinessTrans INTEGER,
DepositTrans INTEGER,
ForeignTrans INTEGER,
USTrans INTEGER,
WithdrawalTrans INTEGER,
OtherTrans INTEGER,
Unmigratable VARCHAR(54) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX ( CLNT_NO )
);

 

insert into &gbUserDataBase..&gbTablePrefix._CATO_Trans_New

 

select Stuff.*
/*----------------------------------------------------------- Derive migratability ---*/
, case when BadTransaction ^= ' ' then BadTransaction
when TotalTrans ^= GoodTrans then '(11) AM 3.1: Session has Unmigratable Transactions'
when PaymentTrans > 6
or BusinessTrans > 6
or DepositTrans > 6
or ForeignTrans > 6
or USTrans > 6
or WithdrawalTrans > 6
or OtherTrans > 6 then '(12) AM 3.2: Session has Too Many Similar Transactions'
when OtherTrans = TotalTrans then '(13) AM 3.3: Session has Only "Other" Transactions'
else ' ' end as Unmigratable /*--- blank = migratable ---*/
from (select Txn.*
, Arr.ar_id as arngmnt_ar_id /*--- temporary ---*/
, case when txn_cd = 0 then 'TRANS CODE 0'
/* when substr(Txn.ar_id,14,2) in ('45','46','47','48','49','85','86','87','88','89') then 'US$ INCOME CODE'*/
when Arr.ar_id is not null then 'US$ ACCT CATG CODE'
when clnt_typ = 1 and txn_typ in (21, 95) and Txn.crncy_cd = 'CAD' then 'BILL PAYMENT'
when clnt_typ = 1 and txn_typ in (21, 95) and Txn.crncy_cd = 'USD' then 'US$ BILL PAYMENT'
when clnt_typ = 1 and txn_typ in (21, 95) then 'FOREIGN$ BILL PAYMENT'
when clnt_typ = 1 and txn_typ = 50 and Txn.crncy_cd = 'CAD' then 'WITHDRAWAL'
when clnt_typ = 1 and txn_typ = 50 and Txn.crncy_cd = 'USD' then 'US$ WITHDRAWAL'
when clnt_typ = 1 and txn_typ = 50 then 'FOREIGN$ WITHDRAWAL'
when clnt_typ = 1 and txn_typ in (51, 92) and Txn.crncy_cd = 'CAD' then 'DEPOSIT'
when clnt_typ = 1 and txn_typ in (51, 92) and Txn.crncy_cd = 'USD' then 'US$ DEPOSIT'
when clnt_typ = 1 and txn_typ in (51, 92) then 'FOREIGN$ DEPOSIT'
when Txn.crncy_cd = 'USD' then 'US$ LEFTOVERS'
when Txn.crncy_cd ^= 'CAD' then 'FOREIGN$ LEFTOVERS'
when clnt_typ ^= 1 and txn_typ in (10, 11, 12, 13, 17) then 'BUSINESS'
when clnt_typ = 1 and txn_typ in (10, 12) and txn_cd = 99 then 'OTHER (TxnTyp 10...)'
when clnt_typ = 1 and txn_typ in (11, 13) and txn_cd = 98 then 'OTHER (TxnTyp 11...)'
when clnt_typ = 1 and txn_typ in (20,22,23,82,83,91,96,97,102,103,107) then 'OTHER (TxnTyp 20...)'
else 'NOT APPLICABLE' end as txn_cat
, case when entitlement_cd in (1,6,7,60,61,82,86,88,92,94) then 1500
when entitlement_cd = 5 then 3000
when entitlement_cd in (10,12,14,50,58,62,68) then 1000
when entitlement_cd in (25,40,52,54,56,64) then 500
when entitlement_cd = 48 then 0
when entitlement_cd = 49 then 20
else null end as ATM_Limit
, case when txn_cd = 0 then '(01) Transaction Code is 0'
when txn_cat = 'NOT APPLICABLE' then '(02) Transaction Not Applicable'
when txn_cat like 'FOREIGN$%' then '(03) AM 2.1: Foreign Transaction'
when txn_cat like 'US$%' then '(04) AM 2.2: US Transaction'
when txn_cat like 'BUSINESS' then '(05) AM 2.3: Business Transaction'
when Txn.clnt_typ = 1 and Rel.ar_id is not null then '(06) Personal with Business Relationship'
when atm_limit is not null and atm_limit < amt and txn_typ = 50 then '(07) AM 2.4: Withdrawal Higher than ATM Limit'
when txn_typ = 50 and amt > 500
or txn_typ = 51 and amt > 1000
or txn_typ = 92 and amt > 2000 then '(08) AM 2.5: Large Amount Transaction'
when txn_typ in (22,23,83,91,96) then '(09) AM 2.6: Unmigratable Transaction Type'
when txn_typ = 92 and entitlement_cd = 68 then '(10) AM 2.7: TxnTyp 92 and Entitlement 68'
else ' ' end as BadTransaction
, sum(case when txn_cat in ('NOT APPLICABLE', 'TRANS CODE 0') then 0 else 1 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as TotalTrans
, sum(case when BadTransaction = ' ' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as GoodTrans
, sum(case when txn_cat = 'BILL PAYMENT' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as PaymentTrans
, sum(case when txn_cat = 'BUSINESS' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as BusinessTrans
, sum(case when txn_cat = 'DEPOSIT' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as DepositTrans
, sum(case when txn_cat like 'FOREIGN%' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as ForeignTrans
, sum(case when txn_cat like 'US%' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as USTrans
, sum(case when txn_cat = 'WITHDRAWAL' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as WithdrawalTrans
, sum(case when txn_cat like 'OTHER%' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as OtherTrans
/*------------------------------------------------------- Get transactions ---*/
from (select a.clnt_no
, a.clnt_typ
, b.prtition_no /*--- Primary index ---*/
, b.seq_no /*--- Primary index ---*/
, b.sess_dt /*--- Primary index ---*/
, b.sess_tm /*--- Primary index ---*/
, b.sess_tr /*--- Primary index ---*/
, b.txn_id /*--- Primary index ---*/
, b.txn_sess_id /*--- Primary index ---*/
, b.txn_src_cd /*--- Primary index ---*/
, b.txn_typ
, b.crncy_cd
, b.txn_cd
, b.amt
, b.tr_no
, b.acct_id
, case when zeroifnull(b.gl_acct_no ) ^= 0 and b.txn_src_cd = 6 then substr(cast(b.tr_no + 10000000000000 as char(14)),2,13)
|| substr(cast(b.gl_acct_no + 10000000 as char(8)),2,7)
when zeroifnull(b.visa_crd_no) ^= 0 and b.txn_src_cd = 7 then substr(cast(b.visa_crd_no + 100000000000000000000 as char(21)),2,20)
when zeroifnull(b.acct_id ) ^= 0 and b.txn_src_cd = 12 then substr(cast(b.tr_no + 1000000000 as char(10)),2,9)
|| substr(cast(b.acct_id + 100000000 as char(9)),2,8)
|| substr(cast(b.ln_seg_no + 1000 as char(4)),2,3)
when zeroifnull(b.acct_id ) ^= 0 then substr(cast(b.tr_no + 10000000000000 as char(14)),2,13)
|| substr(cast(b.acct_id + 10000000 as char(8)),2,7)
else cast(random(-100000000, 0) as char(20)) end as ar_id
/* , case when b.acct_id is null then cast(random(-100000000, 0) as char(20)) else '00000000' || substr(cast(b.tr_no + 100000 as char(6)),2,5) || substr(cast(b.acct_id + 10000000 as char(8)),2,7) end as ar_id*/
from (select *
from ddwv01.rb_clnt
where snap_dt = (select max(snap_dt) from ddwv01.rb_clnt)) as a
inner join ddwv01.cust_sess c
on c.ip_id = a.clnt_no
and c.ip_id <> 0
and c.sess_sts = 1
and c.sess_typ = 0
and c.sess_dt between date '2017-12-10' and date '2018-06-08'

inner join ddwv01.txn b
on b.prtition_no = c.prtition_no
and b.sess_dt = c.sess_dt
and b.sess_tm = c.sess_tm
and b.sess_tr = c.sess_tr
and b.txn_sts = 1) as Txn
/*---------------------------------------------------- Get acct categories ---*/
left join (select distinct ar_id
from ddwv01.arngmnt
where acct_catg_cd in (1960, 1975, 2000, 2010, 2015, 2035, 2419)) as Arr
on Arr.ar_id = Txn.ar_id
/*--------------------------------------------- Get business relationships ---*/
left join (select a.clnt_no1 as bus_clnt_no
, a.clnt_no2 as clnt_no
, b.ar_id
from ddwv01.clnt_to_clnt_reltn a
, ddwv01.arngmnt_own b
where a.clnt_no1 = b.clnt_no
and a.snap_dt = (select max(dt_val) from dg6v01.dt_cntrl)
and a.ip_to_ip_reltn_typ = 2) as Rel
on Rel.ar_id = Txn.ar_id
/*------------------------------------------------------- Get entitlements ---*/
left join (select clnt_no
, entitlement_cd
from dg6v01.clnt
where entitlement_cd is not null) as Ent
on Ent.clnt_no = Txn.clnt_no) as Stuff;

 

The variable starts with '&' and ends with '.' is a SAS macro variable that resolves to a macro value in text.

 

Please advise.

 

Thanks

Stephen

 

 

Enthusiast

Re: Comparing two tables

Just to clarify what I meant:

 

I used the submitted codes to create table 'DDWUTDC.QJK8SNK_CATO_TRANS_NEW' and then 'DDWUTDC.QJK8SNK_CATO_TRANS_ORIG'
10 mins later. When I run:

 

Select * from DDWUTDC.QJK8SNK_CATO_TRANS_NEW

Minus ALL

Select * from DDWUTDC.QJK8SNK_CATO_TRANS_ORIG;

 

I ended up with 39496349 rows in differences of values.

Teradata Employee

Re: Comparing two tables

Most likely the rows with a generated random ar_id

, case when zeroifnull(b.gl_acct_no ) ^= 0 and b.txn_src_cd = 6 then substr(cast(b.tr_no + 10000000000000 as char(14)),2,13)
|| substr(cast(b.gl_acct_no + 10000000 as char(8)),2,7)
when zeroifnull(b.visa_crd_no) ^= 0 and b.txn_src_cd = 7 then substr(cast(b.visa_crd_no + 100000000000000000000 as char(21)),2,20)
when zeroifnull(b.acct_id ) ^= 0 and b.txn_src_cd = 12 then substr(cast(b.tr_no + 1000000000 as char(10)),2,9)
|| substr(cast(b.acct_id + 100000000 as char(9)),2,8)
|| substr(cast(b.ln_seg_no + 1000 as char(4)),2,3)
when zeroifnull(b.acct_id ) ^= 0 then substr(cast(b.tr_no + 10000000000000 as char(14)),2,13)
|| substr(cast(b.acct_id + 10000000 as char(8)),2,7)
else cast(random(-100000000, 0) as char(20)) end as ar_id
Enthusiast

Re: Comparing two tables

Hi Fred,

 

Thank you for pointing this out for me!

 

To remedy the problem and just for the sake of comparing two tables in the same terms (so an apple to an apple), I have changed the random function: “cast(random(-100000000, 0) as char(20))” to a constant “cast(100000000 as char(20))” so my query becomes:

 

select Stuff.*

/*----------------------------------------------------------- Derive migratability ---*/

, case when BadTransaction ^= ' ' then BadTransaction

when TotalTrans ^= GoodTrans then '(11) AM 3.1: Session has Unmigratable Transactions'

when PaymentTrans > 6

or BusinessTrans > 6

or DepositTrans > 6

or ForeignTrans > 6

or USTrans > 6

or WithdrawalTrans > 6

or OtherTrans > 6 then '(12) AM 3.2: Session has Too Many Similar Transactions'

when OtherTrans = TotalTrans then '(13) AM 3.3: Session has Only "Other" Transactions'

else ' ' end as Unmigratable /*--- blank = migratable ---*/

from (select Txn.*

, Arr.ar_id as arngmnt_ar_id /*--- temporary ---*/

, case when txn_cd = 0 then 'TRANS CODE 0'

/* when substr(Txn.ar_id,14,2) in ('45','46','47','48','49','85','86','87','88','89') then 'US$ INCOME CODE'*/

when Arr.ar_id is not null then 'US$ ACCT CATG CODE'

when clnt_typ = 1 and txn_typ in (21, 95) and Txn.crncy_cd = 'CAD' then 'BILL PAYMENT'

when clnt_typ = 1 and txn_typ in (21, 95) and Txn.crncy_cd = 'USD' then 'US$ BILL PAYMENT'

when clnt_typ = 1 and txn_typ in (21, 95) then 'FOREIGN$ BILL PAYMENT'

when clnt_typ = 1 and txn_typ = 50 and Txn.crncy_cd = 'CAD' then 'WITHDRAWAL'

when clnt_typ = 1 and txn_typ = 50 and Txn.crncy_cd = 'USD' then 'US$ WITHDRAWAL'

when clnt_typ = 1 and txn_typ = 50 then 'FOREIGN$ WITHDRAWAL'

when clnt_typ = 1 and txn_typ in (51, 92) and Txn.crncy_cd = 'CAD' then 'DEPOSIT'

when clnt_typ = 1 and txn_typ in (51, 92) and Txn.crncy_cd = 'USD' then 'US$ DEPOSIT'

when clnt_typ = 1 and txn_typ in (51, 92) then 'FOREIGN$ DEPOSIT'

when Txn.crncy_cd = 'USD' then 'US$ LEFTOVERS'

when Txn.crncy_cd ^= 'CAD' then 'FOREIGN$ LEFTOVERS'

when clnt_typ ^= 1 and txn_typ in (10, 11, 12, 13, 17) then 'BUSINESS'

when clnt_typ = 1 and txn_typ in (10, 12) and txn_cd = 99 then 'OTHER (TxnTyp 10...)'

when clnt_typ = 1 and txn_typ in (11, 13) and txn_cd = 98 then 'OTHER (TxnTyp 11...)'

when clnt_typ = 1 and txn_typ in (20,22,23,82,83,91,96,97,102,103,107) then 'OTHER (TxnTyp 20...)'

else 'NOT APPLICABLE' end as txn_cat

, case when entitlement_cd in (1,6,7,60,61,82,86,88,92,94) then 1500

when entitlement_cd = 5 then 3000

when entitlement_cd in (10,12,14,50,58,62,68) then 1000

when entitlement_cd in (25,40,52,54,56,64) then 500

when entitlement_cd = 48 then 0

when entitlement_cd = 49 then 20

else null end as ATM_Limit

, case when txn_cd = 0 then '(01) Transaction Code is 0'

when txn_cat = 'NOT APPLICABLE' then '(02) Transaction Not Applicable'

when txn_cat like 'FOREIGN$%' then '(03) AM 2.1: Foreign Transaction'

when txn_cat like 'US$%' then '(04) AM 2.2: US Transaction'

when txn_cat like 'BUSINESS' then '(05) AM 2.3: Business Transaction'

when Txn.clnt_typ = 1 and Rel.ar_id is not null then '(06) Personal with Business Relationship'

when atm_limit is not null and atm_limit < amt and txn_typ = 50 then '(07) AM 2.4: Withdrawal Higher than ATM Limit'

when txn_typ = 50 and amt > 500

or txn_typ = 51 and amt > 1000

or txn_typ = 92 and amt > 2000 then '(08) AM 2.5: Large Amount Transaction'

when txn_typ in (22,23,83,91,96) then '(09) AM 2.6: Unmigratable Transaction Type'

when txn_typ = 92 and entitlement_cd = 68 then '(10) AM 2.7: TxnTyp 92 and Entitlement 68'

else ' ' end as BadTransaction

, sum(case when txn_cat in ('NOT APPLICABLE', 'TRANS CODE 0') then 0 else 1 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as TotalTrans

, sum(case when BadTransaction = ' ' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as GoodTrans

, sum(case when txn_cat = 'BILL PAYMENT' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as PaymentTrans

, sum(case when txn_cat = 'BUSINESS' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as BusinessTrans

, sum(case when txn_cat = 'DEPOSIT' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as DepositTrans

, sum(case when txn_cat like 'FOREIGN%' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as ForeignTrans

, sum(case when txn_cat like 'US%' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as USTrans

, sum(case when txn_cat = 'WITHDRAWAL' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as WithdrawalTrans

, sum(case when txn_cat like 'OTHER%' then 1 else 0 end) over(partition by Txn.clnt_no, Txn.sess_dt, Txn.sess_tm, Txn.sess_tr) as OtherTrans

/*------------------------------------------------------- Get transactions ---*/

from (select a.clnt_no

, a.clnt_typ

, b.prtition_no /*--- Primary index ---*/

, b.seq_no /*--- Primary index ---*/

, b.sess_dt /*--- Primary index ---*/

, b.sess_tm /*--- Primary index ---*/

, b.sess_tr /*--- Primary index ---*/

, b.txn_id /*--- Primary index ---*/

, b.txn_sess_id /*--- Primary index ---*/

, b.txn_src_cd /*--- Primary index ---*/

, b.txn_typ

, b.crncy_cd

, b.txn_cd

, b.amt

, b.tr_no

, b.acct_id

, case when zeroifnull(b.gl_acct_no ) ^= 0 and b.txn_src_cd = 6 then substr(cast(b.tr_no + 10000000000000 as char(14)),2,13)

|| substr(cast(b.gl_acct_no + 10000000 as char(8)),2,7)

when zeroifnull(b.visa_crd_no) ^= 0 and b.txn_src_cd = 7 then substr(cast(b.visa_crd_no + 100000000000000000000 as char(21)),2,20)

when zeroifnull(b.acct_id ) ^= 0 and b.txn_src_cd = 12 then substr(cast(b.tr_no + 1000000000 as char(10)),2,9)

|| substr(cast(b.acct_id + 100000000 as char(9)),2,8)

|| substr(cast(b.ln_seg_no + 1000 as char(4)),2,3)

when zeroifnull(b.acct_id ) ^= 0 then substr(cast(b.tr_no + 10000000000000 as char(14)),2,13)

|| substr(cast(b.acct_id + 10000000 as char(8)),2,7)

else cast(0 as char(1))  end as ar_id

/* , case when b.acct_id is null then cast(random(-100000000, 0) as char(20)) else '00000000' || substr(cast(b.tr_no + 100000 as char(6)),2,5) || substr(cast(b.acct_id + 10000000 as char(8)),2,7) end as ar_id*/

from (select *

from ddwv01.rb_clnt

where snap_dt = (select max(snap_dt) from ddwv01.rb_clnt)) as a

inner join ddwv01.cust_sess c

on c.ip_id = a.clnt_no

and c.ip_id <> 0

and c.sess_sts = 1

and c.sess_typ = 0

and c.sess_dt between date '2017-12-10' and date '2018-06-08'

 

inner join ddwv01.txn b

on b.prtition_no = c.prtition_no

and b.sess_dt = c.sess_dt

and b.sess_tm = c.sess_tm

and b.sess_tr = c.sess_tr

and b.txn_sts = 1) as Txn

/*---------------------------------------------------- Get acct categories ---*/

left join (select distinct ar_id

from ddwv01.arngmnt

where acct_catg_cd in (1960, 1975, 2000, 2010, 2015, 2035, 2419)) as Arr

on Arr.ar_id = Txn.ar_id

/*--------------------------------------------- Get business relationships ---*/

left join (select a.clnt_no1 as bus_clnt_no

, a.clnt_no2 as clnt_no

, b.ar_id

from ddwv01.clnt_to_clnt_reltn a

, ddwv01.arngmnt_own b

where a.clnt_no1 = b.clnt_no

and a.snap_dt = (select max(dt_val) from dg6v01.dt_cntrl)

and a.ip_to_ip_reltn_typ = 2) as Rel

on Rel.ar_id = Txn.ar_id

/*------------------------------------------------------- Get entitlements ---*/

left join (select clnt_no

, entitlement_cd

from dg6v01.clnt

where entitlement_cd is not null) as Ent

on Ent.clnt_no = Txn.clnt_no) as Stuff;

 

When run, I got an error: “SELECT Failed 2646: No more space in QJK8SNK”. ‘QJK8SNK’ is my UNIX server user id. But I don’t get this error when I run the SQLs with the original random function.

 

What is the cause and how to make a correction?

 

Thanks!

Stephen

Teradata Employee

Re: Comparing two tables

The RANDOM function was likely included in the original query just to give better data distribution, and when you make it a constant the joins to Arr & Rel on ar_id become too heavily skewed.

Perhaps there is some deterministic combination of other columns that could be used to generate dummy values, instead of RANDOM.