I have to convert Oracle SQL into Teradata SQL, there is a NVL function used in the SQL as follows:
NVL(NVL(select abc from Tablename where ddf = 'CUSTOMER_TYPES' ), ats_contract_code), 'N/A') contract_type
I have tried to convert the same using COALESCE but I don't know whether we can write SQL statement inside COALESCE function, Can I use NULLIF instead of COALESCE.
COALESCE(COALESCE( (select abc from Tablename where ddf = 'CUSTOMER_TYPES' ) , ats_contract_code), 'N/A') contract_type
A quick response is highly appreciated.
you were quite close:
COALESCE(COALESCE( (select abc from Tablename where ddf = 'CUSTOMER_TYPES' ) , ats_contract_code, 'N/A'
But I would always try to rewrite those Scalar Subqueries, in your case it will be a LEFT JOIN to Tablename.
Thanks for the quick response, I have tried this SQL but it spit out the error 3706: [expected something between '(' and the select keyword]. I'm wondering there is some problem with brackets .. i guess!
) , ats_contract_code,
there probably was a cut&paste problem.
COALESCE( (select abc from Tablename where ddf = 'CUSTOMER_TYPES' ) , ats_contract_code, 'N/A')
If this is still not working you run a quite old version of TD :-)
TD12 does not support Scalar Subqueries like yours within a CASE/COALESCE, you need TD13.
But you can still rewrite using a Left Join.