问题描述
有人可以帮助我对 SQL 查询进行故障排除以发现为什么它不返回任何结果，只返回列别名吗?
Can someone assist me in troubleshooting my SQL query to discover why it's not returning any results, only the column aliases?
我已经把它分开了，所有明显组合在一起的部分分别返回了预期的数据.感谢您提前提供任何指导/帮助.下面是我的脚本:
I've broken it apart, and all sections that obviously group together returns the expected data individually. Thanks for any guidance/assistance in advance. Below is my script:
...
DECLARE @u_cnt INT;
DECLARE @f_yr DATE;
DECLARE @qrt VARCHAR(3);
DECLARE @dnum VARCHAR(5);
SET @u_cnt = 10000;
SET @f_yr = '2002-05-20';
SET @qrt = 'Q2';
SET @dnum = '43234';
SELECT c.GroupLabel AS ORG_Code,
CONVERT (VARCHAR(7), FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy')) AS [MONTH],
COUNT(DISTINCT CASE s.TestType
WHEN 'IR' THEN c.changedate
ELSE NULL END) AS TEST_DAYS,
COUNT(DISTINCT c.changedate) AS ALLDAYS,
COUNT(s.Id) AS total,
(CASE WHEN (@u_cnt IS NULL) THEN -1
ELSE @u_cnt
END) AS board_cnt,
FORMAT((COUNT(s.Id) / CASE
WHEN (@u_cnt IS NULL) THEN -1
ELSE @u_cnt
END), 'P0') AS pct_tested_text,
CASE WHEN 100 * (COUNT(s.Id) / CASE
WHEN (@u_cnt IS NULL) THEN -1
ELSE @u_cnt
END) >= 15
AND (COUNT(DISTINCT CASE s.TestType
WHEN 'IR' THEN c.changedate
ELSE NULL END)) >= 4
THEN 'Yes'
ELSE 'NO' END
FROM cforms c
INNER JOIN spitems sp
ON c.Id = s.FormId
WHERE c.Group = 'HR'
AND c.bFlag IS NULL
AND s.Report IN ('P', 'N')
AND CONVERT(VARCHAR(6), c.changedate, 112) IN
(SELECT
CASE
WHEN f.Quarter = 'Q1' THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + f.FyMonthNumber
WHEN f.Quarter = 'ALL' AND f.FyMonth IN ('OCT', 'NOV', 'DEC') THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1, 112) + f.FyM
ELSE CONVERT(VARCHAR(4), YEAR(@f_yr), 112) + f.FyM
END AS FY_MONTH
FROM fis f
WHERE f.Quarter = @qrt)
AND c.GroupLabel = 'Hr' + @dnum
GROUP BY c.GroupLabel, FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy')
ORDER BY 1, FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy');
推荐答案
所有可能会限制您的数据的内容都在下面这部分代码中.我将其分解并添加了对为什么和哪里限制的评论.我认为你的 CONVERT 是罪魁祸首.
Everything that could be limiting your data is in this part of your code below. I broke it apart and added comments to why and where they are limited. I think your CONVERT is the culprit.
--this inner join will limit the rows to only those with matching Id and FormId
INNER JOIN spitems sp
ON c.Id = s.FormId
--of the rows already filtered via the JOIN, they are further limited to thous with the Group = 'HR', a NULL bFlag, and Report = to P or N
WHERE c.Group = 'HR'
AND c.bFlag IS NULL
AND s.Report IN ('P', 'N')
--The first convert here changed changedate to yyyymmd (notice the day).
--In the sub-query, you seem to only be returning yyyymm formatted with a -,
--thus this would return ZERO rows. varchar(6) could resolve this,
--by dropping the day, but you'd need to add the hyphen or remove it from the `IN` clause
AND CONVERT(VARCHAR(7), c.changedate, 112) IN
(SELECT
CASE
WHEN f.Quarter = 'Q1' THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + '-' + f.FyMonthNumber
WHEN f.Quarter = 'ALL' AND f.FyMonth IN ('OCT', 'NOV', 'DEC') THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1, 112) + '-' + f.FyMonthNumber
ELSE CONVERT(VARCHAR(4), YEAR(@f_yr), 112) + '-' + f.FyMonthNumber
END AS FY_MONTH
FROM FyQm f
WHERE f.Quarter = @qrt)
--Lastly, there may be a case sensitivity here Hr vs HR or there just simply aren't any rows that match this predicate
AND c.GroupLabel = 'Hr' + @dnum
编辑
详细说明我上面的回答……您已经更改了 where 子句的一部分.特别是您将 c.changedate 评估为值列表的部分.您已更改为:
Elaborating on my answer above... you have changed a portion of your where clause. Specifically the portion where you are evaluating c.changedate to a list of values. You have made the change to :
AND CONVERT(VARCHAR(6), c.changedate, 112) IN ...
这是部分修复.它会删除您之前拥有的尾随 DAY 值，为您留下 YYYYMM.但是，在您的子查询中，您将值列表格式化为 YYYYMM-?，其中 ?f.FyMonthNumber 是什么.如您所见，这永远不会匹配您原来的 convert 语句，因为它没有 连字符.首先要更改的是从字符串连接中删除连字符.在您编辑的帖子中，您已经做得很好.接下来，问题可能是当您尝试将 + 与 f.FyMonthNumber 组合时，它没有被视为加法而不是串联.如果 f.FyMonthNumber 是 int 那么它会添加它.
This is a partial fix. It would remove the trailing DAY value you had before, leaving you with YYYYMM. However, in your subquery, you are formatting the list of values as YYYYMM-? where the ? is whatever f.FyMonthNumber is. As you can see, this will never match your original convert statement since it doesn't have a hyphen. The first thing to change would be remove the hyphen from the string concatenation. In your edited post, you have already done that so good job. Next, the issue could be that your + is not being treated as addition instead of concatenation when you are trying to combine it with f.FyMonthNumber. If f.FyMonthNumber is an int then it will add it.
DECLARE @f_yr DATE;
SET @f_yr = '2002-05-20';
SELECT CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + 02
在这里您希望它返回 200102 但它返回 2003，因为它正在执行加法.您可以将其转换为 varchar 或 char 来解决此问题.
Here you are wanting it to return 200102 but it returns 2003 since it's performing addition. You can cast it as a varchar or char to fix this.
SELECT CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + cast('02' as varchar)
最后，您可能遇到的一个问题是，如果 f.FyMonthNumber 存储为 int，它不会有前导零.因此，对于 1 月，它将表示为 1 而不是 01 并且这也将在 10 月之前的任何月份返回零行.您可以使用 right 函数处理此问题.
Lastly, an issue you may run into is if f.FyMonthNumber is stored as an int, it won't have the leading zero. Thus, for January it would be represented as 1 instead of 01 and this would also return zero rows for any month before October. You can handle this with the right function.
DECLARE @f_yr DATE;
SET @f_yr = '2002-05-20';
SELECT CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + right('0' + cast('1' as varchar(2)),2) --a month as a single digit
SELECT CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + right('0' + cast('12' as varchar(2)),2) --a month with double digits
将所有这些放在一起，我怀疑此编辑会解决您的问题.不过，我要注意的是，如果适用，您不会评估 Q2、Q3 或 Q4 的任何 case 表达式...
Putting that all together, I would suspect this edit would fix your issue. I would note though, you aren't evaluating any case expressions for Q2, Q3, or Q4 if that would be applicable...
DECLARE @u_cnt INT;
DECLARE @f_yr DATE;
DECLARE @qrt VARCHAR(3);
DECLARE @dnum VARCHAR(5);
SET @u_cnt = 10000;
SET @f_yr = '2002-05-20';
SET @qrt = 'Q2';
SET @dnum = '43234';
SELECT c.GroupLabel AS ORG_Code,
CONVERT (VARCHAR(7), FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy')) AS [MONTH],
COUNT(DISTINCT CASE s.TestType
WHEN 'IR' THEN c.changedate
ELSE NULL END) AS TEST_DAYS,
COUNT(DISTINCT c.changedate) AS ALLDAYS,
COUNT(s.Id) AS total,
(CASE WHEN (@u_cnt IS NULL) THEN -1
ELSE @u_cnt
END) AS board_cnt,
FORMAT((COUNT(s.Id) / CASE
WHEN (@u_cnt IS NULL) THEN -1
ELSE @u_cnt
END), 'P0') AS pct_tested_text,
CASE WHEN 100 * (COUNT(s.Id) / CASE
WHEN (@u_cnt IS NULL) THEN -1
ELSE @u_cnt
END) >= 15
AND (COUNT(DISTINCT CASE s.TestType
WHEN 'IR' THEN c.changedate
ELSE NULL END)) >= 4
THEN 'Yes'
ELSE 'NO' END
FROM cforms c
INNER JOIN spitems sp
ON c.Id = s.FormId
WHERE c.Group = 'HR'
AND c.bFlag IS NULL
AND s.Report IN ('P', 'N')
AND CONVERT(VARCHAR(6), c.changedate, 112) IN
(SELECT
CASE
WHEN f.Quarter = 'Q1' THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + right('0' + cast(f.FyMonthNumber as varchar(2)))
WHEN f.Quarter = 'ALL' AND f.FyMonth IN ('OCT', 'NOV', 'DEC') THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1, 112) + right('0' + cast(f.FyMonthNumber as varchar(2)))
ELSE CONVERT(VARCHAR(4), YEAR(@f_yr), 112) + right('0' + cast(f.FyMonthNumber as varchar(2)))
END AS FY_MONTH
FROM fis f
WHERE f.Quarter = @qrt)
AND c.GroupLabel = 'Hr' + @dnum
GROUP BY c.GroupLabel, FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy')
ORDER BY 1, FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy');
这篇关于我可以在 SQL 查询中使用什么来帮助我确定为什么我的查询没有返回任何数据结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！