Menu
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
383
The SQL Coalesce and IsNull functions are used to handle NULL values. During the expression evaluation process the NULL values are replaced with the user-defined value. The SQL Coalesce function evaluates the arguments in order and always returns first non-null value from the defined argument list.
In PostgreSQL, NULL means no value. In other words, the NULL column does not have any value. It does not equal 0, empty string, or spaces. The NULL value cannot be tested using any equality operator like “=” “!=
The COALESCE function evaluates its arguments from left to right. It stops evaluating until it finds the first non-NULL argument. It means that all the remaining arguments are not evaluated at all. The COALESCE function returns NULL if all arguments are NULL .
If all the values in MySQL COALESCE() function are NULL then it returns NULL as the output. It means that this function does not find any non-NULL value in the list.
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' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
