This discussion is archived
2 Replies Latest reply: Apr 22, 2013 11:00 PM by ASLJQ RSS

Select INTO multiple variables

ASLJQ Newbie
Currently Being Moderated
Hi,

I have a SELECT INTO statement as follows:

-----
SELECT SUM(s1.PRODUCT_QTY) INTO anz
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID
          AND          s2.REGION = 'ANZ'
AND s1.GCM_OPP_ID = s2.GCM_OPP_ID
AND s2.QTR_BOOKED = quarter.QTR_BOOKED
          AND      s1.PRODUCT_ID = s3.PRODUCT_ID
          AND      s3.PRODUCT_TYPE != 'SW';
-----

The statement sums up all the products attributed to the region ANZ and stores it in a NUMBER variable called anz.

The issue is that I have multiple regions: ANZ, JP, ASEAN etc. Wat I would do is to create a statement for each and every region. Is there a way to consolidate them all into a single statement. that means something like

-----
SELECT SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY) INTO anz, jp, asean
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE ..................
-----
  • 1. Re: Select INTO multiple variables
    jeneesh Guru
    Currently Being Moderated
    Like..
    SELECT SUM(decode(s2.region,'ANZ',s1.PRODUCT_QTY,0)) ,
         SUM(decode(s2.region,'JP',s1.PRODUCT_QTY,0)) ,
         ....
    INTO anz,jp,...
    FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3 
    WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID
    --AND s2.REGION = 'ANZ'
    AND s1.GCM_OPP_ID = s2.GCM_OPP_ID
    AND s2.QTR_BOOKED = quarter.QTR_BOOKED
    AND s1.PRODUCT_ID = s3.PRODUCT_ID
    AND s3.PRODUCT_TYPE != 'SW';
    Or use two dimensional arrays..
    declare
     type region_rec is record(region EXACTS_TRANSACTIONS.region%type,qty number);
     type region_rec_tab is table of region_rec;
     region_rec_tab1 region_rec_tab;
    begin
     SELECT s2.region,SUM(s1.PRODUCT_QTY)
     bulk INTO region_rec_tab1
     FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3 
     WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID
     --AND s2.REGION = 'ANZ'
     AND s1.GCM_OPP_ID = s2.GCM_OPP_ID
     AND s2.QTR_BOOKED = quarter.QTR_BOOKED
     AND s1.PRODUCT_ID = s3.PRODUCT_ID
     AND s3.PRODUCT_TYPE != 'SW'
     group by s2.region;
    
     .....
     .....
     
    end;
    Edited by: jeneesh on Apr 23, 2013 9:36 AM
  • 2. Re: Select INTO multiple variables
    ASLJQ Newbie
    Currently Being Moderated
    Thanks! that was exactly what i was looking for.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points