Replace Empty Values with 0 in Query

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

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

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.

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”

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"

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

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 :smiley:

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