Select into a select

Database

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 MM704

FROM dw_v_cust_invline_suppl a

INNER JOIN DW_V_CUSTOMER b
ON a.home_store_id = b.home_store_id
AND a.cust_no = b.cust_no

INNER JOIN DW_V_CUST_BRANCH c
ON b.branch_id = c.branch_id

inner join dw_v_article d
on a.art_no=d.art_no

RIGHT OUTER join CZECCP_MSI_D.SL_MS_Database x
on a.art_no=x.art_no

WHERE

(Extract(year from a.date_of_day) = 2007
or 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;
3 REPLIES
Enthusiast

Re: Select into a select

Your select in a select should consist of this.

Select
From
(Select
From
Where)
Where

JC
Enthusiast

Re: Select into a select

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

Re: Select into a select

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

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

,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 MM704

FROM dw_v_cust_invline_suppl a

INNER JOIN

( 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 (cnt)
ON <CONDITION JOINING Number_of_Customer with other tables to avoid cross product)

INNER JOIN DW_V_CUSTOMER b
ON a.home_store_id = b.home_store_id
AND a.cust_no = b.cust_no

INNER JOIN DW_V_CUST_BRANCH c
ON b.branch_id = c.branch_id

inner join dw_v_article d
on a.art_no=d.art_no

RIGHT OUTER join CZECCP_MSI_D.SL_MS_Database x
on a.art_no=x.art_no

WHERE

(Extract(year from a.date_of_day) = 2007
or 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;