Click here to Skip to main content
15,124,187 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

When I execute the following query it does not retrieve any value as out. So I want to display the text " No Entities" as the output value of the column 'a.mstr_shrt_nm_id'.

SQL
select distinct a.mstr_shrt_nm_id as Entities
FROM RCMglbl..nav_nt a, RCMglbl..enty_mstr b 
WHERE a.enty_id = b.id 
        AND office = 'sf' 
    AND mnth_end_dt = (select lst_mo_end_dt from RCMglbl..proc_dt) 
        AND mnth_end_dt is not NULL 
        AND acct_close_dt = (select cur_proc_dt from RCMglbl..proc_dt)


please reply your valuable suggestions

Thanks,
Karthik
Posted
Updated 5-Nov-13 1:27am
v2
Comments
Amitava Bag (Kolkata) 5-Nov-13 5:45am
   
Please give some sample data...
Karthik Raj 5-Nov-13 5:54am
   
Hi Amitava,

Actually the output of that query is null, but i want to display " No Entities " as the output value of the column

Hi,

I will suggest you to handle these type functionalities in Application Code, which is better way of approach.

If you want to show "No Entities".

Please find this below way, which is not a suggested way, may this logic help you to come up with better solution.

SQL
DECLARE @count INT;
SET @count = 0;

SELECT @count = COUNT(CustomerID)  AS CustomerID FROM ORDERS

IF @count == 0
BEGN
SELECT TOP 1 'No Data Exists'  AS CustomerID FROM ORDERS
END
ELSE
BEGIN
SELECT TOP 1 CustomerID  AS CustomerID FROM ORDERS
END


Please Test the Code block, I not tested and typed in Notepad.

Thanks!
   
v2
u can write some thing like...
SQL
If exists
(
  select distinct isnull(a.mstr_shrt_nm_id,'No Entites') as Entities
  FROM RCMglbl..nav_nt a, RCMglbl..enty_mstr b  
  WHERE a.enty_id = b.id 
        AND office = 'sf' 
        AND mnth_end_dt = (select lst_mo_end_dt from RCMglbl..proc_dt) 
        AND mnth_end_dt is not NULL 
        AND acct_close_dt = (select cur_proc_dt from RCMglbl..proc_dt)
)
       select distinct isnull(a.mstr_shrt_nm_id,'No Entites') as Entities
       FROM RCMglbl..nav_nt a, RCMglbl..enty_mstr b 
       WHERE a.enty_id = b.id 
             AND office = 'sf' 
             AND mnth_end_dt = (select lst_mo_end_dt from RCMglbl..proc_dt) 
             AND mnth_end_dt is not NULL 
             AND acct_close_dt = (select cur_proc_dt from RCMglbl..proc_dt)
Else
      Select 'No Entities' [Entities]

or
SQL
if 
(
 select Count(distinct isnull(a.mstr_shrt_nm_id,'No Entites')) 
 FROM RCMglbl..nav_nt a, RCMglbl..enty_mstr b
 WHERE a.enty_id = b.id
        AND office = 'sf'
    AND mnth_end_dt = (select lst_mo_end_dt from RCMglbl..proc_dt)
        AND mnth_end_dt is not NULL
        AND acct_close_dt = (select cur_proc_dt from RCMglbl..proc_dt)
) <> 0
       select distinct isnull(a.mstr_shrt_nm_id,'No Entites') as Entities
       FROM RCMglbl..nav_nt a, RCMglbl..enty_mstr b 
       WHERE a.enty_id = b.id 
             AND office = 'sf' 
             AND mnth_end_dt = (select lst_mo_end_dt from RCMglbl..proc_dt) 
             AND mnth_end_dt is not NULL 
             AND acct_close_dt = (select cur_proc_dt from RCMglbl..proc_dt)
Else
       Select 'No Entities' [Entities]

if you are expecting only one value as output u can assign it to a variable and check...
   
v2
SQL
SELECT ISNULL(InG.Entities, 'No Entites') Entities FROM
(
select distinct a.mstr_shrt_nm_id as Entities
FROM RCMglbl..nav_nt a, RCMglbl..enty_mstr b
WHERE a.enty_id = b.id
        AND office = 'sf'
    AND mnth_end_dt = (select lst_mo_end_dt from RCMglbl..proc_dt)
        AND mnth_end_dt is not NULL
        AND acct_close_dt = (select cur_proc_dt from RCMglbl..proc_dt)
) InG
RIGHT OUTER JOIN (SELECT '' AS rID) AS INQ ON InG.Entities = InG.Entities
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900