如果我查询不存在的记录，那么我将不会返回任何内容。我希望在那种情况下返回0。 [英] 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.

问题描述

这是我的简单查询。如果我查询不存在的记录，那么我将不会返回任何内容。我希望在那个场景中返回0。请帮帮我。谢谢。



<前lang =SQL> SELECT *
FROM （
SELECT DISTINCT emp.EMP_CODE
，emp.EMP_NAME
，ROW_NUMBER（） OVER （
PARTITION BY \\ temp.emp_code ORDER BY emp.EFFECTIVE_FROM DESC
） AS 行
， 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
那么 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
那么 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
那么 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
那么 et.THIRD_SHIFT
< span class =code-keyword> ELSE ' 0'
< span class =code-keyword> 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

我是什么尝试过：



如果我查询不存在的记录，那么我将不会返回任何内容。我希望在那个场景中返回0。

解决方案

这是我通常在我的表示层中处理的东西，但是如果你必须这样做，那么就有了看一下使用 EXISTS [ ^ ]

例如:(虽然你应该反转这个IF语句）

< pre lang =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
WHE RE 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 行，' 0' AS G，' 0' AS F，' 0' AS S，' 0' AS T
ELSE
- 此处的原始查询
END

顺便说一句，我真诚地希望您不在varchar字段中存储数值： - ）

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

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.

解决方案

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)

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 :-)

这篇关于如果我查询不存在的记录，那么我将不会返回任何内容。我希望在那种情况下返回0。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！