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
1 Answer
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;
