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