SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Holland
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL coalesce function

    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?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i think you want to reverse your join:

    Code:
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Holland
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •