3 Replies Latest reply: Jan 2, 2013 12:23 PM by rp0428 RSS

    Dynamic Query at Run time

    936666
      Hi,
      Happy New Year!!

      I am having some problem

      Split table details

      startid, endid
      1,1
      2,2
      3,4

      expected output is 1,2,34

      Begin
      P_blob v_local varchar2(1800) ='1234';

      select listagg(COLUMN_NAME, ',') within group (order by column_id) INTO Column_name
      from user_tab_columns where table_name= upper('mytable' );

      select listagg(column_id, ',') within group (order by column_id) INTO Column_id
      from user_tab_columns where table_name= upper('mytable' );

      insert into myewtable ('|| Column_name || ')
      with t1 as( select '''|| P_blob || ''' str from dual),
      t2 as( Select startid, endid from Split),
      t3 as
      ( select str,startid s,endid e, row_number() over(order by startid) rn from t1,t2)
      select * from t3 pivot
      (
      max(substr(str,s,e)) str for rn in ('|| Column_id || ')
      );
      end;

      how so i represent the Column_name as column values here.

      Query should be
      insert into myewtable ('col1,col2,col3 ')
      with t1 as( select '''|| P_blob || ''' str from dual),
      t2 as( Select startid, endid from Split),
      t3 as
      ( select str,startid s,endid e, row_number() over(order by startid) rn from t1,t2)
      select * from t3 pivot
      (
      max(substr(str,s,e)) str for rn in ('1,2,3')
      );
      end;
      i am getting as Column_name ,Column_id.

      how to represent the column values inside the query.


      Thanks
        • 1. Re: Dynamic Query at Run time
          Manik
          Can you please check if this suffices your requirement. (I extended few testcases)
          WITH t AS
                  (SELECT 1 startid, 1 endid FROM DUAL
                   UNION ALL
                   SELECT 2, 2 FROM DUAL
                   UNION ALL
                   SELECT 3, 4 FROM DUAL
                   union all
                   select 9,9 from dual
                   union all
                   select 1,5 from dual)
          SELECT listagg (DECODE (startid, endid, endid, startid || endid), ',')
                    WITHIN GROUP (ORDER BY 1) finstr
            FROM t;
          output
          FINSTR
          1,15,2,34,9
          Cheers,
          Manik.
          • 2. Re: Dynamic Query at Run time
            936666
            Thanks for your time but it does not resolves my problem.
            • 3. Re: Dynamic Query at Run time
              rp0428
              >
              Thanks for your time but it does not resolves my problem.
              >
              Wll you need to explain why or no one else will be able to help you either.