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
-- If you are stuck at something .... consider it an opportunity to think anew.
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 :)
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
-- If you are stuck at something .... consider it an opportunity to think anew.
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.
-- If you are stuck at something .... consider it an opportunity to think anew.
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
Yes, by DDL I meant table definition .... though it is abbreviation of Data Definition Language.
-- If you are stuck at something .... consider it an opportunity to think anew.
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,
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