Oracle use variable in IN clause

Question

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?

It depends on what you mean by "don't have to repetitively call the subquery each time." Your trigger code is only invoking that once. So do you mean that subsequent trigger executions should bypass having to execute it? — Paul W
– Paul W, Commented Feb 8, 2023 at 23:07
@PaulW because there are 10 of these tabletype from rt_1 to rt_10, each of them holds numbers of data in there. Since my code will loop through them, for each interaction of each rt, that subquery is getting called. So it is getting called a lot of times. That's why I am looking for a way to just call it once, store it somewhere and use it when I need to. — AngryOtter
– AngryOtter, Commented Feb 9, 2023 at 14:20

Wernfried Domscheit · Accepted Answer · 2023-02-09 07:43:36Z

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.

There are multiple types of eventid, and not all are been processed the same way, so each rt saves rows of different events.

Collectives™ on Stack Overflow

Oracle use variable in IN clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related