Defining two derived tables using WITH clause ??

Database
Enthusiast

Re: Defining two derived tables using WITH clause ??

Thanks Dieter.

Re: Defining two derived tables using WITH clause ??

Hi All, When I add a subquery in the with recursive, I get 'illegal use of subquery in recursive " error: in below query :WITH RECURSIVE BOM_TABLE (BU_ID,SKU_NBR,MOD_NBR,PART_NBR,LVL) AS ( select distinct pidn.bu_id, pidn.sku_num_val as SKU_NBR, pidn.cmpnt_itm_num_val as mod_nbr, pidn.cmpnt_itm_num_val as part_nbr, 0 AS LVL from (select unique cmpnt_itm_num_val, bu_id, sku_num_val from itm_pkg.part_itm_dim_new) pidn inner join itm_base.COMB_PROD_SNP_ITM_HIER sku on (sku.comb_hier_snp_itm_cd = pidn.sku_num_val and sku.snp_lob_lvl_1 = 'SOFTWARE' and sku.is_snp_flg = 'Y') union all --## BOM down to find mod/part for given SKU SELECT BOM.BU_ID, BOM.SKU_NBR, BOM.MOD_NBR, pidn.cmpnt_itm_num_val, BOM.LVL+1 AS NEWDEPTH FROM BOM_TABLE BOM JOIN (select distinct cmpnt_itm_num_val,sku_num_val,bu_id from itm_pkg.part_itm_dim_new pidn) A on bom.part_nbr = A.sku_num_val and bom.bu_id = A.bu_id where NEWDEPTH <= 25 ) --## Get final BOM resultset SELECT unique BOM.BU_ID, BOM.SKU_NBR , BOM.MOD_NBR, BOM.PART_NBR , 's_m_SFTWR_PART_SKU_MAP', current_TIMESTAMP(6), CAST( SUBSTR( CAST(CURRENT_TIMESTAMP AS CHAR(19)),1,10) AS DECIMAL(18)) ||'00',81 FROM BOM_TABLE BOM where sku_nbr <> part_nbr
Apprentice

Re: Defining two derived tables using WITH clause ??

Hi,

 

I don't know if this is the whole problem, but your derived table query contains "select unique", I think that should be "select distinct"

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Teradata Employee

Re: Defining two derived tables using WITH clause ??

Teradata actually still accepts SELECT UNIQUE  as a synonym of SELECT DISTINCT, though that usage of UNIQUE is deprecated.

 

The problem I see is that after the UNION ALL, your SELECT refers to pidn.cmpnt_itm_num_val where it appears you should use A.cmpnt_itm_num_val instead.

Re: Defining two derived tables using WITH clause ??

Thanks for the reply.

 

I can see the below part of query is having problem, as when I keep that part in a temp table and join , It works:

 

JOIN 
 (select distinct cmpnt_itm_num_val,sku_num_val,bu_id
  from
  itm_pkg.part_itm_dim_new pidn).

 

I am unable to put that subquery in join.

Any suggestions how to fix it?

 

Thanks

Shraddha

Teradata Employee

Re: Defining two derived tables using WITH clause ??

Specify the alias for the subquery result. (Currently, it's only defined within the scope of the subquery.)

 

JOIN 
 (select distinct cmpnt_itm_num_val,sku_num_val,bu_id
  from
  itm_pkg.part_itm_dim_new) pidn