I have the following query
SELECT DISTINCT
pt.incentive_marketing,
pt.incentive_channel,
pt.incentive_advertising
FROM test.pricing pt
WHERE pt.contract_id = 90000
group by 1,2,3
order by pt.incentive_marketing;
The above query returns the o/p as shown in the attached image
However I want to replace all null values by 0 using COALESCE Please let me know how this can be achieved in above SELECT query
Now I further modified the query using coalesce as below
SELECT
COALESCE( pt.incentive_marketing, '0' ),
COALESCE(pt.incentive_channel,'0'),
COALESCE( pt.incentive_advertising,'0')
FROM test.pricing pt
WHERE pt.contract_id = 90000
group by 1,2,3
the result of which is as attached in image 2.
I still receive one row with blank values
You can use COALESCE
in conjunction with NULLIF
for a short, efficient solution:
COALESCE( NULLIF(yourField,'') , '0' )
The NULLIF
function will return null if yourField
is equal to the second value (''
in the example), making the COALESCE
function fully working on all cases:
QUERY | RESULT
---------------------------------------------------------------------------------
SELECT COALESCE(NULLIF(null ,''),'0') | '0'
SELECT COALESCE(NULLIF('' ,''),'0') | '0'
SELECT COALESCE(NULLIF('foo' ,''),'0') | 'foo'