RE:Change if

[Chanty4u]

Hi all ,

swuehl,M G,Peter Cammaert,jagan mohan rao appala,Marco Wedel

How to change the below if statements to case statements?

Anyone help me on this.

If(MATCH_TYPE='VOD', 'Tier 1',

          If(NOT MATCH_TYPE(NETWORK_TYPE,'Broadcast','Cable') AND NOT IsNull(PROGRAM_NAME),

               If(Num#(Left(TIMESHIFT_INDICATOR_ID,2)) >= 10 AND Num(Left(TIMESHIFT_INDICATOR_ID,2)) <= 16

AND Count(Distinct USERS_META_ID) <> 0,'Tier 2','Tier 1')

          )

   ) as Field_name

if(MATCH_TYPE = 'VOD','Tier 1',

if(MATCH_TYPE <> 'VOD' AND NETWORK_TYPE IN ('Broadcast', 'Cable') AND PROGRAM_NAME <> null,

if(TIMESHIFT_INDICATOR_ID = 10 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 11 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 12 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 13 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 14 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 15 AND SAMPLE <> 0,

if(TIMESHIFT_INDICATOR_ID = 16 AND SAMPLE <> 0,'Tier 2','Tier 1')))))))))

as field_name

Message was edited by: suri qv

[jagan]

Why not you can use this in load statement like below.

Data:

LOAD

*,

If(MATCH_TYPE='VOD', 'Tier 1',

          If(NOT MATCH_TYPE(NETWORK_TYPE,'Broadcast','Cable') AND NOTIsNull(PROGRAM_NAME),

               If(Num#(Left(TIMESHIFT_INDICATOR_ID,2)) >= 10 AND Num(Left(TIMESHIFT_INDICATOR_ID,2)) <= 16

AND Count(Distinct USERS_META_ID) <> 0,'Tier 2','Tier 1')

          )

   ) as Field_name;

SELECT*

FROM TableName;

[Peter_Cammaert]

Ah, but a SQL SELECT statement does support a CASE construct inside column specifiers.

See here: CASE (Transact-SQL)

The examples at the bottom show you how to use this technique.

Note: there may be subtle differences if you use another RDBMS. But the general format is about the same.

Best,

Peter

[Chanty4u]

Thanks for the quick reply peter,

i will check this out.