This discussion is archived
7 Replies Latest reply: Nov 24, 2012 10:33 AM by ranit B RSS

sql query for given data

613602 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points