1 2 Previous Next 20 Replies Latest reply: Aug 28, 2013 3:14 AM by AlbertoFaenza RSS

    Problem to resolve

    3e68b26b-bb14-4a01-aa09-9a07741a91cf

      Hello guys,

       

         Im having a trouble to resolve one thing, i have a quantity of products in a table, like this:

      CODNAMEQuantity
      001PROD13
      002PROD24
      003PROD30


      The problem is i need to somehow insert the amount o records into another table acording with the quantity at this first table, i need to do like this:

      Header 1Header 2
      001PROD1
      001PROD1
      001PROD1
      002PROD2
      002PROD2
      002PROD2
      002PROD2

      but idk how, any ideia?

        • 1. Re: Problem to resolve
          AlbertoFaenza

          Hi,

           

          maybe like this:

           

          with mydata as

          (

             select '001' cod, 'PROD1' name,    3 qty from dual union all

             select '002' cod, 'PROD2' name,    4 qty from dual union all

             select '003' cod, 'PROD3' name,    0 qty from dual

          )

          select cod, name

            from mydata

          where qty > 0

          connect by prior cod = cod

              and level <= qty

              and prior sys_guid() is not null;

           

           

          COD NAME

          --- -----

          001 PROD1

          001 PROD1

          001 PROD1

          002 PROD2

          002 PROD2

          002 PROD2

          002 PROD2

           

           

          Regards.

          Alberto

          • 2. Re: Problem to resolve
            Frank Kulash

            Hi,

             

            You can do that using XMLTABLE.  I don't have a copy of your table, so I'll use scott.dept to illustrate:

             

            INSERT INTO new_dept (deptno, dname, loc)

            SELECT   d.*

            FROM     scott.dept  d

            CROSS JOIN  XMLTABLE ( '1 to xs:integer (.)'

                                   PASSING d.deptno / 10

                                 )

            ;

            This inserts the following data:

             

                DEPTNO DNAME          LOC

            ---------- -------------- -------------

                    10 ACCOUNTING     NEW YORK

                    20 RESEARCH       DALLAS

                    20 RESEARCH       DALLAS

                    30 SALES          CHICAGO

                    30 SALES          CHICAGO

                    30 SALES          CHICAGO

                    40 OPERATIONS     BOSTON

                    40 OPERATIONS     BOSTON

                    40 OPERATIONS     BOSTON

                    40 OPERATIONS     BOSTON

            that is, 1 copy of deptno=10, 2 copies of deptno=20, ...,  n copies of deptno 10*n.

            • 3. Re: Problem to resolve
              Hoek


              SQL> create table t1 as
                2  select '001' cod,  'PROD1' nm,  3 qty from dual union
                3  select '002', 'PROD2', 4 from dual union
                4  select '003', 'PROD3', 0 from dual;

              Table created.

              SQL> create table t2 (hdr1 varchar2(10), hdr2 varchar2(10));

              Table created.

              SQL> insert into t2
                2  select cod
                3  ,      nm
                4  from   t1
                5  where qty>0
                6  connect by level <= qty
                7         and cod = prior cod
                8         and nm = prior nm
                9         and prior dbms_random.value is not null;

              7 rows created.

              SQL> select * from t2;

              HDR1       HDR2
              ---------- ----------
              001        PROD1
              001        PROD1
              001        PROD1
              002        PROD2
              002        PROD2
              002        PROD2
              002        PROD2

              7 rows selected.

              • 4. Re: Problem to resolve
                3e68b26b-bb14-4a01-aa09-9a07741a91cf

                It seems like a good way but i dont undestand how it works haha never saw that xmltables before, the scruture of the  table is like i draw, of course, with more, way more data and fields

                • 5. Re: Problem to resolve
                  3e68b26b-bb14-4a01-aa09-9a07741a91cf

                  its seems a good way to do that too, but i have a table with more than 2000 records, how i will do this and way more fields.

                  • 6. Re: Problem to resolve
                    Hoek

                    2000 rows is peanuts, nothing, nada.

                    Just add the necessary columns  you need to the query.

                    Play a bit with it, it shouldn't be too hard to figure this out.

                    • 7. Re: Problem to resolve
                      Frank Kulash

                      Hi,

                       

                      XMLTABLE will work with any table.  You still haven't posted a test version of your table, so I still can't test it with your table.

                      CONNECT BY, as Alberto and Hoek suggested, will also work with any table, but you have to put something unique (like the primary key, or ROWID) in the CONNECT BY clause.  The XMLTABLE solution I posted will work even if there is nothing unique in the original table.

                       

                      What is the problem when you try XMLTABLE?  Post your query.

                      Whenever you have a problem, it also helps if you post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data.

                      See the forum FAQ: https://forums.oracle.com/message/9362002

                      • 8. Re: Problem to resolve
                        3e68b26b-bb14-4a01-aa09-9a07741a91cf

                        This is a sample of my table and in the end i will found the quantity

                         

                        1CONFERIDOOKPRONTA ESU-7N-B85189090AIRONSUPORTE PARA CAIXA ACœSTICA AIRON UNIVERSAL SU-7N - PAR78,000,2667,9147,5468,6980,8121,153
                        2CONFERIDOOKPRONTA ESU-7N-S85189090AIRONSUPORTE PARA CAIXA ACœSTICA AIRON UNIVERSAL SU-7N - PAR78,000,2667,9147,5468,6980,8121,151
                        3CONFERIDOOKPRONTA ESU-7N-W85189090AIRONSUPORTE PARA CAIXA ACœSTICA AIRON UNIVERSAL SU-7N - PAR78,000,2667,9147,5468,6980,8121,152
                        • 9. Re: Problem to resolve
                          AlbertoFaenza

                          3e68b26b-bb14-4a01-aa09-9a07741a91cf wrote:

                           

                          This is a sample of my table and in the end i will found the quantity

                           

                          1 CONFERIDO OK PRONTA E SU-7N-B 85189090 AIRON SUPORTE PARA CAIXA ACœSTICA AIRON UNIVERSAL SU-7N - PAR 78,00 0,26 67,91 47,54 68,69 80,81 21,15 3
                          2 CONFERIDO OK PRONTA E SU-7N-S 85189090 AIRON SUPORTE PARA CAIXA ACœSTICA AIRON UNIVERSAL SU-7N - PAR 78,00 0,26 67,91 47,54 68,69 80,81 21,15 1
                          3 CONFERIDO OK PRONTA E SU-7N-W 85189090 AIRON SUPORTE PARA CAIXA ACœSTICA AIRON UNIVERSAL SU-7N - PAR 78,00 0,26 67,91 47,54 68,69 80,81 21,15 2

                          This is not how sample data should be posted. You should post valid CREATE TABLE statement and INSERT statements.

                           

                          Please read: Re: 2. How do I ask a question on the forums?

                          Everything is explained there.

                           

                          One more suggestion, edit your name to have something more meaningful than 3e68b26b-bb14-4a01-aa09-9a07741a91cf


                          Kind regards.

                          Alberto

                          • 10. Re: Problem to resolve
                            3e68b26b-bb14-4a01-aa09-9a07741a91cf

                            all right, im a rookie sorry, this is what you asked for:

                            -- Create table

                            create table TESTQ

                            (

                              STATUS      VARCHAR2(50),

                              INFO        VARCHAR2(50),

                              DT_ENTG     VARCHAR2(50),

                              CD_PROD     VARCHAR2(50),

                              CD_FISC     NUMBER,

                              CD_FABR     NUMBER,

                              NM_FABR     VARCHAR2(255),

                              CD_BAR      VARCHAR2(30),

                              CD_DIST     NUMBER(10),

                              NM_DIST     VARCHAR2(255),

                              CD_DIST_EXT NUMBER(10),

                              NM_DIST_EXT VARCHAR2(255),

                              NM_PROD     VARCHAR2(255),

                              COR         VARCHAR2(255),

                              TAMN        VARCHAR2(20),

                              MATER       VARCHAR2(100),

                              PRC_HTEC    NUMBER(13,2),

                              PESO        NUMBER(10,2),

                              PRC_TBL     NUMBER(13,2),

                              CUSTO_ST    NUMBER(13,2),

                              PRC_CUST    NUMBER(13,2),

                              DESC_PRD    NUMBER(13,2),

                              DESC_ESP    NUMBER(13,2),

                              REAL        NUMBER(13,2),

                              IDEAL       NUMBER(13,2),

                              LC_V_P      NUMBER(13,2),

                              ESTQ        NUMBER(5)

                            )

                            and

                            insert into testq values                  

                            ('CONFERIDO' , 'OK' , 'PRONTA E' , 'SU-7N-B',85189090,NULL,'AIRON',null,null,null,null,null, 'SUPORTE PARA CAIXA ACœSTICA AIRON UNIVERSAL SU-7N - PAR', null, null, null, '78,00', '0,26', '67,91',null, '47,54',null,null,'68,69','80,81', '21,15', 3);

                            Att,

                            • 11. Re: Problem to resolve
                              AlbertoFaenza

                              You have been provided 2 different solutions:

                              • Using CONNECT BY
                              • Using XMLTABLE

                              Have you tried those?

                              Are they working as expected?

                              If not, what problem do you have?

                              Which are the fields to be considered with the new structure you have posted?

                              If you still have problem, do you mind posting at least more than one row? One INSERT only is not too much. Can you post 2 or 3 rows?

                               

                              Regards.

                              Alberto

                              • 12. Re: Problem to resolve
                                Hoek

                                select *

                                from   testq

                                where estq>0

                                connect by level <= estq

                                             and  prior dbms_random.value is not null

                                             and cd_prod = prior cd_prod

                                             and nm_prod = prior nm_prod;

                                             -- add more columns here that uniquely identify a row if necessary

                                • 13. Re: Problem to resolve
                                  Frank Kulash

                                  Hi,

                                   

                                  Thanks for posting the sample data.  Don't forget to post the results you want from that data.

                                  Into what table will you be inserting the new rows?

                                   

                                  How do you know how many copies to make of each row?  In your original message, it was the wantity column.  If it's the estq column now, then:

                                   

                                   

                                  INSERT INTO  destination_table (col_1, col_2, ...)

                                  SELECT   t.*   -- or list whatever columns you want

                                  FROM        testq  t

                                  CROSS JOIN  XMLTABLE ( '1 to xs:integer (.)'

                                                   PASSING estq

                                         )

                                  ;

                                  Do you really need all those columns to show what the problem is?  Can't you post, say, only 3 columns, as in your original message?  Just mention how many columns you really have, in case it matters.

                                  • 14. Re: Problem to resolve
                                    Greg Spall

                                    Hey Alberto .. what's this line do, specifically?

                                     

                                    and prior sys_guid() is not null;


                                    ??

                                    1 2 Previous Next