Archives of the TeradataForum

Message Posted: Wed, 19 Feb 2003 @ 12:33:59 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Glenn,

It is a little more complex that what you proposed. The difference is that each level referenced the prior level.

One solution was to rename the derived tables to have uniqueness.

I believe that Clay found another solution which was to specify the tables in each select list. I do not have an example of this solution.

Here is an excerpt from what I sent back to the developers (the original SQL had multiple recursive levels - but two levels sufficed):

This is very sloppy SQL.

My first thought is that the problem is in the fact that you reuse LEVEL_FROM on all of your derived tables. I shall show this below.

First, I broke out the two lowest level selects.

This explain works. However, you only have on derived table.

EXPLAIN
SELECT LVL_FROM.*,
LVL_TO.PRNT_CSTM_PROD_CD             PRD_H4_CD,
LVL_TO.CSTM_PROD_NM                  PRD_H5_NM,
LVL_TO.CSTM_PROD_LVL_CD              PRD_H5_LVL
FROM
(
SELECT
LVL_FROM.ORG_ID ORG_ID,
LVL_FROM.CSTM_PROD_CD                PRD_H6_CD,
LVL_FROM.PRNT_CSTM_PROD_CD           PRD_H5_CD,
LVL_FROM.CSTM_PROD_NM                PRD_H6_NM,
LVL_FROM.CSTM_PROD_LVL_CD            PRD_H6_LVL
FROM XEIW890T.TCPR890 LVL_FROM
WHERE LVL_FROM.CSTM_PROD_LVL_CD=6
) LVL_FROM LEFT OUTER JOIN XEIW890T.TCPR890 LVL_TO
ON LVL_FROM.ORG_ID       = LVL_TO.ORG_ID
AND LVL_FROM.PRD_H5_CD   = LVL_TO.CSTM_PROD_CD
AND LVL_FROM.PRD_H6_LVL  > LVL_TO.CSTM_PROD_LVL_CD

However, if I go one level up and have two nested derived tables.

EXPLAIN
SELECT LVL_FROM.*,
LVL_TO.PRNT_CSTM_PROD_CD             PRD_H3_CD,
LVL_TO.CSTM_PROD_NM                  PRD_H4_NM,
LVL_TO.CSTM_PROD_LVL_CD              PRD_H4_LVL
FROM
(
SELECT LVL_FROM.*,
LVL_TO.PRNT_CSTM_PROD_CD             PRD_H4_CD,
LVL_TO.CSTM_PROD_NM                  PRD_H5_NM,
LVL_TO.CSTM_PROD_LVL_CD              PRD_H5_LVL
FROM
(
SELECT
LVL_FROM.ORG_ID,
LVL_FROM.CSTM_PROD_CD                PRD_H6_CD,
LVL_FROM.PRNT_CSTM_PROD_CD           PRD_H5_CD,
LVL_FROM.CSTM_PROD_NM                PRD_H6_NM,
LVL_FROM.CSTM_PROD_LVL_CD            PRD_H6_LVL
FROM XEIW890T.TCPR890 LVL_FROM
WHERE LVL_FROM.CSTM_PROD_LVL_CD=6
) LVL_FROM LEFT OUTER JOIN XEIW890T.TCPR890 LVL_TO
ON LVL_FROM.ORG_ID       = LVL_TO.ORG_ID
AND LVL_FROM.PRD_H5_CD   = LVL_TO.CSTM_PROD_CD
AND LVL_FROM.PRD_H6_LVL  > LVL_TO.CSTM_PROD_LVL_CD
) LVL_FROM LEFT OUTER JOIN XEIW890T.TCPR890 LVL_TO
ON LVL_FROM.ORG_ID       = LVL_TO.ORG_ID
AND LVL_FROM.PRD_H4_CD   = LVL_TO.CSTM_PROD_CD
AND LVL_FROM.PRD_H5_LVL  > LVL_TO.CSTM_PROD_LVL_CD

I get the ambiguous ORG_ID error on TD55.

However, if I change the names for the derived tables

EXPLAIN
SELECT LVL_FROM.*,
LVL_TO.PRNT_CSTM_PROD_CD             PRD_H3_CD,
LVL_TO.CSTM_PROD_NM                  PRD_H4_NM,
LVL_TO.CSTM_PROD_LVL_CD              PRD_H4_LVL
FROM
(
SELECT LVL_FROM.*,
LVL_TO.PRNT_CSTM_PROD_CD             PRD_H4_CD,
LVL_TO.CSTM_PROD_NM                  PRD_H5_NM,
LVL_TO.CSTM_PROD_LVL_CD              PRD_H5_LVL
FROM
(
SELECT
LVL_FROM.ORG_ID,
LVL_FROM.CSTM_PROD_CD                PRD_H6_CD,
LVL_FROM.PRNT_CSTM_PROD_CD           PRD_H5_CD,
LVL_FROM.CSTM_PROD_NM                PRD_H6_NM,
LVL_FROM.CSTM_PROD_LVL_CD            PRD_H6_LVL
FROM XEIW890T.TCPR890 LVL_FROM
WHERE LVL_FROM.CSTM_PROD_LVL_CD=6
) LVL_FROM LEFT OUTER JOIN XEIW890T.TCPR890 LVL_TO
ON LVL_FROM.ORG_ID       = LVL_TO.ORG_ID
AND LVL_FROM.PRD_H5_CD   = LVL_TO.CSTM_PROD_CD
AND LVL_FROM.PRD_H6_LVL  > LVL_TO.CSTM_PROD_LVL_CD
) LVL_FROM LEFT OUTER JOIN XEIW890T.TCPR890 LVL_TO
ON LVL_FROM.ORG_ID       = LVL_TO.ORG_ID
AND LVL_FROM.PRD_H4_CD   = LVL_TO.CSTM_PROD_CD
AND LVL_FROM.PRD_H5_LVL  > LVL_TO.CSTM_PROD_LVL_CD

I get a good explain.

Hope this helps explain it,

Glen

	<Prev	Next>		<<First	<Prev	Next>	Last>>