0

I have this ID column coming as NULL even though I have put a NOT NULL expression. It's not any empty string issue or any special character issue because I tried using this expression:

AND  COALESCE(SYSTEM_ID,ASSET_NUMBER,SERIAL_NUMBER,'X')  <>'X'

In my where clause (same in select) and X comes in output, why is this happening - I have provided sample select code. I am able to solve it by putting the filter in Having clause or using an outer query

SELECT COALESCE(COL1,COL2,COL3) AS ID,
       COL4,
       ...
       COL16
FROM TABLE
WHERE COALESCE(COL1,COL2,COL3) IS NOT NULL
GROUP BY 
      COL4,
      ...
      COL16
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Nandini sharma
  • 1
  • 2
    Well given the example query, it's impossible for `ID` to be NULL. What *could* however result in it being NULL depends on your actual query. For example, if your actual query is `WHERE COALESCE(...) IS NOT NULL OR 1 = 1`; obviously that's a silly example but you get my point... if it's really NULL then some other condition in the query is causing that row to be returned. – 404 Mar 26 '19 at 20:32
  • 2
    Yes, you should post a complete query that exhibits the problem, ideally with sample data to trigger it. – Laurenz Albe Mar 27 '19 at 07:37

0 Answers0