Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Not able to run query in teradata...?

Status
Not open for further replies.

naren044

Programmer
May 1, 2008
7
IN
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?
 
I don't know, but it looks fancy.
Shouldn't this be answered with the EXISTS clause in SQL?
 
Or you can answer it with a left outer-join, and test the existance of a record from datasourceid
 


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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top