SQL coalesce function

[balkem]

Hi guys,

I have a problem with a COALESCE function. The query is as followed:

SELECT CASE when branches.Location_code is null then 'error site'
else active.Location_code end as Location_code, active.Workstation_id
, active.Last_logged, active.report_date FROM active
LEFT OUTER JOIN branches ON active.Location_code = branches.Location_code
AND branches.Deleted_branches = '0'

I would like to add three more columns from the table branches to the view. Branch name, Type site and Status. And this information should be shown with every row listed in the view.

But it’s not working. So far I managed to show those three columns only when the Location code from both tables are matching. Is it possible
to show this information for every row in the view?

[longneck]

i think you want to reverse your join:

SELECT
	CASE
		WHEN branches.Location_code is null
			THEN 'error site'
			ELSE active.Location_code
		END as Location_code
	, active.Workstation_id
	, active.Last_logged
	, active.report_date

FROM branches

LEFT OUTER JOIN active ON
	active.Location_code = branches.Location_code

AND branches.Deleted_branches = '0'

this will list all branches regardless of whether or not there is an entry in active. the way you wrote it, there has to be an entry in the active table.

[balkem]

Thanx for the quick reply.

Yeah, i tried that but if I reverse the JOIN then i am not getting the code:
'error site' for every row from the table ACTIVE if the location codes are not matching. And thats the main reason why i used COALESCE

Eventually i want to export this view to excel , and users then can manually see why
a location code didnt match up...