How to write SELECT statement in NULLIF or COALESCE function

Database

How to write SELECT statement in NULLIF or COALESCE function

Hi,

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.

(Convereted SQL)

COALESCE(COALESCE( (select abc from Tablename where ddf = 'CUSTOMER_TYPES' ) , ats_contract_code), 'N/A') contract_type

A quick response is highly appreciated.

Rgds

SK

6 REPLIES
Junior Contributor

Re: How to write SELECT statement in NULLIF or COALESCE function

Hi SK,

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.

Re: How to write SELECT statement in NULLIF or COALESCE function

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!

COALESCE(COALESCE( (select abc from Tablename where ddf = 'CUSTOMER_TYPES' ) , ats_contract_code, 'N/A'

Rgds

SK

Junior Contributor

Re: How to write SELECT statement in NULLIF or COALESCE function

Hi SK,

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 :-)

Re: How to write SELECT statement in NULLIF or COALESCE function

We are running TD V12

Junior Contributor

Re: How to write SELECT statement in NULLIF or COALESCE function

Hi SK, 

TD12 does not support Scalar Subqueries like yours within a CASE/COALESCE, you need TD13.

But you can still rewrite using a Left Join.

Re: How to write SELECT statement in NULLIF or COALESCE function

oh, now I get it.... thx lemme try same with left join.

Rgds (SK)