2 Replies Latest reply: Apr 23, 2013 1:00 AM by ASLJQ RSS

    Select INTO multiple variables

    ASLJQ
      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
          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
            Thanks! that was exactly what i was looking for.