CODEWITHSUNDEEP
sqloracle-database
AngryOtter
AngryOtter

Reputation: 169

Oracle use variable in IN clause

The following query will be use as a sub-query in a IN clause within a trigger

select RECEIPT_USER from ABCD.GENERIC_FF_EVNT_LAST
WHERE RECEIPT_USER is not null
group by RECEIPT_USER
having max(load_Date) > add_months(SYSDATE,-48)

Simple Trigger

create or replace TRIGGER ABCD.T_EVNTS_UPSERT
FOR INSERT OR UPDATE ON ABCD.EVNTS
COMPOUND TRIGGER

  Type r_evnts_type Is Record (
      shpmt_unts_id  ABCD.evnts.shpmt_unts_id%Type,
      evnts_id       ABCD.evnts.evnts_id%Type,
      evnt_date      ABCD.evnts.evnt_date%Type,
      last_updt_user ABCD.evnts.db_rw_last_updt_usr%Type
  );
  Type rt_evnts_type Is Table Of r_evnts_type Index By Pls_Integer;
  --v_USER_LIST ABCD.GENERIC_FF_EVNT_LAST.RECEIPT_USER%TYPE;
  
  i            Pls_integer;
  rt_1         rt_evnts_type;  
  rt_2         rt_evnts_type;
  rt_3         rt_evnts_type;
  rt_4         rt_evnts_type;
  rt_5         rt_evnts_type;
  rt_6         rt_evnts_type;
  rt_7         rt_evnts_type;
  rt_8         rt_evnts_type;
  rt_9         rt_evnts_type;
  rt_10        rt_evnts_type;

Before Each Row Is
Begin
    --Not relevant
End Before Each Row;


AFTER EACH ROW IS
BEGIN
     --the data of tabletype gets populated
END AFTER EACH ROW;


AFTER STATEMENT IS
BEGIN      
    --There are 10s of these

    If (rt_1.Exists(1)) Then
    ForAll i In 1 .. rt_1.Last
        UPDATE ABCD.SHPMT_UNTS SU
        SET SU.CONUS_ARRIVAL_DT = rt_1(i).EVNT_DATE,
            SU.CONUS_ARRIVAL_EVENTID = rt_1(i).EVNTS_ID,
            SU.CONUS_FLAG = '1'
        WHERE SU.SHPMT_UNTS_ID = rt_1(i).SHPMT_UNTS_ID
          AND (SU.CONUS_DEPARTURE_EVENTID IS NULL or rt_1(i).last_updt_user in
          (select RECEIPT_USER from ABCD.GENERIC_FF_EVNT_LAST
            WHERE RECEIPT_USER is not null
            group by RECEIPT_USER
            having max(load_Date) > add_months(SYSDATE,-48))); <--- long and less readable because there are 10s of these
    End If;

    ---If (rt_2.Exists(1)) Then
    ---If (rt_3.Exists(1)) Then

End After Statement;
    
END t_evnts_upsert;

I am trying to see if there is a way to store the result of the subquery in a variable/cursor and then use it in the IN clause, this way I don't have to repetitively call the subquery each time.

What I have tried:

Method 1 Use cursor:

cursor user_list is
select RECEIPT_USER from ABCD.GENERIC_FF_EVNT_LAST
WHERE RECEIPT_USER is not null
group by RECEIPT_USER
having max(load_Date) > add_months(SYSDATE,-48);

and use it so the where Clause become:

WHERE SU.SHPMT_UNTS_ID = rt_conus_ar(i).SHPMT_UNTS_ID
      AND (SU.CONUS_DEPARTURE_EVENTID IS NULL or rt_conus_ar(i).last_updt_user in user_list.RECEIPT_USER)

Method 2 Store into a variable:

  v_USER_LIST ABCD.GENERIC_FF_EVNT_LAST.RECEIPT_USER%TYPE;

Then do Select INTO v_USER_LIST.......

this doesn't work either

Is there a way to store the result of the subquery into a variable of some sort and use it in the IN clause?

Upvotes: 0

Views: 77

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

In general you can do it.

declare
   type rt_evnts_type is table of VARCHAR2(100);
   rt_events rt_evnts_type;
begin

   select RECEIPT_USER 
   bulk collect into rt_events 
   from ABCD.GENERIC_FF_EVNT_LAST
   WHERE RECEIPT_USER is not null
   group by RECEIPT_USER
   having max(load_Date) > add_months(SYSDATE,-48);


    UPDATE ABCD.SHPMT_UNTS SU
    SET SU.CONUS_ARRIVAL_DT = rt_1(i).EVNT_DATE,
        SU.CONUS_ARRIVAL_EVENTID = rt_1(i).EVNTS_ID,
        SU.CONUS_FLAG = '1'
    WHERE SU.SHPMT_UNTS_ID = rt_1(i).SHPMT_UNTS_ID
      AND (
         SU.CONUS_DEPARTURE_EVENTID IS NULL
         OR rt_1(i).last_updt_user MEMBER OF rt_events 
      );

end;

You can also use expression SELECT COLUMN_VALUE FROM TABLE(rt_events)

Note, I did not test above code. I am not 100% sure, perhaps you have to define the type as database object in order to use it SQL query, i.e.

CREATE TYPE rt_evnts_type is table of VARCHAR2(100);

You may review your data design, why do you create 10 different variables of rt?

A more generic approach could be this one:

Type r_evnts_type Is Record (
  shpmt_unts_id  ABCD.evnts.shpmt_unts_id%Type,
  evnts_id       ABCD.evnts.evnts_id%Type,
  evnt_date      ABCD.evnts.evnt_date%Type,
  last_updt_user ABCD.evnts.db_rw_last_updt_usr%Type,
  rt_type NUMBER
);
Type rt_evnts_type Is Table Of r_evnts_type;
rt_evnts rt_evnts_type;

... 

   WHERE CONUS_ARRIVAL_EVENTID =ANY
      (select EVNTS_ID
       from TABLE(rt_evnts)
       where rt_type = 1) 

  FOR r in 1..5 LOOP
   ...
   WHERE CONUS_ARRIVAL_EVENTID =ANY
      (select EVNTS_ID
       from TABLE(rt_evnts)
       where rt_type = r) 
  END LOOP;

The Multiset Operators and Multiset Conditions may make your life easier.

Upvotes: 2

Related Questions