All Forums Database
robin-root 1 post Joined 03/08
26 Mar 2008
Select into a select

Hello everybody, I am trying to run this query but Teradata returns an error between the comma and the select. I don't know how to proceed to run this query. Is it possible to run it, and is it possible to run a select into another select? The query I am working with is here below.SEL CASE WHEN c.branch_family_id between 21 and 23 THEN 'TRADER' WHEN c.branch_family_id between 31 and 53 THEN 'CBU' WHEN c.branch_family_id between 11 and 15 THEN 'HoReCa' WHEN c.branch_family_id =99 THEN 'Intern' END HTC ,extract(year from a.date_of_day) ,x.lead_item_ID ,x.lead_item_Desc ,d.CATMAN_BUY_DOMAIN_ID , ( sel count (distinct (z.cust_no*100 || z.home_store_id) ) from dw_v_cust_invline_suppl z where z.date_of_day between '2007-01-10' AND '2007-01-23') as Number_of_Customer ,sum(case when a.date_of_day between'2007-01-10' AND '2007-01-23' then a.cu_sell_prom_val_nsp - a.cu_sell_prom_val_nnbp else 0 end) As GP702 ,sum (case when a.date_of_day between'2007-01-10' AND '2007-01-23' then a.cu_sell_prom_val_nsp else 0 end) As MM702 ,sum(case when a.date_of_day between'2007-01-24' AND '2007-02-06' then a.cu_sell_prom_val_nsp - a.cu_sell_prom_val_nnbp else 0 end) As GP703 ,sum (case when a.date_of_day between'2007-01-24' AND '2007-02-06' then a.cu_sell_prom_val_nsp else 0 end) As MM703 ,sum(case when a.date_of_day between'2007-02-07' AND '2007-02-20' then a.cu_sell_prom_val_nsp - a.cu_sell_prom_val_nnbp else 0 end) As GP704 ,sum (case when a.date_of_day between'2007-02-07' AND '2007-02-20' then a.cu_sell_prom_val_nsp else 0 end) As MM704FROM dw_v_cust_invline_suppl aINNER JOIN DW_V_CUSTOMER b ON a.home_store_id = b.home_store_id AND a.cust_no = b.cust_noINNER JOIN DW_V_CUST_BRANCH c ON b.branch_id = c.branch_idinner join dw_v_article d on a.art_no=d.art_noRIGHT OUTER join CZECCP_MSI_D.SL_MS_Database x on a.art_no=x.art_no WHERE (Extract(year from a.date_of_day) = 2007or Extract(year from a.date_of_day) = 2008)and d.CATMAN_BUY_DOMAIN_ID in (160)and a.prom_id in (70002,70003,70004,70005,70006,70007,70008,70009,70010 ,70011,70012,70013,70014,70015,70016,70017,70018,70019 ,70020,70021,70022,70023,70024,70025,70026,80001,80002 ,80003,80004,80005,80006) GROUP BY 1,2,3,4,5;

James Coutre 5 posts Joined 03/08
26 Mar 2008

Your select in a select should consist of this.SelectFrom (Select From Where)WhereJC

Sunar 59 posts Joined 02/08
27 Mar 2008

As of my knowledge, u cannot have a select in the column level. we can have select in from caluse.

dhirajpalse 36 posts Joined 01/08
27 Mar 2008

Hi,I also think we cant use "select" as part of the column list in select statement.I think in case of your query, we can use select as a derived table and join it with other tables to avoide cartesian product.I think the foll. qury should work..SEL CASEWHEN c.branch_family_id between 21 and 23 THEN 'TRADER'WHEN c.branch_family_id between 31 and 53 THEN 'CBU'WHEN c.branch_family_id between 11 and 15 THEN 'HoReCa'WHEN c.branch_family_id =99 THEN 'Intern'END HTC,extract(year from a.date_of_day),x.lead_item_ID,x.lead_item_Desc,d.CATMAN_BUY_DOMAIN_ID,sum(case when a.date_of_day between'2007-01-10' AND '2007-01-23' then a.cu_sell_prom_val_nsp - a.cu_sell_prom_val_nnbp else 0 end) As GP702,sum (case when a.date_of_day between'2007-01-10' AND '2007-01-23' then a.cu_sell_prom_val_nsp else 0 end) As MM702,sum(case when a.date_of_day between'2007-01-24' AND '2007-02-06' then a.cu_sell_prom_val_nsp - a.cu_sell_prom_val_nnbp else 0 end) As GP703,sum (case when a.date_of_day between'2007-01-24' AND '2007-02-06' then a.cu_sell_prom_val_nsp else 0 end) As MM703,sum(case when a.date_of_day between'2007-02-07' AND '2007-02-20' then a.cu_sell_prom_val_nsp - a.cu_sell_prom_val_nnbp else 0 end) As GP704,sum (case when a.date_of_day between'2007-02-07' AND '2007-02-20' then a.cu_sell_prom_val_nsp else 0 end) As MM704FROM dw_v_cust_invline_suppl aINNER JOIN( sel count (distinct (z.cust_no*100 || z.home_store_id) ) from dw_v_cust_invline_suppl zwhere z.date_of_day between '2007-01-10' AND '2007-01-23') as Number_of_Customer (cnt)ON <CONDITION JOINING Number_of_Customer with other tables to avoid cross product)INNER JOIN DW_V_CUSTOMER bON a.home_store_id = b.home_store_idAND a.cust_no = b.cust_noINNER JOIN DW_V_CUST_BRANCH cON b.branch_id = c.branch_idinner join dw_v_article don a.art_no=d.art_noRIGHT OUTER join CZECCP_MSI_D.SL_MS_Database xon a.art_no=x.art_noWHERE(Extract(year from a.date_of_day) = 2007or Extract(year from a.date_of_day) = 2008)and d.CATMAN_BUY_DOMAIN_ID in (160)and a.prom_id in (70002,70003,70004,70005,70006,70007,70008,70009,70010,70011,70012,70013,70014,70015,70016,70017,70018,70019,70020,70021,70022,70023,70024,70025,70026,80001,80002,80003,80004,80005,80006) GROUP BY 1,2,3,4,5;

Dhiraj

You must sign in to leave a comment.