Displaying 'No Entities' as a output value of a column when query does not retrieve any value as output

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 4-Nov-13 23:36pm

Karthik Raj

Updated 5-Nov-13 0:27am

v2

Add a Solution

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

3 solutions

Solution 3

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!

Posted 5-Nov-13 1:15am

Bhuvanesh Mohankumar

Updated 5-Nov-13 1:21am

v2

Solution 1

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

Posted 5-Nov-13 0:21am

Raja Sekhar S

Updated 5-Nov-13 0:22am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amitava Bag (Kolkata) · Accepted Answer · 2013-11-05T00:29:00

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