CODEWITHSUNDEEP
sqloracle-databasecasedecode
Spanky
Spanky

Reputation: 121

Oracle DECODE vs. CASE. Queries return different result sets

I have the following SQL Statement in two versions. One uses DECODE the other CASE. Everything else is the same. DECODE works as expected while CASE delivers different output (basically groups with corresponding access rights). What is the reason?

Both can be run in a client, no dependencies on the DB schema. Only the subqueries using DECODE or CASE are different all other parts are equal.

Any advice is welcome!

Output DECODE: enter image description here

Output CASE: enter image description here

DECODE (Short)

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

CASE Short

...
SELECT    principal
     || 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    latest_permission,
        principal
     || CASE MAX (
                 CASE "ON" WHEN 'O' THEN permission END)
            WHEN NULL
            THEN
                MAX (
                    CASE "ON"
                        WHEN 'N' THEN permission
                    END)
            ELSE
                MAX (
                    CASE "ON"
                        WHEN 'O' THEN permission
                    END)
        END    dont_overwrite_existing

FROM 
...

DECODE Full

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;

CASE Full

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 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    latest_permission,
            principal
         || CASE MAX (
                     CASE "ON" WHEN 'O' THEN permission END)
                WHEN 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;

Upvotes: 1

Views: 951

Answers (1)

gsalem
gsalem

Reputation: 2028

The difference comes from the difference in behavior when comparing to null:

SQL> select decode(null,null,1) from dual;

DECODE(NULL,NULL,1)
-------------------
                  1

SQL> select case null when null then 1 end from dual;

CASENULLWHENNULLTHEN1END
------------------------

The second query return NULL and not 1, so 'case x when null then 1 end' does NOT return 1 when x is NULL, while this works:

SQL> select case when null is null then 1 end from dual;

CASEWHENNULLISNULLTHEN1END
--------------------------
                         1

Upvotes: 1

Related Questions