Sql Query If Null I Want Return Value 0

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi, All

i'm facing problem with this query, i want this query when return empty then i want fill value 0,

My Query Is:

SQL

select distinct sm.EmpCodeC,sm.EmpNameC,(select count(UserId) from CanteenLogs where TimeEntry between '01/Oct/2014 09:00:00 AM' and '01/Oct/2014 10:00:00 AM' and userId='20') as 'BreakFast' from CanteenLogs as rd inner join StaffMaster as sm on rd.UserID=sm.UserIdN and rd.UserID='20'

Here i'm passing value 20, if userid=20 returns empty i want BreakFast column as value 0

Posted 27-Nov-14 23:58pm

prasanna.raj

Updated 27-Nov-14 23:59pm

v2

Add a Solution

2 solutions

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)

Maciej Los · Accepted Answer · 2014-11-28T00:10:00

Solution 2

Use COALESCE[^] function, which replaces NULL with passed value.

SQL

SELECT COALESCE(NULL, 0) --returns 0

Posted 28-Nov-14 0:10am

Maciej Los

Shweta N Mishra · Accepted Answer · 2014-11-28T00:04:00

Solution 1

check this

SQL

select distinct 
   sm.EmpCodeC,sm.EmpNameC,
  Isnull((select count(UserId) from CanteenLogs where TimeEntry between '01/Oct/2014 09:00:00 AM' and '01/Oct/2014 10:00:00 AM' and userId='20'),0) as 'BreakFast' 
from CanteenLogs as rd inner join StaffMaster as sm on rd.UserID=sm.UserIdN and rd.UserID='20

and this when no record returned

SQL

select distinct
   sm.EmpCodeC,sm.EmpNameC,
  Isnull((select count(UserId) from CanteenLogs
                 where TimeEntry between '01/Oct/2014 09:00:00 AM' and '01/Oct/2014 10:00:00 AM' and userId='20'),0) as 'BreakFast'
 into #tempfile
from CanteenLogs rd inner join StaffMaster sm on rd.UserID=sm.UserIdN and rd.UserID='20'

IF Exists (select top 1 * from #tempfile)
select * from Outputdata
else
select '' EmpCodeC ,'' EmpNameC ,0 BreakFast

Posted 28-Nov-14 0:04am

Shweta N Mishra

Updated 28-Nov-14 2:12am

v3

Comments

prasanna.raj 28-Nov-14 6:06am

its return empty ...

prasanna.raj 28-Nov-14 6:08am

i need return value 0, so that where i want use 0.

Shweta N Mishra 28-Nov-14 6:12am

does there no record return for your query or just 'BreakFast' column is empty

prasanna.raj 28-Nov-14 6:15am

it possible to set value 0, when return column is empty.

Shweta N Mishra 28-Nov-14 6:21am

if there is no record return then not,but there is other way to do that. first can you answer my question do you get any record out of your selection even null ?

Shweta N Mishra 28-Nov-14 6:21am

Post your result which you get after running your query.

prasanna.raj 28-Nov-14 6:23am

i'm expecting after run this query
BreakFast column =0

Shweta N Mishra 28-Nov-14 6:29am

let me ask you this way do you get values for sm.EmpCodeC,sm.EmpNameC and for breakfast as NULL now ?

prasanna.raj 28-Nov-14 6:31am

yes it's returns null when using userID='20'

Shweta N Mishra 28-Nov-14 6:32am

and is there a value for sm.EmpCodeC,sm.EmpNameC ?

prasanna.raj 28-Nov-14 6:39am

Actually i want to try join two table like 'tabA','tabB' and from that two table i want to get BreakFast and Lunch and Dinner, that's nothing issue i got that even though some userid not it 'tabB' which available in tabA, so when i try the userid not in 'tabB' it returns empty row, so that i want to fill as 0.

Shweta N Mishra 28-Nov-14 6:43am

thast what i need to know that are you getting an empty row or a row with null values.

updating my solution for filling your data when you get a empty row.

prasanna.raj 28-Nov-14 6:53am

k post ur query... i ll try...

Shweta N Mishra 28-Nov-14 6:54am

updated in solution

prasanna.raj 28-Nov-14 7:28am

i Got Error! i want to change anything...
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'from'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'else'.

Shweta N Mishra 28-Nov-14 8:12am

check now

Shweta N Mishra 28-Nov-14 9:36am

mark as accepted and rate it if it helped you.

Maria Lopez 4-Dec-14 8:21am

So are you helping others or trying to earn points.

Shweta N Mishra 4-Dec-14 8:28am

And whats bad about it ?

Do you warn points without helping ? i dont . and dont you see the comments after the user reply at the end.

prasanna.raj 29-Nov-14 0:54am

thans yar but i find another way...

Shweta N Mishra 1-Dec-14 4:12am

np:)

post your solutions also for others reference