Archives of the TeradataForum

Message Posted: Fri, 02 May 2008 @ 11:21:49 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi

I am not able to run this query on teradata, syntactically it is correct but I dont know why it is giving an error message.

I am getting error message something as

illegal expression in when clause of case Expression

And I am submitting query using SQL/Assistant

Do you have any alternative way to solve this query or any reason that why I am not able to run this query on teradata....

Query:

     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
                                        ELSE NULL
                                        END)

Can't I use subquery in the case Like this????

I am not able to run this Part of the query.

If I use hard coded values instead of select in the in operator as m.member_partner_plan_id in(1000,1001,1002)

It's working But I don't know why it is not working with this type or query...

Can you tell me any specific reason for this

Or is there any alternative way to solve this problem?

     Case
        When m.member_partner_plan_id
               in(select ssxs.subscriberserviceid
                    from subscriber_service_x_source ssxs)
            then m.member_partner_plan_id

Thank you

Regards,

Narendra

	<Prev	Next>		<<First	<Prev	Next>	Last>>