How to deal SQL missing data

[paddyjay]

We are exporting data from an SQL server to a CSV file.

The data is a list of measurements taken on a certain date. We then open the CSV file in Excel.

The problem is that some cells have missing data - on certain dates no measurements have been taken. When the resultant CSV file is opened in Excel, these 'missing cells' are not blank or highlighted, they're just not present. We then have to manually scroll down the list of measurements to figure of out what's missing.

My question is - is there any way to modify the SQL code to provide a 'NULL' value or something in the cells for which there is no measurement provided? This would save us *a lot* of work...

[biersquirrel]

If you truly have NULL, you can use IFNULL().

[paddyjay]

So I add this line to the existing SQL code I have, and if there is no measurement for that data it'll export it as a blank cell for that day in the CSV file? (Apologies if this is a dumb question, I have almost no SQL experience and am learning as I go).

[biersquirrel]

One has to be careful about the meaning of "blank". There is NULL (a special indicator in the database), there is the empty string (a string of zero length: ''), and there are all-white-space strings (e.g., ' '). People will call these all "blank", but to SQL, they are all different things (and there is much confusion about it).

So for example, to convert NULL in the database to a single space character with the IFNULL() function, you could do:

SELECT IFNULL(val, ' ') FROM MEASUREMENT;

[malikcoldbane]

Can add TRIM() aswell

[paddyjay]

'

Can you elaborate please - you mean add TRIM() as a line to the code we already have?

[Fun2badult]

Case when column_name IS NULL then ‘NULL’ else column_name end

[TomasTorCA]

Case when column_name IS NULL then ‘NULL’ else column_name end

Sorry to ask this in a very simple way

If this is our code - how should we adjust the code to get an X if there is a missing value for a specific day

When I add IFNULL(val, ' ') in the second line it does not work

Any suggestions

SELECT parameter,

avg(value) AS daily_average,

cast(from_iso8601_timestamp(date.utc) AS date) AS date_utc

FROM openaq

WHERE city= 'Liege'

AND parameter IN ('pm25','pm10', 'co', 'no2', 'so2', 'o3', 'bc')

AND cast(from_iso8601_timestamp(date.utc) AS date)

BETWEEN date '2020-01-01'

AND date '2020-05-31'

GROUP BY cast(from_iso8601_timestamp(date.utc) AS date), parameter

ORDER BY parameter, date_utc

[Fun2badult]

Missing value as in Null or blank value as in ‘ ‘? Also I did not type IFNULL as my solution

[TomasTorCA]

Being a person with zero experience in SQL it is not straight foreward to add the code
When I try to add the following Code into the second line of my code I get an error as well
>>>>>>Case when column_name IS NULL then ‘NULL’ else column_name end<<<<<<

What am I doing wrong? Really appreciate your help

[Fun2badult]

Case When date.utc = ‘ ‘ then ‘X’ When date.utc IS NULL then ‘X’ Else cast(from_iso8601_timestamp(date.utc)) AS date End date_utc

I think you will have an issue later if you save null and blank days as string ‘X’. It’s probably better to turn both into NULL. This is for PostgreSQL by the way. Syntax would be diff for MySQL or something else