Why subqueries in case are not working in teradata?

Analytics

Why subqueries in case are not working in teradata?

Please look at the query below

select
ename
from
emp e
where
not exists(select
'x'
from
dept d
where
d.deptno=case
when d.deptname in (select
deptname
from xdepartments
)
then
20
else
50
end
);

In the place of subquery "select deptname from xdepartments" , if I use hardcoded values like 100,200,3000 then it is working fine but I don't know why It is not working if I use subquery like above It's giving an error message..
Please Help me out...
I am using teradata SQL Assistant 7.1
and getting Error message as : Illegal expression in when clause of case expression.
Please help me out
Thanx

Regards,
jack
15 REPLIES
SN
Enthusiast

Re: Why subqueries in case are not working in teradata?

hi,

WHEN search conditions cannot contain SELECT statements.
try using a derived table instead.

thx
Junior Contributor

Re: Why subqueries in case are not working in teradata?

Hi Jack,
Teradata supports Scalar Subqueries only in some special cases within WHERE/HAVING.

You'll have to rewrite it to (Outer) Joins.

Dieter

Re: Why subqueries in case are not working in teradata?

I am also facing similar problem..

SEL

CASE WHEN CHANNELS NOT IN (SEL CHANNELS FROM p5p_rsa1_tbls.FFMT)  THEN 'Y' ELSE 'N' END AS CHANNEL_OK_FLAG

FROM p5p_rsa1_tbls.FFMT

Does anyone has a solution around it ??

Thanks in Advance,

Amar

Junior Contributor

Re: Why subqueries in case are not working in teradata?

Hi Amar,

scalar subqueries are most often a hidden outer join:

SEL
CASE WHEN t2.col IS NULL THEN 'N' ELSE 'Y' END
FROM t1 LEFT JOIN t2 ON t1.col=t2.col

Btw, in most DBMSes outer joins are usually more efficient than SSQs.

Dieter

Enthusiast

Re: Why subqueries in case are not working in teradata?

Hi Dieter,

I am also facing the same issue, for converting one oracle query to Teradata.

Oracle Query:

case when W_PARTY_PER_D.X_DISTRICT in (select upper(WC_ORG_EXT_XM_F.attrib_04) from wc_org_ext_xm_f where pf_org_wid = a.PR_VIS_ORG_WID)  then 0 else 1 end 

Here "a" is representing entire subquery alias which is treated as table here to join with other tables.

Please help to replicate same in Teradata.

Regards,

Ashish

Junior Contributor

Re: Why subqueries in case are not working in teradata?

Hi Sahish,

if you're on TD13.00 this syntax should run as is, otherwise you have to rewrite it as an Outer Join.

Dieter

Enthusiast

Re: Why subqueries in case are not working in teradata?

Thanks Dieter.

We are on TD 13.10 but its not working as is, so trying to rewrite as outer join.

Regards,

Ashish

Enthusiast

Re: Why subqueries in case are not working in teradata?

Hi Dieter,

 As you said the syntax should work on TD 13.00, but same is not working on 13.10. Can you pls suggest some changes to achieve same on TD.

Below is my complete query,

select distinct a.asset_wid,a.PR_VIS_ORG_WID,a.SELL_DT_WID,a.prod_wid,a.contact_wid,a.accnt_wid,

case 

when 

(a.contact_wid <> 0 and W_PARTY_PER_D.x_district is not null ) then

 (case when W_PARTY_PER_D.X_DISTRICT in (select upper(WC_ORG_EXT_XM_F.attrib_04) from wc_org_ext_xm_f where pf_org_wid = a.PR_VIS_ORG_WID) 

 then 0 else 1 end )

else 

(case when W_PARTY_ORG_D.X_DISTRICT in (select upper(WC_ORG_EXT_XM_F.attrib_04) from wc_org_ext_xm_f where pf_org_wid = a.PR_VIS_ORG_WID)

 then 0 else 1 end ) 

 end infringement

from (

SELECT 

W_ASSET_D.ROW_WID asset_wid, 

W_ASSET_F.PR_VIS_ORG_WID PR_VIS_ORG_WID, 

W_ASSET_F.SELL_DT_WID SELL_DT_WID, 

min(WC_PRODUCT_DH.PROD_WID) prod_wid,

W_ASSET_F.contact_wid contact_wid,

w_asset_f.Accnt_wid accnt_wid

FROM

W_ASSET_D, W_ASSET_F, W_PRODUCT_DH, W_INT_ORG_D ,WC_PRODUCT_DH,w_product_d

WHERE

W_ASSET_D.ROW_WID = W_ASSET_F.ASSET_WID and

W_ASSET_F.PROD_WID = W_PRODUCT_DH.PROD_WID and

W_INT_ORG_D.ROW_WID = W_ASSET_F.PR_VIS_ORG_WID AND

WC_PRODUCT_DH.LVL8ANC_PROD_ID = W_PRODUCT_DH.LVL8ANC_PROD_ID  and

w_product_d.row_wid = W_PRODUCT_DH.prod_wid and

w_product_d.x_BU_UNIT in ('TMPC','TM') and

W_INT_ORG_D.BU_NAME = 'TMPC' 

group by   W_ASSET_D.ROW_WID,W_ASSET_D.LICENSE_NO,W_ASSET_F.PR_VIS_ORG_WID,W_ASSET_F.contact_wid,w_asset_f.Accnt_wid,

W_ASSET_F.SELL_DT_WID) a ,

WC_ORG_EXT_XM_F,

W_PARTY_PER_D,

W_PARTY_ORG_D

where 

WC_ORG_EXT_XM_F.PF_ORG_WID = a.PR_VIS_ORG_WID and 

WC_ORG_EXT_XM_F.prod_wid  = a.prod_wid and

a.contact_wid = W_PARTY_PER_D.row_wid and 

a.accnt_wid = W_PARTY_ORG_D.row_wid and

a.sell_dt_wid = to_number(to_char(sysdate,'yyyymmdd'))-1 and

WC_ORG_EXT_XM_F.attrib_04 is not null

Thanks and Regards,

Ashish

Junior Contributor

Re: Why subqueries in case are not working in teradata?

Hi Ashish,

this query is actually running in Oracle? 

I don't think any other DBMS allows Scalar Subqueries based on a Dervied Table/Inline View.

But it should be easy to rewrite :-)

Move "a" into a WITH clause (Oracle supports it, too) should work:

WITH a (asset_wid,PR_VIS_ORG_WID,SELL_DT_WID,prod_wid,contact_wid,accnt_wid) AS
(
SELECT
W_ASSET_D.ROW_WID asset_wid
, W_ASSET_F.PR_VIS_ORG_WID PR_VIS_ORG_WID
, W_ASSET_F.SELL_DT_WID SELL_DT_WID
, MIN(WC_PRODUCT_DH.PROD_WID) prod_wid
, W_ASSET_F.contact_wid contact_wid
, w_asset_f.Accnt_wid accnt_wid
FROM
W_ASSET_D
, W_ASSET_F
, W_PRODUCT_DH
, W_INT_ORG_D
,WC_PRODUCT_DH
,w_product_d
WHERE
W_ASSET_D.ROW_WID = W_ASSET_F.ASSET_WID
AND W_ASSET_F.PROD_WID = W_PRODUCT_DH.PROD_WID
AND W_INT_ORG_D.ROW_WID = W_ASSET_F.PR_VIS_ORG_WID
AND WC_PRODUCT_DH.LVL8ANC_PROD_ID = W_PRODUCT_DH.LVL8ANC_PROD_ID
AND w_product_d.row_wid = W_PRODUCT_DH.prod_wid
AND w_product_d.x_BU_UNIT IN ('TMPC','TM')
AND W_INT_ORG_D.BU_NAME = 'TMPC'
GROUP BY
W_ASSET_D.ROW_WID
,W_ASSET_D.LICENSE_NO
,W_ASSET_F.PR_VIS_ORG_WID
,W_ASSET_F.contact_wid
,w_asset_f.Accnt_wid
, W_ASSET_F.SELL_DT_WID
)
SELECT DISTINCT
a.asset_wid
,a.PR_VIS_ORG_WID
,a.SELL_DT_WID
,a.prod_wid
,a.contact_wid
,a.accnt_wid
, CASE
WHEN (
a.contact_wid <> 0
AND W_PARTY_PER_D.x_district IS NOT NULL
)
THEN (CASE
WHEN W_PARTY_PER_D.X_DISTRICT IN
(
SELECT
UPPER(WC_ORG_EXT_XM_F.attrib_04)
FROM
wc_org_ext_xm_f
WHERE
pf_org_wid = a.PR_VIS_ORG_WID
)
THEN 0
ELSE 1
END )
ELSE (CASE
WHEN W_PARTY_ORG_D.X_DISTRICT IN
(
SELECT
UPPER(WC_ORG_EXT_XM_F.attrib_04)
FROM
wc_org_ext_xm_f
WHERE
pf_org_wid = a.PR_VIS_ORG_WID
)
THEN 0
ELSE 1
END )

END infringement
FROM
a
, WC_ORG_EXT_XM_F
, W_PARTY_PER_D
, W_PARTY_ORG_D
WHERE
WC_ORG_EXT_XM_F.PF_ORG_WID = a.PR_VIS_ORG_WID
AND WC_ORG_EXT_XM_F.prod_wid = a.prod_wid
AND a.contact_wid = W_PARTY_PER_D.row_wid
AND a.accnt_wid = W_PARTY_ORG_D.row_wid
AND a.sell_dt_wid = TO_NUMBER(TO_CHAR(SYSDATE,'yyyymmdd'))-1
AND WC_ORG_EXT_XM_F.attrib_04 IS NOT NULL

I don't know about the business logic but it seems you might also fully remove the Scalar Subqueries with some OLAP functions, which should result in better performance (in both TD and Oracle)

Dieter