Sql ELSE case is not working

Question

I have below query I am trying to show message 'No SubSource for this RAO' when there is no 'tblOrganisation.Name', please have look into below query, it working fine but not showing my message when there no organisation.name returned

DECLARE @RAOID INT, @ORGID INT

SET @ORGID = 28
SET @RAOID = (SELECT RAOID FROM tblOrganisation WHERE ORGID = @ORGID)

PRINT @RAOID

IF @RAOID IS NOT NULL
BEGIN
  SELECT 
    CASE 
      WHEN tblOrganisation.ORGID IS NOT NULL THEN tblOrganisation.ORGID 
      ELSE 'No ORGID' 
    END AS ORGID,
    CASE 
      WHEN tblOrganisation.Name IS NOT NULL THEN tblOrganisation.Name 
      ELSE 'No SubSource for this RAO' 
    END as SUBSOURCENAME
  FROM tblOrganisation 
  LEFT OUTER JOIN tblSubGroup ON tblOrganisation.SubGroupID = tblSubGroup.SubGroupID
  WHERE
    tblSubGroup.RAOID = @RAOID
END

Thanks.

Best Regards, MS

Answer 1

...
FROM tblOrganisation 
LEFT OUTER JOIN
tblSubGroup ON tblOrganisation.SubGroupID = tblSubGroup.SubGroupID
--Need to change WHERE clause
    AND
    tblSubGroup.RAOID = @RAOID
...

The original WHERE clause is changing the OUTER JOIN into an INNER JOIN, so you need to filter there.

Also, some other points:

• Why join on tblSubGroup? It is not used for ouput. Unless you are checking for this with CASE

• Is tblOrganisation.Name null or empty string?

  ISNULL(NULLIF(tblOrganisation.Name, ''), 'No SubSource for this RAO') AS ORGID

Try this, this may be more what you want...

DECLARE @RAOID INT, @ORGID INT

SET @ORGID = 28
SET @RAOID = (SELECT RAOID FROM tblOrganisation WHERE ORGID = @ORGID)
PRINT @RAOID

IF @RAOID IS NOT NULL
BEGIN
    SELECT 
        ISNULL(tblSubGroup.ORGID), 'No ORGID') AS ORGID,
        ISNULL(NULLIF(tblSubGroup.Name, ''), 'No SubSource for this RAO') AS SUBSOURCENAME
    FROM
        tblOrganisation 
        LEFT OUTER JOIN
        tblSubGroup ON tblOrganisation.SubGroupID = tblSubGroup.SubGroupID
                AND
                tblSubGroup.RAOID = @RAOID
END

However, I'm still not sure what you want...

Answer 2

DECLARE @RAOID INT, @ORGID INT

SET @ORGID = 28
SET @RAOID = (SELECT RAOID FROM tblOrganisation WHERE ORGID = @ORGID)

PRINT @RAOID

IF @RAOID IS NOT NULL
BEGIN
  SELECT 
    CASE 
      WHEN tblOrganisation.ORGID IS NOT NULL THEN tblOrganisation.ORGID 
      ELSE 'No ORGID' 
    END AS ORGID,
    **CASE isnull(tblOrganisation.Name,'')
      WHEN '' THEN 'No SubSource for this RAO'
      ELSE  tblOrganisation.Name**
    END as SUBSOURCENAME
  FROM tblOrganisation 
  LEFT OUTER JOIN tblSubGroup ON tblOrganisation.SubGroupID = tblSubGroup.SubGroupID
  WHERE
    tblSubGroup.RAOID = @RAOID
END

Do like this it may be work for you.