Replace Empty Values with 0 in Query

[bdean22]

I have an SQL query combining two tables as below. In the results there are a lot of empty fields in Item_Total_Cost column which makes sense. However I would like these blank results to be replaced with a 0.

SELECT “Jobs”.“Job_Number”, “Query_Job_Item_Total_Cost”.“Item_Total_Cost” FROM “Jobs” LEFT OUTER JOIN “Query_Job_Item_Total_Cost” ON “Jobs”.“Job_Number” = “Query_Job_Item_Total_Cost”.“Job_Number”.

If I add the below line it splits out the fields I want to replace with 0, but I am not sure what to do from there?
WHERE “Query_Job_Item_Total_Cost”.“Item_Total_Cost” IS NULL

[ggk]

You can use a case statement or coalesce. Syntax depending on database used.

[Wanderer]

I understand you ask how to replace the value NULL in the used table, not only in the generated table. Then you have to enter a statement at the SQL- “commandline” provided in the menu.
UPDATE Query_Job_Item_Total_Cost SET Item_Total_Cost = 0 WHERE Query_Job_Item_Total_Cost.Item_Total_Cost IS NULL

If this is your goal it would be better to define this as a NOT NULL constraint to the column with giving a default of 0

Otherwise follow @ggk and use
COALESCE(Query_Job_Item_Total_Cost.Item_Total_Cost, 0)

in your JOIN to replace NULL with 0 only in the result of your JOIN.

PS: I removed your quotation marks to prevent error for my answers, even as some may see typographic quotation marks asba feature of the site.

Can typographic quotes be switched off for ask.libreoffice.org?

Well, your “better” is apparently opposite to mine here. I find the postings to look better due to the feature in question. This site’s engine does provide a tool to present chunks of code as they should be. Some lazy or […] users do not use proper tools, but I don’t think this should be a reason to disable certain features. P. S. Irrespective of the feature, I use proper quotation marks and apostrophes here (slightly edited by @LeroyG)

[bdean22]

Thanks for the reply, I am confused by the syntax here. I tried below but it errors. Am I using this command in the wrong way?

SELECT “Jobs”.“Job_Number”, COALESCE(“Query_Job_Item_Total_Cost.Item_Total_Cost”, 0) FROM “Jobs” LEFT OUTER JOIN “Query_Job_Item_Total_Cost” ON “Jobs”.“Job_Number” = “Query_Job_Item_Total_Cost”.“Job_Number”

[cpb]

the double quotes were missing either side of the period:

COALESCE("Query_Job_Item_Total_Cost". "Item_Total_Cost", 0)

SELECT "Jobs"."Job_Number",
    COALESCE("Query_Job_Item_Total_Cost"."Item_Total_Cost", 0)
FROM "Jobs"
left join "Query_Job_Item_Total_Cost"
on "Jobs"."Job_Number" = "Query_Job_Item_Total_Cost"."Job_Number"

EDIT:
Discourse have posted a discussion showing how to format code on their site.

Discourse Guide: Code Formatting

I have made several edits here in order to apply their instructions
you should now be able to copy and paste this formatted code without error.

it does seem to work

[bdean22]

Thanks. Simple error but hard to see when I am still learning it all!