Complex Logic Question... 2

[trmweb]

code:
-------------------------------------------------
SELECT M_YEAR, COUNT(*)
FROM TABLE_D D
WHERE D.PRODUCT_CD = 'PP'
AND D.LOCATION_CD = 'ZZ'
AND D.GROUP_CD = 'GG'
AND D.A-ID IN
(SELECT DISTINCT C.A_ID
FROM TABLE_C C
WHERE C.PRODUCT_CD = 'PP'
AND C.LOCATION_CD = 'LL'
AND C.GROUP_CD = 'GG'
AND C.A-ID IN
(SELECT DISTINCT B.A_ID
FROM TABLE_B B
WHERE B.PRODUCT_CD = 'PP'
AND B.M_LIT_CD = 'MLIT'
AND B.A_LIT_CD = 'ALIT'
AND B.A-ID IN
(SELECT DISTINCT A.A_ID
FROM TABLE_A A
WHERE A.PRODUCT_CD = 'PP'
AND A.GROUP_CD = 'GG'
AND A.JV_LIT = 'JVLIT'
)))
GROUP BY M_YEAR;
-------------------------------------------------

This code joins 4 tables together to generate counts. The only common key is PRODUCT_CD, yet this is only a partial key in each table -- that's why I'm using the DISTINCT keyword. The goal of generating counts is to have some numbers to compare to when I change the logic from a SELECT to an UPDATE.

My question is relating to updating each table based on the where clause logic.

- Does the order of the tables in the nested subquery matter?
For example, the current SELECT (and future UPDATE) will be on TABLE_D and subqueries on TABLE_C, TABLE_B, and TABLE_A. The next SELECT (and future UPDATE) will be on TABLE_C with subqueries on TABLE_B, TABLE_A, and TABLE_D ... etc.

- Should I combine these into one subquery, rather then to nest them?
I'm wondering if I can do this on PRODUCT_CD even though its only a partial key on each of my four tables.

- When I change the 'one' subquery to the different table names -- does the order matter?

Many questions here for the PROs!

TRMWEB

 

[BillDHS]

I work within a Teradata environment, and subqueries automatically drop dups, so the DISTINCT is unnecessary. This should help run faster, but does not eliminate subqueries.

I would think the following query would give you the same results:

SELECT M_YEAR, COUNT(*)
FROM TABLE_D D
WHERE D.PRODUCT_CD = 'PP'
AND D.LOCATION_CD = 'ZZ'
AND D.GROUP_CD = 'GG'
AND D.A-ID IN
(SELECT C.A_ID
FROM TABLE_C C, Table_B B, Table_A A
WHERE C.PRODUCT_CD = 'PP'
AND C.LOCATION_CD = 'LL'
AND C.GROUP_CD = 'GG'
AND C.A-ID = B.B_ID
AND B.PRODUCT_CD = 'PP'
AND B.M_LIT_CD = 'MLIT'
AND B.A_LIT_CD = 'ALIT'
AND B.A-ID = A.A_ID
AND A.PRODUCT_CD = 'PP'
AND A.GROUP_CD = 'GG'
AND A.JV_LIT = 'JVLIT'
)
GROUP BY M_YEAR;

 

[dnoeth]

"The only common key is PRODUCT_CD, yet this is only a partial key in each table -- that's why I'm using the DISTINCT keyword."
No need to specify DISTINCT explicitly in a subquery (not only in Teradata), it's always distinct.

"Does the order of the tables in the nested subquery matter?"
Not for the result set (because subqueries are similar to inner joins), but the performance may be different.

"Should I combine these into one subquery, rather then to nest them?"

It depends. Sometimes subqueries are better optimized than joins and vice versa.

"I'm wondering if I can do this on PRODUCT_CD even though its only a partial key on each of my four tables."

You can, but you'll have much more rows in the subquery than expected, just run BillDHS' query and then put a DISTINCT in it.

"When I change the 'one' subquery to the different table names -- does the order matter?"
The order of table names within FROM?
If it's a cost optimizer hopefully not.

Bottom line: Test different variations for performance.

Dieter

 

[trmweb]

I appreciate the feedback. The sample code and the answers will take me to the next step.

Thanks!