Not able to run query in teradata...?

[naren044]

SELECT CASE
WHEN party_x_source.partytypeid IN
(SELECT party_x_source.datasourceid
FROM party_x_source)
THEN 1
ELSE 0
END
FROM party_x_source;

Cant i use subquery in when part of case?

 

[Hans63]

I don't know, but it looks fancy.
Shouldn't this be answered with the EXISTS clause in SQL?

 

[Hans63]

Or you can answer it with a left outer-join, and test the existance of a record from datasourceid

 

[naren044]

Hi,

Actually my real query which i am using to test datawarehouse is

Query 2:

Select
count(*)
from
mediafact_stg m
Where
m.action_id in(1004,1005)
AND not exists(select 'x'
from
Media_event me,
media_event_x_source mexs,
Subscriber_service ss1,
subscriber_service_activity ssa
Where
mexs.sourcesessionid=m.session_id
And mexs.datasourceid=163
And mexs.mediaeventid=me.mediaeventid
And me.subscriberserviceid =
Case
When m.member_partner_plan_id in(select ssxs.subscriberserviceid from subscriber_service_x_source ssxs)
then m.member_partner_plan_id

WHEN ss1.subscriberserviceid=ssa.subscriberserviceid
and
m.t_timestamp >=(cast((cast(cast(ssa.startdt as date)as varchar(20))||' '||cast(cast(ssa.starttime as time(6))as varchar(20))) as timestamp)
) and
m.t_timestamp<=(CAST((cast(cast(ssa.enddt AS DATE )as varchar(20))||' '||cast(CAST(ssa.endtime AS TIME(6)) as varchar(20)))as timestamp)

) and ss1.partyid=m.user_id
then ss1.subscriberserviceid

END)




The query which i have written is as a part of this query.

The query 1 is syntacticlly correct. If i use hardcoded values in the place of "SELECT party_x_source.datasourceid
FROM party_x_source" it's working But I dont know what is the problem if i use select instead of hardcoded values


Regards
Naren

 

[Hans63]

Hi Naren,

I don't know much about Teradata. I do know that sometimes the syntaxcheck for constructs is implemented, but the actual functionality is not yet present.

For the rest I think it is Teradata specific, so I would suggest to look into those forums.

The constructs I suggested are ways to rewrite the query in case you don't get the "Select" in de WHEN clause to work.

Regards,
Hans