需要帮助来查找查询中的问题并对其进行优化
需要帮助来查找查询中的问题并对其进行优化
提问于 2018-10-12 01:16:53
在DB2中使用以下查询来获取记录。如果找不到任何记录，则尝试在该列中使用默认值。但事情并没有像预期的那样工作。查询肯定存在一些问题，也需要对其进行优化。期待一些帮助/建议/正面批评。
查询：
SELECT DISTINCT(t1.col1),t1.col2,t1.col3,t1.col4,t1.col5
CASE
WHEN t1.col1 = '' THEN ‘AA99’ ELSE t1.col1 END AS col1
WHEN t1.col2 = '' THEN ‘AA99’ ELSE t1.col2 END AS col2
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON (t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.col3 = t2.col3
INNER JOIN TABLE3 t3
ON (t1.col2 = t3.col2)
INNER JOIN TABLE4 t4
ON (t1.col1 = t4.col1 AND t1.col3 = t4.col3)
WHERE (((t1.col1 <> 'NA' OR t1.col1 IS NOT NULL) AND (t2.col1 <> 'NA' OR t2.col1 IS NOT NULL))
AND ((t1.col2 <> 'NA' OR t1.col2 IS NOT NULL) AND (t2.col2 <> 'NA' OR t2.col2 IS NOT NULL))
GROUP BY t1.col1,t1.col2 ;错误：
1) [Code: -104, SQL State: 42601] An unexpected token "WHEN" was found following ".col1
CASE
". Expected tokens may include: "INTO".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.22.29
2) [Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens "WHEN|.col1
CASE
|INTO".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.22.29
3) Group By is also not workingUpdate_1：
按照建议，尝试执行以下操作：
SELECT DISTINCT (t1.col1),t1.col2,t1.col3,t1.col4,t1.col5,
CASE
WHEN t1.col1 = '' THEN ‘AA99’
ELSE t1.col1
END AS newcol1,
CASE
WHEN t1.col2 = '' THEN ‘AA99’
ELSE t1.col2
END AS newcol2
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON (t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.col3 = t2.col3
INNER JOIN TABLE3 t3
ON (t1.col2 = t3.col2)
INNER JOIN TABLE4 t4
ON (t1.col1 = t4.col1 AND t1.col3 = t4.col3)
WHERE (((t1.col1 <> 'NA' OR t1.col1 IS NOT NULL) AND (t2.col1 <> 'NA' OR t2.col1 IS NOT NULL))
AND ((t1.col2 <> 'NA' OR t1.col2 IS NOT NULL) AND (t2.col2 <> 'NA' OR t2.col2 IS NOT NULL))
GROUP BY newcol1,newcol2; 错误：
1) [Code: -206, SQL State: 42703] "newcol1" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.22.29
2) [Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "newcol1".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.22.29 这很尴尬，但我不明白我在做什么傻事。
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-10-12 01:43:23
您缺少了第二个case-when语句中的CASE部分。此外，您还选择了col1和col2，然后使用别名使用case-when语句再次选择它们，这可以解释group by问题。试试这个版本：
SELECT DISTINCT
t1.col1,
t1.col2,
t1.col3,
t1.col4,
t1.col5,
CASE
WHEN t1.col1 = '' THEN ‘AA99’
ELSE t1.col1
END AS column1,
CASE
WHEN t1.col2 = '' THEN ‘AA99’
ELSE t1.col2
END AS column2
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON (t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.col3 = t2.col3
INNER JOIN TABLE3 t3
ON (t1.col2 = t3.col2)
INNER JOIN TABLE4 t4
ON (t1.col1 = t4.col1 AND t1.col3 = t4.col3)
WHERE (((t1.col1 <> 'NA' OR t1.col1 IS NOT NULL) AND (t2.col1 <> 'NA' OR t2.col1 IS NOT NULL))
AND ((t1.col2 <> 'NA' OR t1.col2 IS NOT NULL) AND (t2.col2 <> 'NA' OR t2.col2 IS NOT NULL))
GROUP BY column1,column2 ;Stack Overflow用户
发布于 2018-10-12 01:58:57
如果您使用IBM Data Studio，它将突出显示语法错误的起始点。这使得编写语法正确的SQL变得容易得多，例如
SELECT DISTINCT
t1.col1,t1.col2,t1.col3,t1.col4,t1.col5
, CASE WHEN t1.col1 = '' THEN 'AA99' ELSE t1.col1 END AS col1
, CASE WHEN t1.col2 = '' THEN 'AA99' ELSE t1.col2 END AS col2
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON (t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.col3 = t2.col3)
INNER JOIN TABLE3 t3
ON (t1.col2 = t3.col2)
INNER JOIN TABLE4 t4
ON (t1.col1 = t4.col1 AND t1.col3 = t4.col3)
WHERE ((t1.col1 <> 'NA' OR t1.col1 IS NOT NULL) AND (t2.col1 <> 'NA' OR t2.col1 IS NOT NULL))
AND ((t1.col2 <> 'NA' OR t1.col2 IS NOT NULL) AND (t2.col2 <> 'NA' OR t2.col2 IS NOT NULL))
GROUP BY t1.col1,t1.col2 ;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52765693
复制相关文章
相似问题