Nested decode in where clause

Question

I have a cursor that fetches address for the id passed, based on the latest activity date.

CURSOR get_address_upd_c (
id t1.id%TYPE
) IS

  SELECT   street_line1,
           street_line2,
           city,
           stat_code,
           zip,
           activity_date,
           atyp_code
    FROM   addr
   WHERE       addr_im = '1'
           AND status_ind IS NULL
           AND from_date < SYSDATE
           AND DECODE (TO_DATE, NULL, SYSDATE, TO_DATE) >= SYSDATE
ORDER BY   activity_date DESC;

There are certain ids's that have more than 1 address for the date based n a atyp code. My query should fetch address for a particular atyp code('AB') , if that id does not have address of this AB type then i should fetch address for another atype code like'SP'.

I tried to filter my above cursor result as decode statements , but fail when my id has more than 1 atyp code.

Tried the below in IN clause

SELECT   DECODE (
             DECODE (
                 (SELECT   atyp_code
                    FROM   addr a2
                   WHERE   a2.id = '1' AND a2.status_ind IS NULL
                           AND (TRUNC (SYSDATE) BETWEEN TRUNC(NVL (
                                                                  a2.from_date,
                                                                  NVL (
                                                                      a2.TO_DATE,
                                                                      SYSDATE)))
                                                    AND  TRUNC(NVL (
                                                                   a2.TO_DATE,
                                                                   SYSDATE)))
                           AND a2.atyp_code = 'AB'),
                 NULL,
                 (SELECT   atyp_code
                    FROM   addr a2
                   WHERE   a2.id = '1' AND a2.status_ind IS NULL
                           AND (TRUNC (SYSDATE) BETWEEN TRUNC(NVL (
                                                                  a2.addr_from_date,
                                                                  NVL (
                                                                      a2.TO_DATE,
                                                                      SYSDATE)))
                                                    AND  TRUNC(NVL (
                                                                   a2.TO_DATE,
                                                                   SYSDATE)))
                           AND a2.atyp_code = 'SP'),
                 'AB'),
             NULL,
             (SELECT   atyp_code
                FROM   addr a2
               WHERE   a2.id = '1' AND a2.status_ind IS NULL
                       AND (TRUNC (SYSDATE) BETWEEN TRUNC(NVL (
                                                              a2.from_date,
                                                              NVL (
                                                                  a2.TO_DATE,
                                                                  SYSDATE)))
                                                AND  TRUNC(NVL (a2.TO_DATE,
                                                                SYSDATE)))),
             'ar')
             AS t
  FROM   DUAL;

My query always goes to the 'ar' part even though my id has records in 'SP' type

Sample data:

ID  Street_1    City    State   Type_Code   Activity_Date
1   aaa         sds      MI      SM         23-Dec-19
1   bb          wew      TN      IN         23-Dec-19
1   ccc         fcvc     AR      SP         23-Dec-19
1   dd          ewe      NY      SL         23-Dec-19
2   eee         fff      TX      AB          5-Jan-20
3   gg          kkk      TX      SM         19-Sep-18

O/p should be like the below based on the type code values.

ID  Street_1    City    State   Type_Code   Activity_Date
1   ccc         fcvc     AR      SP         23-Dec-19
2   eee         fff      TX      AB          5-Jan-20
3   gg          kkk      TX      SM         19-Sep-18

Create and insert scripts for the above sample

CREATE TABLE addr (
    id              NUMBER(8, 0),
    street_1        VARCHAR2(100),
    city            VARCHAR2(100),
    state           VARCHAR2(5),
    type_code       VARCHAR2(10),
    activity_date   DATE,
    status_ind      VARCHAR2(5),
    addr_from_date  DATE,
    addr_to_date    DATE
);

INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(1,'aaa','sds','MI','SM','23-DEC-19',NULL,'01-SEP-15',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(1,'bb','wew','TN','IN','23-DEC-19',NULL,'01-JUN-18',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(1,'ccc','fcvc','AR','SP','23-DEC-19',NULL,'01-SEP-15',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(1,'dd','ewe','NY','SL','23-DEC-19',NULL,'01-SEP-18',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(2,'ee','fff','TX','AB','05-JAN-20',NULL,'01-MAY-17',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(3,'gg','kkk','TX','SM','19-SEP-18',NULL,'23-JUL-15',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(4,'aaa','sds','MI','PA','03-NOV-19',NULL,'01-MAR-15',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(4,'lll','mno','LA','PB','03-NOV-19',NULL,'01-SEP-15',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(4,'jjj','pqr','LA','SP','03-NOV-19',NULL,'01-SEP-15',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(5,'mmm','dee','NY','SM','03-MAR-19',NULL,'10-SEP-15',NULL);
 INSERT INTO addr (id,street_1,city,state,type_code,activity_date,status_ind,addr_from_date,addr_to_date) values(5,'ppp','aru','TX','SP','03-DEC-17',NULL,'01-SEP-15',NULL);

Answer 1

What you are looking for can be accomplished much easier without decode at all. I hate decode, it was good for its time but that timed ended with 9i for me it's just too unclear/complicated. Use case instead. Further the task reduces to the following. For a given id return id return the active 'AB' address type. If the type AB doesn't exist the return the active 'SP' address type. If neither exist return the lowest type_code or if null then 'ar'. This can be done with a simple case statement and playing around with order by.

select nvl(type_code,'ar') type_cocde
  from (select type_code
          from addr  
         where id = &ID
           and status_ind is null 
           and trunc (sysdate) between trunc(coalesce(addr_from_date, sysdate))
                                   and trunc(coalesce(addr_to_date, sysdate))

         order by case when type_code = 'AB' then 1
                       when type_code = 'SP' then 2
                       else 3
                  end 
             , type_code
        ) tc
  where rownum<=1;

Note: If you have Oracle 12c or higher, you can use LIMIT instead of rownum.

I had initially thought to show a full breakdown of the decode statement to show where it went wrong. But for now pressed for time, perhaps later.

-------- Update ------- Evaluation of the decode( decode( ....
Previously I ended my answer with indication of showing the evolution of the initial decode statement. Well I guess it now time to honor that.

But first a couple comments on the Post itself.
When communicating dates use ISO format. 'yyyy-mm-dd hh24.mi.ss' or just 'yyyy-mm-dd' If dates are in any other format, specify format with to_date('........','FORMAT'). In this case format = 'dd-mon-rr';

Even after you posted table ddl and inserts your query DID NOT match table. Query columns differ from table columns. The following shows the queries and the actual table column names as query_name ==> table_name:

• atyp_code ==> type_code
• to_date ==> addr_to_date
• from_date ==> addr_from_date

Make sure the column names used in queries actually match table column names.

So how do we go about resolving issues within a query. Well often as the first step break into easily identifiable and independent components. Perhaps some can look at this query and see exactly what is happening. I however am not gifted with such insight. So how to break this down into manageable byte size pieces.

The structure of decode is decode(exp, compare, result [,exp2,compare2,result2 ,....], default) and eventuates as -- Structure1 If exp = compare then return result elsif exp2 = compare2 then return result2 ... else return default;

 Or if no default is specified
   -- Structure2
   If exp = compare then return result
   elsif exp2 = compare2 then return result2
   ...
   else return null;

Starting with your inner decode break that down as follows:
Let Ei be statement

  select type_code
    from addr a2
   where a2.id = &ID and a2.status_ind is null
     and (trunc (sysdate) between trunc(nvl (a2.addr_from_date,
                                             nvl (a2.addr_to_date,
                                                  sysdate)))
                              and trunc(nvl (a2.addr_to_date,
                                            sysdate)))
     and a2.type_code = 'AB'

Let Ri be the statement

  select type_code
    from addr a2
   where a2.id = &ID and a2.status_ind is null
     and (trunc (sysdate) between trunc(nvl (a2.addr_from_date,
                                             nvl (a2.addr_to_date,
                                                  sysdate)))
                              and trunc(nvl (a2.addr_to_date,
                                            sysdate)))
     and a2.type_code = 'SP';

And the outer
Let Ro be the statement

  select type_code
    from addr a2
   where a2.id = &ID and a2.status_ind is null
     and (trunc (sysdate) between trunc(nvl (a2.addr_from_date ,
                                             nvl (a2.addr_to_date ,
                                                  sysdate)))
                               and trunc(nvl (a2.addr_to_date ,
                                               sysdate)))

We can now substitute these into the original expression obtaining the expression

decode ( decode (Ei,null,Ri)  ,null,Ro,'ar') )

Running each of the above expressions and manually evaluate the the overall decode expression to see where it's going wrong. But even prior to that a little extra analysis comes to mind. Looking at queries Ei and Ri you can tell that this query has only 2 possible results:
Ei returns AB or null
Ri returns SP or null
Now we can see the first issue with the overall statement:
If Ei returns AB then the match to Null is not satisfied so Ri is bypassed and since there are no more conditions it attempts to return the dafault. However the is no default So it returns null. (see Structure2 above).

If Ei returns null that does match to null (One of or perhaps only condition in Oracle where Null matches Null) so the expression Ri is executed. Now Ri returns either SP or null.
At that point the evaluation of the inner decode completes returning either SP or null. Lets call it Di (Decode inner).

Now the outer decode begins evaluation having been reduced to:

   decode(Di, null, Ro, 'ar');

If Di returns SP we get: SP does not match null so bypass Ro and since no further evaluations return the default 'ar'.

If Di returns null then that does match null so evaluate Ro and return the result. (Note: if the specified ID has 2 or more type_code values that neither of them is SP the Ro throws exception ORA-01427 single-row sub query returns more than one row).

Now we are in the postilion fix the initial statement:
Since Ei can return AB but that gets 'translated' to null as there is no default value we need to a default AB to the inner decode.
Then the outer decode translates all not null returns from Di to the default 'ar' so need to additional compare/return values for AB and SP. Resulting in revising initial statement to

decode (decode (Ei,null,Ri, 'AB'),null,Ro,'SP','SP','AB','AB,'ar')

Now you can re-substitute the original queries back into the statement. Be sure to document this well. It would not do to have a junior developer come along later and have to modify it. In fact I wouldn't even want a senior developer attempting to modify it. Best option abandon decode,it not with completely differentquery as proposed at least in favor or case expression.