时间戳

首页 > 解决方案 > 需要帮助来查找查询中的问题并对其进行优化

sql - 需要帮助来查找查询中的问题并对其进行优化

问题描述

使用下面的查询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 working

更新_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

很尴尬，但不明白我在这里做什么愚蠢的事情。

标签： sqldatabasedb2

解决方案

您缺少CASE第二个 case-when 语句的部分。此外，您正在选择 col1 和 col2，然后使用您的 case-when 语句使用别名再次选择它们，这可以按问题解释组。试试这个版本：

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 ;

推荐阅读