Help in Teradata SQL

[natncm70]

Hello,
I am using two derived tables in one Query. In that query, only difference is date field. First query, I am using today's date and second query, I am using previous date.
I want to use in single query(one derived table) with two above mentioned dates.
Can I use both dates in single derived table.
This is the query.
1.
SELECT
COALESCE(SUM(ENDING_LEDGER_BALANCE_AMT),0)
FROM
DD_TEDW.T0305_ACCOUNT_SUMMARY_DERIVE A
INNER JOIN
DD_TEDW.T0303_ACCOUNT_STATUS_HISTORY B
ON A.ACCOUNT_NBR = B.ACCOUNT_NBR
WHERE A.ACCOUNT_NBR = 960294
AND B.ACCT_STATUS_TYPE_CODE = 1
AND A.ACCOUNT_SUMMARY_DATE IN ('2002-10-31')

2.

SELECT
COALESCE(SUM(ENDING_LEDGER_BALANCE_AMT),0)
FROM
DD_TEDW.T0305_ACCOUNT_SUMMARY_DERIVE A
INNER JOIN
DD_TEDW.T0303_ACCOUNT_STATUS_HISTORY B
ON A.ACCOUNT_NBR = B.ACCOUNT_NBR
WHERE A.ACCOUNT_NBR = 960294
AND B.ACCT_STATUS_TYPE_CODE = 1
AND A.ACCOUNT_SUMMARY_DATE = ( Select calendar_date - 1 From sys_calendar.calendar t2 Where t2.calendar_date = '2002-10-31' ) .

I want to use both where condition in one query. is it possible.because, I am using same concept for 118 derived tables.So, it's exceeds the parser limit. Please help me
for this.

Regards

 

[dnoeth]

Why don't you simply use
AND A.ACCOUNT_SUMMARY_DATE IN ('2002-10-31', '2002-10-30')

or

AND
(A.ACCOUNT_SUMMARY_DATE = '2002-10-31' OR
A.ACCOUNT_SUMMARY_DATE = CAST('2002-10-31' AS DATE) - 1)

If it's really today's date, then
AND
(A.ACCOUNT_SUMMARY_DATE = CURRENT_DATE OR
A.ACCOUNT_SUMMARY_DATE = CURRENT_DATE - 1)


Dieter

 

[natncm70]

Hello,
That's ok. But, I need two sum fields( both today's and previous day's sum). What should i do for that.Please help me.

Regards

 

[natncm70]

Hello,
I am using sum keyword. It is summing both date's. I need each date's sum of account balances.

Regards

 

[dnoeth]

In a single column?
'2002-10-30' 12345.99
'2002-10-31' 34567.00


SELECT
A.ACCOUNT_SUMMARY_DATE as "Date"
,COALESCE(SUM(ENDING_LEDGER_BALANCE_AMT),0) as Balance
...
GROUP BY A.ACCOUNT_SUMMARY_DATE
Date Balance
2002-10-30 12345.99
2002-10-31 34567.00


Or in a single row?
SELECT
COALESCE(SUM(
CASE WHEN A.ACCOUNT_SUMMARY_DATE = '2002-10-31'
THEN ENDING_LEDGER_BALANCE_AMT
END
),0) as "Balance Today"
,COALESCE(SUM(
CASE WHEN A.ACCOUNT_SUMMARY_DATE = '2002-10-30'
THEN ENDING_LEDGER_BALANCE_AMT
END
),0) as "Balance Yesterday"
...

Balance Today Balance Yesterday
34567.00 12345.99

Dieter

 

[natncm70]

Hello,
Thanks. It is working fine

 

[geppetto]

natcm70,

If this is working for you, I don't want to rock the boat.

I did want to point out the "Case" statements used in the manner described above are not very efficient. The reason is that each row in your result set must be evaluated and stuck in one of the "buckets". So, if you have many rows, performance is very poor. For smaller tables or smaller result sets (spool) the poor performance may not be noticable.

I would suggest bringing in two copies of DD_TEDW.T0305_ACCOUNT_SUMMARY_DERIVE and perform your one day offset in the join. (see below)

The thing to watch here is order Teradata hits the larger tables. In other words, we want the filters applied before the joins are made, check the explain. You also may be able to move the WHERE clause into the FROM clause.




SELECT
COALESCE(SUM(Today.ENDING_LEDGER_BALANCE_AMT),0),
COALESCE(SUM(Yesterday.ENDING_LEDGER_BALANCE_AMT),0)
FROM
(DD_TEDW.T0305_ACCOUNT_SUMMARY_DERIVE Yesterday INNER JOIN
(DD_TEDW.T0305_ACCOUNT_SUMMARY_DERIVE Today INNER JOIN
DD_TEDW.T0303_ACCOUNT_STATUS_HISTORY Status ON
Today .ACCOUNT_NBR = B.ACCOUNT_NBR) ON
Today.ACCOUNT_SUMMARY_DATE = Yesterday.ACCOUNT_SUMMARY_DATE+1)
WHERE Today .ACCOUNT_NBR = 960294
AND Status.ACCT_STATUS_TYPE_CODE = 1
AND Today .ACCOUNT_SUMMARY_DATE = '2002-10-31'

 

[dnoeth]

Hi gepetto,
some remarks:

Instead of an inner joining you'll need a full outer join or you'll get no result set if there is no row for one of the dates.

And your query will return a wrong result set, if there is more than a single row for today or yesterday.
The only way to avoid this is probably to sum the rows for today and yesterday in two derived tables and then join the result.

But the main point:
Do you have any proof for poor performance using case?
You add one more join in your query, so there will be another step involving a new spool.
Especially if it's a large number of rows this is a huge overhead. Compare this to just some CPU usage for the CASE evaluation.

Dieter