If i query a record that doesn't exist then i will get nothing returned. i'd prefer that 0 is returned in that scenario.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Here's my simple query. If I query a record that doesn't exist then I will get nothing returned. I'd prefer that 0 is returned in that scenario. Please help me out from this.Thanks in advance

SQL

SELECT *
    FROM (
        SELECT DISTINCT emp.EMP_CODE
            ,emp.EMP_NAME
            ,ROW_NUMBER() OVER (
                PARTITION BY emp.emp_code ORDER BY emp.EFFECTIVE_FROM DESC
                ) AS Row
            ,CASE 
                WHEN et.DAY = @d1
                    AND et.MONTH = @m1
                    AND et.YEAR = @y1
                    AND et.EMP_CODE = emp.EMP_CODE
                    AND et.site_id = @SITE_ID
                    THEN et.GENERAL_SHIFT
                ELSE '0'
                END AS G
            ,CASE 
                WHEN et.DAY = @d1
                    AND et.MONTH = @m1
                    AND et.YEAR = @y1
                    AND et.EMP_CODE = emp.EMP_CODE
                    AND et.site_id = @SITE_ID
                    THEN et.FIRST_SHIFT
                ELSE '0'
                END AS F
            ,CASE 
                WHEN et.DAY = @d1
                    AND et.MONTH = @m1
                    AND et.YEAR = @y1
                    AND et.EMP_CODE = emp.EMP_CODE
                    AND et.site_id = @SITE_ID
                    THEN et.SECOND_SHIFT
                ELSE '0'
                END AS S
            ,CASE 
                WHEN et.DAY = @d1
                    AND et.MONTH = @m1
                    AND et.YEAR = @y1
                    AND et.EMP_CODE = emp.EMP_CODE
                    AND et.site_id = @SITE_ID
                    THEN et.THIRD_SHIFT
                ELSE '0'
                END AS T
        FROM dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
        LEFT JOIN dbo.EMP_FPONM_EMP_TIME_SHEET et ON emp.EMP_CODE = et.EMP_CODE
        WHERE EMP.SITE_ID = @SITE_ID
            AND emp.DEPT_NAME = @DEPT_NAME
            AND emp.STATUS = 'Present'
            AND et.DAY = @d1
            AND et.MONTH = @m1
            AND et.YEAR = @y1
        ) a
    WHERE a.Row = 1

What I have tried:

If I query a record that doesn't exist then I will get nothing returned. I'd prefer that 0 is returned in that scenario.

Posted 4-Mar-16 1:35am

Member 12369428

Updated 4-Mar-16 2:46am

Add a Solution

1 solution

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)

CHill60 · Accepted Answer · 2016-03-04T02:46:00

Solution 1

That's something I would normally handle in my presentation layer but if you must do it this way then have a look at using EXISTS[^]
For example: (although you should probably invert this IF statement)

SQL

IF NOT EXISTS 
(
	SELECT emp.EMP_CODE 
        FROM dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
        LEFT JOIN dbo.EMP_FPONM_EMP_TIME_SHEET et ON emp.EMP_CODE = et.EMP_CODE
        WHERE EMP.SITE_ID = @SITE_ID
            AND emp.DEPT_NAME = @DEPT_NAME
            AND emp.STATUS = 'Present'
            AND et.DAY = @d1
            AND et.MONTH = @m1
            AND et.YEAR = @y1
)
	SELECT 0 as EMP_CODE, '' AS EMP_NAME, 0 AS Row, '0' AS G, '0' AS F, '0' AS S, '0' AS T
ELSE
	-- your original query in here
END

As an aside, I sincerely hope that you are not storing numeric values in varchar fields :-)

Posted 4-Mar-16 2:46am

CHill60

Comments

Member 12369428 18-Mar-16 5:47am

hi using your above example i completed my query but i got some other with same query
recently i registered new employee that employee data is not comining some days

CHill60 18-Mar-16 5:52am

What do you mean "some days"? Do you have an example?

Member 12369428 18-Mar-16 5:48am

Please suggest me anything thanks in advance.

Member 12369428 18-Mar-16 5:56am

ok i will post my query

Member 12369428 18-Mar-16 6:00am

IF NOT EXISTS
(
SELECT emp.EMP_CODE,emp.EMP_NAME
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_CODE
where EMP.SITE_ID=@SITE_ID AND emp.DEPT_NAME=@DEPT_NAME AND emp.STATUS='Present'
and et.DAY=01 and et.MONTH=03 and et.YEAR=16
)
INSERT INTO #temp5(EMP_CODE,EMP_NAME,Row,G4,F4,S4,T4)
select * from(
select emp.EMP_CODE,emp.EMP_NAME,ROW_NUMBER() OVER(PARTITION BY emp.emp_code ORDER BY emp.EFFECTIVE_FROM DESC) AS Row,
'0' AS G4, '0' AS F4, '0' AS S4, '0' AS T4
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_CODE
where EMP.SITE_ID=@SITE_ID AND emp.DEPT_NAME=@DEPT_NAME AND emp.STATUS='Present'

)a where a.Row=1
ELSE
INSERT INTO #temp5 (EMP_CODE,EMP_NAME,Row,G4,F4,S4,T4)
select * from(
select distinct emp.EMP_CODE,emp.EMP_NAME,ROW_NUMBER() OVER(PARTITION BY emp.emp_code ORDER BY emp.EFFECTIVE_FROM DESC) AS Row,
case when et.DAY=@d5 and et.MONTH=@m5 and et.YEAR=@y5 and et.EMP_CODE=emp.EMP_CODE and et.site_id=@SITE_ID then et.GENERAL_SHIFT else 0 end as G4,
case when et.DAY=@d5 and et.MONTH=@m5 and et.YEAR=@y5 and et.EMP_CODE=emp.EMP_CODE and et.site_id=@SITE_ID then et.FIRST_SHIFT else 0 end as F4,
case when et.DAY=@d5 and et.MONTH=@m5 and et.YEAR=@y5 and et.EMP_CODE=emp.EMP_CODE and et.site_id=@SITE_ID then et.SECOND_SHIFT else 0 end as S4,
case when et.DAY=@d5 and et.MONTH=@m5 and et.YEAR=@y5 and et.EMP_CODE=emp.EMP_CODE and et.site_id=@SITE_ID then et.THIRD_SHIFT else 0 end as T4
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_CODE
where EMP.SITE_ID=@SITE_ID AND emp.DEPT_NAME=@DEPT_NAME AND emp.STATUS='Present'
and et.DAY=01 and et.MONTH=03 and et.YEAR=16
)a where a.Row=1

Member 12369428 18-Mar-16 7:11am

Hi above posted my query so please suggest me

CHill60 18-Mar-16 9:29am

I'm working on it when I get moments free

CHill60 18-Mar-16 10:39am

Well your first problem is that you have hard-coded the day, month, year you are looking for instead of using @d5, @m5 and @y5.
Secondly, all those CASE clauses are meaningless as you have already filtered the records on the hard-coded date.
Your original question is a little misleading - the IF-ELSE that I originally suggested is not necessary. The following query will return a single row for ALL employees. If they have a timesheet for the date represented by @d5, @m5 and @y5 then you will get the values from the et table; if they do not have a timesheet for that date then you will get zeroes returned

INSERT INTO #temp5 (EMP_CODE,EMP_NAME,Row,G4,F4,S4,T4)
 select * from(
 select distinct emp.EMP_CODE,emp.EMP_NAME,ROW_NUMBER() OVER(PARTITION BY emp.emp_code ORDER BY emp.EFFECTIVE_FROM DESC) AS Row,
 ISNULL(GENERAL_SHIFT,0) as G4, 
 ISNULL(et.FIRST_SHIFT,0) as F4,
 ISNULL(et.SECOND_SHIFT,0) as S4,
 ISNULL(et.THIRD_SHIFT,0) as T4
 from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
 left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_CODE 
	AND et.DAY=@d5 and et.MONTH=@m5 and et.YEAR=@y5 and et.EMP_CODE=emp.EMP_CODE and et.site_id=@SITE_ID
 where EMP.SITE_ID=@SITE_ID AND emp.DEPT_NAME=@DEPT_NAME AND emp.STATUS='Present'
 --and et.DAY=@d5 and et.MONTH=@m5 and et.YEAR=@y5
 )a where a.Row=1

I can't see why you return Row as it is always 1, nor why you are using it at all - if the employees have multiple timesheets for a single date then surely you should be using SUM or something similar

Member 12369428 22-Mar-16 4:35am

Thank u very much my problem with this query Actually iam using row=1 for every employee code have multiple revisisons but time sheet entered using latest revision details