7 Replies Latest reply: Nov 24, 2012 12:57 PM by Frank Kulash RSS

    sql query for given data

    613602
      My Oracle Version
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

      I have table consolidate stock
      table script

      CREATE TABLE PIPEMILL.consolidate_stock
      (
      SIZE1 NUMBER,
      CLASS1 NUMBER,
      ITEM_SIZE VARCHAR2(123 BYTE),
      ITEM_GRP VARCHAR2(2 BYTE),
      ITEM_QUALITY VARCHAR2(3 BYTE),
      PIPE_LENGTH NUMBER,
      ISI_SPEC VARCHAR2(4000 BYTE),
      STAMP VARCHAR2(70 BYTE),
      PCS NUMBER,
      MTR NUMBER,
      TON NUMBER
      )

      table data
      SIZE1     CLASS1     ITEM_SIZE     ITEM_GRP     ITEM_QUALITY     PIPE_LENGTH     ISI_SPEC     STAMP     PCS     MTR     TON
      
      15       2     15 x M              GI                  PE                 6                             EN1025  30     180     0.111
      I want to output like from above table.
      wono ITEM_SIZE     ITEM_CLASS     ITEM_GROUP     ITEM_QUALITY     ITEM_SPEC       ITEM_PSL     PIPE_LENGTH     STAMP     PCS 
       10       15               2             GI                PE                                                    6     15
       20       15               2             GI                PE                                                       6     15
      In this output two wo numberwith same size , i want 30 pcs which from consolidate stock divide in same qty 15-15.

      thanks
        • 1. Re: sql query for given data
          ranit B
          wono ITEM_SIZE     ITEM_CLASS     ITEM_GROUP     ITEM_QUALITY     ITEM_SPEC       ITEM_PSL     PIPE_LENGTH     STAMP     PCS 
          10       15               2             GI                PE                                                    6     15
          20       15               2             GI                PE                                                       6     15
          In this output two wo numberwith same size , i want 30 pcs which from consolidate stock divide in same qty 15-15.
          How is 'wono' generated?
          Suppose, no of pieces wanted is 35 and NOT 30. How will the output vary?

          Please explain clearly and provide the Insert Scripts.

          Ranit B.
          • 2. Re: sql query for given data
            613602
            Insert statement for consolidate_stock
            insert into consolidate_stock values(15,2,'15 x M','GI','PE',6,'EN1025',null,30,180,.111);
            Output data comes from wo_club table where wono store.

            wo_club table structure.
            CREATE TABLE PIPEMILL.WO_CLUB
            (
              WO_NO           NUMBER(5)                     NOT NULL,
              ITEM_SIZE       NUMBER(15,3)                  NOT NULL,
              ITEM_CLASS      NUMBER(6,3)                   NOT NULL,
              ITEM_GROUP      VARCHAR2(2 )              NOT NULL,
              ITEM_QUALITY    VARCHAR2(3 )              NOT NULL,
              ITEM_SPEC       NUMBER(2)                     NOT NULL,
              ITEM_PSL        NUMBER(1)                     ,
              PIPE_LENGTH     NUMBER(7,3)                   NOT NULL,
              STAMP           VARCHAR2(70  ),
              WO_PCS NUMBER
            )
            insert statement for wo_club
            insert into wo_club values(10,15,2,'GI','PE',33,0,6,null,10);
            insert into wo_club values(20,15,2,'GI','PE',33,0,6,null,20);
            consolidate_stock and wo_club table relation with size,item_class,item_group and item_quality.


            i want to that stock(30) divide into based on wo_pcs. suppose wo_pcs 10 then pcs qty 10 and remaining transfer to next wo size. if qty 35 then remaining 5 qty not
            show any where other wise if there are any wo is available for same size then transfer to that wo.

            thank

            Edited by: empty on Nov 24, 2012 1:51 AM
            • 3. Re: sql query for given data
              Frank Kulash
              Hi,
              empty wrote:
              ... i want to that stock(30) divide into based on wo_pcs. suppose wo_pcs 10 then pcs qty 10 and remaining transfer to next wo size.
              That seems to contradict what you said in your first message:
              empty wrote:
              I want to output like from above table.
              wono ITEM_SIZE     ITEM_CLASS     ITEM_GROUP     ITEM_QUALITY     ITEM_SPEC       ITEM_PSL     PIPE_LENGTH     STAMP     PCS 
              10       15               2             GI                PE                                                    6     15
              20       15               2             GI                PE                                                       6     15
              I think you want something like this:
              WITH  got_total_pcs       AS
              (
                   SELECT  wo_club.*
                   ,     SUM (wo_pcs) OVER (ORDER BY  wo_no)
                                        AS total_pcs
                   FROM    wo_club
              )  
              SELECT    gt.wo_no
              ,       gt.item_size
              ,       gt.item_class
              ,       gt.item_group
              ,       gt.item_quality
              ,       NULL               AS item_spec
              ,       NULL               AS item_psl
              ,       gt.pipe_length
              ,       gt.stamp
              ,       LEAST ( cs.pcs + gt.wo_pcs - gt.total_pcs
                          , gt.wo_pcs
                        )          AS pcs
              FROM       got_total_pcs      gt
              JOIN       consolidate_stock  cs      ON   cs.pcs  >= gt.total_pcs
              ORDER BY  gt.wo_no
              ;
              • 4. Re: sql query for given data
                ranit B
                Hi Frank,

                Can you please explain the working of your query?
                I'm not gettin how it'll get subtracted in every row.

                And what is the purpose behind the LEAST used ?

                (@all - sorry guys for going off track...)
                Frank - You in office on Sunday also ??? or in Home?

                Ranit B.
                • 5. Re: sql query for given data
                  Satyaki_De
                  ranit B wrote:
                  And what is the purpose behind the LEAST used ?
                  Check the given link -

                  [url http://psoug.org/definition/LEAST.htm] Least Function
                  (@all - sorry guys for going off track...)
                  Frank - You in office on Sunday also ??? or in Home?

                  Ranit B.
                  Good point.... ;)

                  Satyaki De.
                  • 6. Re: sql query for given data
                    ranit B
                    Hi Satyaki.... i've added u as contact in LinkedIn... Can you please reply me there?
                    Good to see you as Guru. :-)

                    Ranit B.
                    • 7. Re: sql query for given data
                      Frank Kulash
                      Hi,
                      ranit B wrote:
                      ... I'm not gettin how it'll get subtracted in every row.
                      ,       LEAST ( cs.pcs + gt.wo_pcs - gt.total_pcs
                      --                                ^
                      --                         |
                      --           it gets subtracted  |  here
                      --
                                  , gt.wo_pcs
                                )          AS pcs
                      And what is the purpose behind the LEAST used ?
                      The last column is the amount of the outstanding balance that matches the current wo_pcs.
                      If outstanding balance is less than the current wo_pcs, then the full outstanding balance matches this row.
                      If not, only the amount of the cruuent wo_pcs is matched.
                      So the amount to report on the current row is the lesser value of the full outstanding balance and wo_pcs. That's waht LEAST does.