Oracle中DECODE与CASE查询结果不一致问题求助

Hey there, let's break down why your CASE-based query is returning different results from the DECODE version—this almost always boils down to how SQL handles NULL comparisons between the two functions. Here's a step-by-step breakdown of the issue and fixes:

1. The Core Issue: NULL Handling Differences Between DECODE and CASE #

The biggest gotcha here is how each function treats NULL values:

• DECODE: Directly matches NULL values. For example, DECODE(expr, NULL, val1, val2) will return val1 if expr is NULL, since DECODE uses equality checks that explicitly recognize NULL as a match.
• CASE: Uses standard SQL comparison logic, where expr WHEN NULL never evaluates to TRUE. In SQL, NULL = NULL isn't TRUE—it returns UNKNOWN, so the WHEN NULL branch in your code never triggers, even when the MAX() result is NULL. That's exactly why your permission grouping results are off.

Looking at your original CASE code snippet:

CASE MAX ( CASE "ON" WHEN 'N' THEN permission END) 
  WHEN NULL THEN MAX ( CASE "ON" WHEN 'O' THEN permission END) 
  ELSE MAX ( CASE "ON" WHEN 'N' THEN permission END) 
END

This WHEN NULL check is effectively dead code—no matter what the MAX() returns, it will always jump to the ELSE branch.

Fix for the CASE Statement #

Replace the simple CASE with a searched CASE that uses IS NULL instead of WHEN NULL:

CASE 
  WHEN MAX ( CASE "ON" WHEN 'N' THEN permission END) IS NULL 
  THEN MAX ( CASE "ON" WHEN 'O' THEN permission END) 
  ELSE MAX ( CASE "ON" WHEN 'N' THEN permission END) 
END

Apply this same fix to the second CASE expression in your query (the one for dont_overwrite_existing).

2. Additional Troubleshooting Steps #

If fixing the NULL check doesn't resolve everything, verify these points to rule out other inconsistencies:

• Subquery Results: Run the union subquery (SELECT o.*, 'O' "ON" FROM old o UNION SELECT n.*, 'N' "ON" FROM new n) alone to confirm both queries are working with identical raw data.
• MAX() Function Behavior: Since permission is a string, MAX() uses alphabetical sorting. Confirm that your expected permission values (like RWDA vs. RWD) are ordered correctly for your use case.
• Group Consistency: Double-check that GROUP BY principal is grouping the same rows in both queries—look for hidden whitespace, case differences, or special characters in principal values that might throw off grouping.
• Implicit Data Type Conversion: Ensure there's no unexpected type coercion happening when concatenating principal || ...—DECODE and CASE can sometimes handle type conversions differently in edge cases.

Your Original Queries for Reference #

DECODE Full Version #

WITH old AS (SELECT REGEXP_SUBSTR (acl, '[^\\(]+') principal, REGEXP_SUBSTR (acl, '\\(.+') permission FROM ( SELECT REGEXP_SUBSTR ( ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)', '[^,]+', 1, LEVEL) acl FROM DUAL WHERE ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)' IS NOT NULL CONNECT BY REGEXP_SUBSTR ( ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)', '[^,]+', 1, LEVEL) IS NOT NULL)), 
new AS (SELECT REGEXP_SUBSTR (acl, '[^\\(]+') principal, REGEXP_SUBSTR (acl, '\\(.+') permission FROM ( SELECT REGEXP_SUBSTR (':Group1(R),:Group1(RW),:GroupA(RWDA),:Group5(R)', '[^,]+', 1, LEVEL) acl FROM DUAL WHERE ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4(R)' IS NOT NULL CONNECT BY REGEXP_SUBSTR ( ':Group1(R),:Group1(RW),:GroupA(RWDA),:Group5(R)', '[^,]+', 1, LEVEL) IS NOT NULL)), 
principalsToDelete AS (SELECT DISTINCT REGEXP_SUBSTR (acl, '[^\\(]+') principal FROM ( SELECT REGEXP_SUBSTR (NULL, '[^,]+', 1, LEVEL) acl FROM DUAL WHERE NULL IS NOT NULL CONNECT BY REGEXP_SUBSTR (NULL, '[^,]+', 1, LEVEL) IS NOT NULL)) 
SELECT principal || DECODE (MAX (DECODE ("ON", 'N', permission)), NULL, MAX (DECODE ("ON", 'O', permission)), MAX (DECODE ("ON", 'N', permission))) latest_permission, 
       principal || DECODE (MAX (DECODE ("ON", 'O', permission)), NULL, MAX (DECODE ("ON", 'N', permission)), MAX (DECODE ("ON", 'O', permission))) dont_overwrite_existing 
FROM (SELECT o.*, 'O' "ON" FROM old o UNION SELECT n.*, 'N' "ON" FROM new n) 
WHERE principal NOT IN (SELECT principal FROM principalsToDelete) 
GROUP BY principal 
ORDER BY principal;

Corrected CASE Full Version #

WITH old AS (SELECT REGEXP_SUBSTR (acl, '[^\\(]+') principal, REGEXP_SUBSTR (acl, '\\(.+') permission FROM ( SELECT REGEXP_SUBSTR ( ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)', '[^,]+', 1, LEVEL) acl FROM DUAL WHERE ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)' IS NOT NULL CONNECT BY REGEXP_SUBSTR ( ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4((R)', '[^,]+', 1, LEVEL) IS NOT NULL)), 
new AS (SELECT REGEXP_SUBSTR (acl, '[^\\(]+') principal, REGEXP_SUBSTR (acl, '\\(.+') permission FROM ( SELECT REGEXP_SUBSTR (':Group1(R),:Group1(RW),:GroupA(RWDA),:Group5(R)', '[^,]+', 1, LEVEL) acl FROM DUAL WHERE ':Group1(RWDA),:Group2(RWD),:Group3(RW),:Group4(R)' IS NOT NULL CONNECT BY REGEXP_SUBSTR ( ':Group1(R),:Group1(RW),:GroupA(RWDA),:Group5(R)', '[^,]+', 1, LEVEL) IS NOT NULL)), 
principalsToDelete AS (SELECT DISTINCT REGEXP_SUBSTR (acl, '[^\\(]+') principal FROM ( SELECT REGEXP_SUBSTR (NULL, '[^,]+', 1, LEVEL) acl FROM DUAL WHERE NULL IS NOT NULL CONNECT BY REGEXP_SUBSTR (NULL, '[^,]+', 1, LEVEL) IS NOT NULL)) 
SELECT principal || CASE 
                      WHEN MAX ( CASE "ON" WHEN 'N' THEN permission END) IS NULL 
                      THEN MAX ( CASE "ON" WHEN 'O' THEN permission END) 
                      ELSE MAX ( CASE "ON" WHEN 'N' THEN permission END) 
                    END latest_permission, 
       principal || CASE 
                      WHEN MAX ( CASE "ON" WHEN 'O' THEN permission END) IS NULL 
                      THEN MAX ( CASE "ON" WHEN 'N' THEN permission END) 
                      ELSE MAX ( CASE "ON" WHEN 'O' THEN permission END) 
                    END dont_overwrite_existing 
FROM (SELECT o.*, 'O' "ON" FROM old o UNION SELECT n.*, 'N' "ON" FROM new n) 
WHERE principal NOT IN (SELECT principal FROM principalsToDelete) 
GROUP BY principal 
ORDER BY principal;

内容的提问来源于stack exchange，提问作者Spanky