SQL 选择子句上的 CASE 子句抛出“SQLCODE=-811，SQLSTATE=21000”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26072235/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CASE Clause on select clause throwing 'SQLCODE=-811, SQLSTATE=21000' Error
提问by user2661315
This query is very well working in Oracle. But it is not working in DB2. It is throwing
此查询在 Oracle 中非常有效。但它在 DB2 中不起作用。它在扔
DB2 SQL Error: SQLCODE=-811, SQLSTATE=21000, SQLERRMC=null, DRIVER=3.61.65
DB2 SQL 错误：SQLCODE=-811，SQLSTATE=21000，SQLERRMC=null，DRIVER=3.61.65
error when the sub query under THENclause is returning 2 rows.
当THEN子句下的子查询返回 2 行时出错。
However, my question is why would it execute in the first place as my WHEN clause turns to be false always.
但是，我的问题是为什么它会首先执行，因为我的 WHEN 子句总是错误的。
SELECT
CASE
WHEN (SELECT COUNT(1)
FROM STOP ST,
FACILITY FAC
WHERE ST.FACILITY_ID = FAC.FACILITY_ID
AND FAC.IS_DOCK_SCHED_FAC=1
AND ST.SHIPMENT_ID = 2779) = 1
THEN
(SELECT ST.FACILITY_ALIAS_ID
FROM STOP ST,
FACILITY FAC
WHERE ST.FACILITY_ID = FAC.FACILITY_ID
AND FAC.IS_DOCK_SCHED_FAC=1
AND ST.SHIPMENT_ID = 2779
)
ELSE NULL
END STAPPFAC
FROM SHIPMENT SHIPMENT
WHERE SHIPMENT.SHIPMENT_ID IN (2779);
回答by Joachim Isaksson
The SQL standard does not require short cut evaluation (ie evaluation order of the parts of the CASE statement). Oracle chooses to specify shortcut evaluation, however DB2 seems to not do that.
SQL 标准不要求快捷评估（即 CASE 语句各部分的评估顺序）。Oracle 选择指定快捷方式评估，但是 DB2 似乎没有这样做。
Rewriting your query a little for DB2 (8.1+ only for FETCHin subqueries) should allow it to run (unsure if you need the added ORDER BYand don't have DB2 to test on at the moment)
为 DB2 稍微重写您的查询（8.1+ 仅用于FETCH子查询）应该允许它运行（不确定您是否需要添加ORDER BY并且目前没有 DB2 进行测试）
SELECT
CASE
WHEN (SELECT COUNT(1)
FROM STOP ST,
FACILITY FAC
WHERE ST.FACILITY_ID = FAC.FACILITY_ID
AND FAC.IS_DOCK_SCHED_FAC=1
AND ST.SHIPMENT_ID = 2779) = 1
THEN
(SELECT ST.FACILITY_ALIAS_ID
FROM STOP ST,
FACILITY FAC
WHERE ST.FACILITY_ID = FAC.FACILITY_ID
AND FAC.IS_DOCK_SCHED_FAC=1
AND ST.SHIPMENT_ID = 2779
ORDER BY ST.SHIPMENT_ID
FETCH FIRST 1 ROWS ONLY
)
ELSE NULL
END STAPPFAC
FROM SHIPMENT SHIPMENT
WHERE SHIPMENT.SHIPMENT_ID IN (2779);
回答by Clockwork-Muse
Hmm... you're running the same query twice. I get the feeling you're not thinking in sets(how SQL operates), but in a more procedural form (ie, how most common programming languages work). You probably want to rewrite this to take advantage of how RDBMSs are supposed to work:
嗯...你运行相同的查询两次。我感觉您不是在思考集合（SQL 的操作方式），而是以更程序化的形式（即，最常见的编程语言如何工作）。您可能想要重写它以利用 RDBMS 应该如何工作：
SELECT Current_Stop.facility_alias_id
FROM SYSIBM/SYSDUMMY1
LEFT JOIN (SELECT MAX(Stop.facility_alias_id) AS facility_alias_id
FROM Stop
JOIN Facility
ON Facility.facility_id = Stop.facility_id
AND Facility.is_dock_sched_fac = 1
WHERE Stop.shipment_id = 2779
HAVING COUNT(*) = 1) Current_Stop
ON 1 = 1
(no sample data, so not tested. There's a couple of other ways to write this based on other needs)
This should work on all RDBMSs.
（没有样本数据，所以没有测试。根据其他需要，还有其他几种方法可以编写它）
这应该适用于所有 RDBMS。
那么这里发生了什么，为什么会这样呢？（为什么我删除了对 的引用
ShipmentShipment？）First, let's look at your query again:
首先，让我们再次查看您的查询：
CASE WHEN (SELECT COUNT(1)
FROM STOP ST, FACILITY FAC
WHERE ST.FACILITY_ID = FAC.FACILITY_ID
AND FAC.IS_DOCK_SCHED_FAC = 1
AND ST.SHIPMENT_ID = 2779) = 1
THEN (SELECT ST.FACILITY_ALIAS_ID
FROM STOP ST, FACILITY FAC
WHERE ST.FACILITY_ID = FAC.FACILITY_ID
AND FAC.IS_DOCK_SCHED_FAC = 1
AND ST.SHIPMENT_ID = 2779)
ELSE NULL END
(First off, stop using the implicit-join syntax - that is, comma-separated FROMclauses - always explicitly qualify your joins. For one thing, it's way too easy to miss a condition you should be joining on)
...from this it's obvious that your statement is the 'same' in both queries, and shows what you're attempting - if the dataset has one row, return it, otherwise the result should be null.
（首先，停止使用隐式连接语法 - 即逗号分隔的FROM子句 - 始终明确限定您的连接。一方面，错过您应该加入的条件太容易了）
...从这里开始很明显，您的语句在两个查询中都是“相同的”，并显示您正在尝试的内容 - 如果数据集有一行，则返回它，否则结果应该为空。
Enter the HAVINGclause:
输入HAVING子句：
HAVING COUNT(*) = 1
This is essentially a WHEREclause for aggregates (functions like MAX(...), or here, COUNT(...)). This is useful when you want to make sure some aspect of the entireset matches a given criteria. Here, we want to make sure there's just one row, so using COUNT(*) = 1as the condition is appropriate; if there's more (or less! could be 0 rows!) the set will be discarded/ignored.
这本质上是一个WHERE聚合子句（函数，如MAX(...), 或 here, COUNT(...)）。当您想确保整个集合的某些方面符合给定条件时，这很有用。在这里，我们要确保只有一行，因此使用COUNT(*) = 1as 条件是合适的；如果有更多（或更少！可能是 0 行！）该集合将被丢弃/忽略。
Of course, using HAVINGmeans we're using an aggregate, the usual rules apply: all columns must either be in a GROUP BY(which is actually an option in this case), or an aggregate function. Because we only want/expect one row, we can cheat a little, and just specify a simple MAX(...)to satisfy the parser.
当然， usingHAVING意味着我们正在使用聚合，通常的规则适用：所有列必须在 a GROUP BY（在这种情况下实际上是一个选项）或聚合函数中。因为我们只想要/期望一行，所以我们可以作弊，只需要指定一个简单的MAX(...)来满足解析器。
At this point, the new subquery returns one row (containing one column) if there was only one row in the initial data, and no rowsotherwise (this part is important). However, we actually need to return a row regardless.
此时，如果初始数据中只有一行，则新子查询返回一行（包含一列），否则返回没有行（这部分很重要）。然而，不管怎样，我们实际上都需要返回一行。
FROM SYSIBM/SYSDUMMY1
This is a handy dummy table on all DB2 installations. It has one row, with a single column containing '1'(character '1', not numeric 1). We're actually interested in the fact that it has only one row...
这是一个适用于所有 DB2 安装的方便的虚拟表。它有一行，一列包含'1'（字符“1”，而不是数字 1）。我们实际上对它只有一行这一事实感兴趣......
LEFT JOIN (SELECT ... )
ON 1 = 1
A LEFT JOINtakes every row in the preceding set (all joined rows from the preceding tables), and multiplies it by every row in the next table reference, multiplying by 1 in the case that the set on the right (the new reference, our subquery) has no rows. (This is different from how a regular (INNER) JOINworks, which multiplies by 0 in the case that there is no row) Of course, we only maybehave 1 row, so there's only going to be a maximum of one result row. We're required to have an ON ...clause, but there's no data to actually correlate between the references, so a simple always-true condition is used.
ALEFT JOIN取前一个集合中的每一行（来自前一个表的所有连接行），并将其乘以下一个表引用中的每一行，在右侧的集合（新引用，我们的子查询）的情况下乘以 1没有行。（这与常规的(INNER) JOIN工作方式不同，在没有行的情况下乘以 0）当然，我们可能只有 1 行，因此最多只有一个结果行。我们需要有一个ON ...子句，但在引用之间没有实际关联的数据，因此使用了一个简单的始终为真的条件。
To get our data, we just need to get the relevant column:
要获取我们的数据，我们只需要获取相关列：
SELECT Current_Stop.facility_alias_id
... if there's the one row of data, it's returned. In the case that there is some other count of rows, the HAVINGclause throws out the set, and the LEFT JOINcauses the column to be filled in with a null(no data) value.
...如果有一行数据，则返回。在有一些其他行数的情况下，该HAVING子句抛出该集合，并LEFT JOIN导致用null（无数据）值填充该列。
So why did I remove the reference to Shipment? First off, you weren't using any data from the table - the only column in the result set was from the subquery. I also have good reason to believe that there would only be one row returned in this case - you're specifying a single shipment_idvalue (which implies you know it exists). If we don't need anything from the table (including the number of rows in that table), it's usually best to remove it from the statement: doing so can simplify the work the db needs to do.
那么为什么我删除了对 的引用Shipment？首先，您没有使用表中的任何数据 - 结果集中唯一的列来自子查询。我也有充分的理由相信在这种情况下只会返回一行 - 您指定的是一个shipment_id值（这意味着您知道它存在）。如果我们不需要表中的任何内容（包括该表中的行数），通常最好将其从语句中删除：这样做可以简化数据库需要做的工作。
