Toad World® Forums

Possible to return non-existent fields?

SQL beginner here. In my query one of the columns is interesting in that it exists for some records but not for others (as opposed to existing with a null value).
In the attached file, the column in question is PROVIDERS_GOALS_DATA.NOVEMBER_HOURS.

Specifically, I’m reporting on staff monthly productivity. Our agency has 10 programs, but a staff’s productivity hours are only charged off to one or two programs. The tricky part is that sometimes staff will charge off hours to a different program, but there is no record in the database linking the staff to that program (although I still need to report on those hours).

Is there a way to set up the query to return 0 for PROVIDERS_GOALS_DATA.NOVEMBER_HOURS whenever this occurs?

Thanks very much in advance, and please let me know if more info is needed.

SQL.txt (2.14 KB)

Just needed a Left Join

I don’t know if this is something you’re still working on, but I just noticed your post. If you’re already past this, maybe it will help someone else.

Without knowing your tables, my best guess is attached. In my experience, performance is best if you order your joins so that left joins come last. Also, if you have a left join, you need to take references to the left joined table out of the where clause and put them into the join clause or you won’t see the rows where there is no match (defeating the purpose of a left join). In order to return a 0 for NOVEMBER_HOURS, use the COALESCE function. This function takes in n arguements separated by commas and returns the first non-null value. So, COALESCE(NOVEMBER_HOURS,0) returns 0 when a matching row is now found on PROVIDERS_GOALS_DATA. This function works in DB2 LUW. I’m not sure the platform you’re working with. If COALESCE doesn’t work, try VALUE(arg1,arg2). It works identically and may be more universal. If that doesn’t work, go with the old standby: CASE WHEN NOVEMBER_HOURS IS NULL THEN 0 ELSE NOVEMBER_HOURS END

Hope this helps
Revised SQL.txt (2.16 KB)