Multiple Case Statements

UDA

Multiple Case Statements

I've been working on this for hours!  Literally hours and I can't seem to get my statement to work.  I'm converting a decode statement. and I can get

one of them to work.  But I can't get them all to work the way they currently work in the Decode. 

DECODE(DECODE(A.FIRST_NM,NULL, NULL, 0,0,1) + DECODE(A.LAST_NM,NULL, NULL, 0,0,1),NULL,NULL,0,0,1,0,1)

                        + DECODE(A.STR_CN,NULL, NULL, 0,0,1) + DECODE(A.ISSU_ID,NULL, NULL, 0,0,1)

                        + DECODE(A.DOB_CN,NULL, NULL, 0,0,1) as PA_SCORE,

here's what I got to work in TD:

Case  when A.APPL_DOB_CFRM_CN  is null then null

 when A.APPL_DOB_CFRM_CN  = 0 then 0

else 1 end as PA_SCORE

when I try to add in just one other case I get error after error after error.  I've tried several different ways and none work.

help is greatly appreciated!?

Don

7 REPLIES
Teradata Employee

Re: Multiple Case Statements

Hi,

You can surely use CASE combinations .... better to use paranthesis for clearer SQL.

Another option is to use a DECODE UDF for Teradata, available at: http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions

In case you cant use UDF and has to use CASE statement, please share DDL, sample data, exact SQL you are executing along with exact error you get.

Regards, Adeel

Re: Multiple Case Statements

unfortunately I can't download anything where I work. 

I realize I can use case combo's.  I just don't know how, which is why I posted the code :)

Teradata Employee

Re: Multiple Case Statements

As requested earlier, please share DDL, data and SQL you get errors in!

The SQL you wrote above is perfectly fine:

Case  when A.APPL_DOB_CFRM_CN  is null then null

 when A.APPL_DOB_CFRM_CN  = 0 then 0

else 1 end as PA_SCORE

But, how exactly are you translating "+" gives you error, and you have not shared code for that.

Please do so, if you need help.

Regards, Adeel

Teradata Employee

Re: Multiple Case Statements

Hi,

Please allow me to say one thing .... if you are not sharing DDLs it makes our life horrible IF we chose to assist in your issues. Reason being .... now i'll have to figure out all the columns write DDL and then figure out whats the issue. And even then it may not be fixed because there might be some issues in DDL itself - thats for requesting for DDLs again and again with no luck. :)

Regarding your query, you are not using END for CASE and starting a new one. Please re-do your SQL as below:

SELECT

CASE

WHEN Col1 IS NULL THEN NULL

WHEN Col1 = 0 THEN 0

WHEN Col1 = 1 THEN 1

ELSE 1 END

||

CASE 

WHEN Col2 IS NULL THEN NULL

WHEN Col2 = 0 THEN 0

WHEN Col2 = 1 THEN 1

ELSE 1 END

FROM Table1;

Regards, Adeel

-- SQL is written on the fly .... so it may not be executable as is.

Re: Multiple Case Statements

what's a ddl?  I'm very new to TD.  I don't mean to make this complicated... Data definistion?

currently the columns are names column and an ID column

We are trying to build a basic scoring system that looks at a few different columns.  If there is a name in the column it gets a 1 the end result can be a number 0 to 5

does that help.  Again thank you

Teradata Employee

Re: Multiple Case Statements

Yes, by DDL I meant table definition .... though it is abbreviation of Data Definition Language.

Re: Multiple Case Statements

a co worker solved the issue so here is the finished product for those in need.  Looks like it was a comma issue on my end.  so simple...

 Case

           When  A.FIRST_NM is Null Then Null

           When   A.FIRST_NM= 0 Then 0

           Else 1 End as  A.FIRST_NM,

           Case

           When   A.LAST_NMis Null Then Null

           When  A.LAST_NM= 0 Then 0

           Else 1 End as A.LAST_NM,

          Case

           When    A.STR_ADis Null Then Null

           When   A.STR_AD= 0 Then 0

           Else 1 End as  A.STR_AD,

           Case

           When   A.ID is Null Then Null

           When  A.ID= 0 Then 0

           Else 1 End as A.ID,