CASE Clause on select clause throwing 'SQLCODE=-811, SQLSTATE=21000' Error

Question

This query is very well working in Oracle. But it is not working in DB2. It is throwing

DB2 SQL Error: SQLCODE=-811, SQLSTATE=21000, SQLERRMC=null, DRIVER=3.61.65

error when the sub query under THEN clause is returning 2 rows.

However, my question is why would it execute in the first place as my WHEN clause turns to be false always.

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

Answer 1

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.

Rewriting your query a little for DB2 (8.1+ only for FETCH in subqueries) should allow it to run (unsure if you need the added ORDER BY and don't have DB2 to test on at the moment)

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

Answer 2

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:

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.

---

Shipment

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 FROM clauses - 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.

Enter the HAVING clause:

HAVING COUNT(*) = 1

This is essentially a WHERE clause for aggregates (functions like MAX(...), or here, COUNT(...)). This is useful when you want to make sure some aspect of the entire set matches a given criteria. Here, we want to make sure there's just one row, so using COUNT(*) = 1 as the condition is appropriate; if there's more (or less! could be 0 rows!) the set will be discarded/ignored.

Of course, using HAVING means 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.

At this point, the new subquery returns one row (containing one column) if there was only one row in the initial data, and no rows otherwise (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...

LEFT JOIN (SELECT ... )
       ON 1 = 1

A LEFT JOIN takes 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) JOIN works, which multiplies by 0 in the case that there is no row) Of course, we only maybe have 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.

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 HAVING clause throws out the set, and the LEFT JOIN causes the column to be filled in with a null (no data) value.

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_id value (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.