How to iterate output of query to another select query as an input parameter?

Question

I want to pass the output of the below query as an input parameter to another query.

 SELECT DISTINCT MV_sum.exch
          FROM LLDMA.MERCAUDIT_VOLUME_AGG_SUMMARY MV_sum,
               EXCHANGE_MAPPING ex_map
         WHERE MV_SUM.EXCH = ex_map.agora_exchange
ORDER BY 1;

Sample OUTPUT of query1:

ABC
CDE
DEF
GHI
XYX

I want to pass this output 1 by 1 as a input parameter to below query iterating the output to another query

I have written the below code as I am new to oracle, but its throwing some error Please help me in this.....

DECLARE
   exch_name       VARCHAR2 (200);
   v_exch          VARCHAR2 (1000);
   v_exch_output   VARCHAR2 (4000);
   v_cnt_tot       VARCHAR2 (4000);
   abc             VARCHAR2 (4000);
   v_str           VARCHAR2 (4000);

   CURSOR tbl
   IS
        SELECT DISTINCT MV_sum.exch
          FROM LLDMA.MERCAUDIT_VOLUME_AGG_SUMMARY MV_sum,
               EXCHANGE_MAPPING ex_map
         WHERE MV_SUM.EXCH = ex_map.agora_exchange
      ORDER BY 1;
BEGIN
   v_cnt_tot := 0;

   OPEN tbl;

   LOOP
      FETCH tbl INTO exch_name;

      EXIT WHEN tbl%NOTFOUND;
      v_str :=
         ' select distinct ''ICTO-10510'' ,   MV_sum.exch , to_char(to_date(MV_sum.TRADEDATE,''yymmdd''), ''dd-Mon-yyyy'')   ,  ''   ,  ''TDV''   ,  MV_sum.new  ,   ''NEW'' ,  '' , ''-- into abc  
from LLDMA.MERCAUDIT_VOLUME_AGG_SUMMARY MV_sum , EXCHANGE_MAPPING ex_map
where (TRADEDATE between TO_CHAR  (sysdate-545, ''YYMMDD'') and TO_CHAR  (sysdate, ''YYMMDD'') )
and MV_SUM.EXCH = ex_map.agora_exchange
and MV_sum.exch = exch_name
group by MV_sum.exch,
to_char(to_date(MV_sum.TRADEDATE, ''yymmdd''), ''dd-Mon-yyyy''),
MV_sum.new';

      EXECUTE IMMEDIATE v_str;

      DBMS_OUTPUT.PUT_LINE (v_str);
   END LOOP;



   CLOSE tbl;
END;
/

Please let me know how to do this.

Answer 1

the code won't return you the result as a "select statement" if this is what you need to achieve. from your code I have noticed: - you are using the cursor only to iterate through the distinct values from EXCHANGE_MAPPING table - you can take out this statement EXECUTE IMMEDIATE v_str - if you use only the select statement you will obtain the same result

    select distinct 'ICTO-10510' ,   MV_sum.exch ,    to_char(to_date(MV_sum.TRADEDATE,'yymmdd'), 'dd-Mon-yyyy'),
                '','TDV',  MV_sum.new ,  'NEW' ,  '', ''
from LLDMA.MERCAUDIT_VOLUME_AGG_SUMMARY MV_sum , EXCHANGE_MAPPING ex_map
where (TRADEDATE between TO_CHAR  (sysdate-545, 'YYMMDD') and TO_CHAR  (sysdate, 'YYMMDD') )
and MV_SUM.EXCH = ex_map.agora_exchange
group by MV_sum.exch,
to_char(to_date(MV_sum.TRADEDATE, 'yymmdd'), 'dd-Mon-yyyy'), MV_sum.new

Answer 2

Hi you can try following code.

DECLARE
  exch_name     VARCHAR2(200);
  v_exch        VARCHAR2(1000);
  v_exch_output VARCHAR2(4000);
  v_cnt_tot     VARCHAR2(4000);
  abc           VARCHAR2(4000);
  v_str         VARCHAR2(4000);

  CURSOR tbl IS
    SELECT DISTINCT MV_sum.exch
      FROM LLDMA.MERCAUDIT_VOLUME_AGG_SUMMARY MV_sum, EXCHANGE_MAPPING ex_map
     WHERE MV_SUM.EXCH = ex_map.agora_exchange
     ORDER BY 1;
BEGIN
  v_cnt_tot := 0;
  for x in tbl loop
    -- no need of EXECUTE IMMEDIATE it will harm performance
    select distinct '' ICTO - 10510 '',
                    MV_sum.exch,
                    to_char(to_date(MV_sum.TRADEDATE, '' yymmdd ''), '' dd - Mon - yyyy ''),
                    '',
                    '' TDV '',
                    MV_sum.new,
                    '' NEW '',
                    '',
                    '' -- into abc  
      from LLDMA.MERCAUDIT_VOLUME_AGG_SUMMARY MV_sum, EXCHANGE_MAPPING ex_map
     where (TRADEDATE between TO_CHAR(sysdate - 545, '' YYMMDD '') and TO_CHAR(sysdate, '' YYMMDD ''))
       and MV_SUM.EXCH = ex_map.agora_exchange
       and MV_sum.exch = exch_name
    **--and  MV_sum.exch = x.exch**  exch value from upper query  
     group by MV_sum.exch, to_char(to_date(MV_sum.TRADEDATE, '' yymmdd ''), '' dd - Mon - yyyy ''), MV_sum.new;

  end loop;

exception
  when others then
    null;
END;

Answer 3

Hi you can try this you need to limit number or rows by rownum =1 if you are interested in all rows then you need to use collection to hold all values. don't know how order matter for you if it matters then you need to modify query inside loop

DECLARE
  exch_name VARCHAR2(200);
  v_exch    VARCHAR2(1000);
  output    VARCHAR2(4000);
  abc       VARCHAR2(4000); -- remove this.

  CURSOR tbl IS
    SELECT DISTINCT MV_sum.exch
      into exch_name
      FROM LLDMA.MERCAUDIT_VOLUME_AGG_SUMMARY MV_sum, EXCHANGE_MAPPING ex_map
     WHERE MV_SUM.EXCH = ex_map.agora_exchange
     ORDER BY 1;
BEGIN

  for x in tbl loop
    select 'ICTO-10510' || ',' || MV_sum.exch || ',' || to_char(to_date(MV_sum.TRADEDATE, 'yymmdd'), 'dd-Mon-yyyy') || ',' || '' || ',' || 'TDV' || ',' ||
           MV_sum.new || ',' || "NEW" || ',' || '' || ',' || ''
      into output
      from LLDMA.MERCAUDIT_VOLUME_AGG_SUMMARY MV_sum, EXCHANGE_MAPPING ex_map
     where (TRADEDATE between TO_CHAR(sysdate - 545, 'YYMMDD') and TO_CHAR(sysdate, 'YYMMDD'))
       and MV_SUM.EXCH = ex_map.agora_exchange
       and MV_sum.exch = x.exch --**changes**
       and rownum = 1
     group by MV_sum.exch, to_char(to_date(MV_sum.TRADEDATE, 'yymmdd'), 'dd-Mon-yyyy'), MV_sum.new;

    /* EXECUTE IMMEDIATE abc
    into output; */
    --dont need this .. .. 
    DBMS_OUTPUT.PUT_LINE(output);
  end loop;

exception
  when others then
    DBMS_OUTPUT.PUT_LINE('null');
    --null;`enter code here`
END;