CODEWITHSUNDEEP
sqldatabaseteradata
Alpha001
Alpha001

Reputation: 371

Using Case-When in Teradata

Simple problem but driving me crazy for a while. Have following table in Teradata:

A       B
112211  
113311  56
226144  61
996688  66
005400  
771277

For the blank fields in col-B need to pull the numbers from col-A like:

SELECT A
CASE 'B'
WHEN 'B' IS NULL THEN SUBSTR('A',3,4)
END AS 'B'
FROM TABLE_T1;

Referred some documentation but not getting what's going wrong in the above query. This query will work on MySQL but why not in Teradata.

Expected O/P:

A       B
112211  22
113311  56
226144  61
996688  66
005400  54
771277  12

Upvotes: 1

Views: 6217

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

You can use this form of case expression note the difference is case when ... not case b when .... Also use single quotes for literals only, use double quotes for identities. (Note sure why you used single quotes.)

SELECT
      A
    , CASE WHEN B IS NULL THEN SUBSTR(A,3,4) 
           ELSE B 
      END AS "B"
FROM TABLE_T1;

But you can also use coalesce() for this I believe:

SELECT
      A
    , coalesce(B ,SUBSTR(A,3,4) AS "B"
FROM TABLE_T1;

Upvotes: 3

Related Questions