1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please help how to make oracle command

Discussion in 'SQL PL/SQL' started by rosy, Jul 22, 2015.

  1. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Dear Everyone please help me sql command as below
    I have an table like below:
    [​IMG][/url] hebergeur d images[/IMG] 3.png
    and I want to make an sql command
    1. short by Date, Location, parts
    2. after short is ok I want to create below infor

    2.png

    I already make execel to do it but it not convinionces .
    So could everyone help us to create sql command.

    thank you
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Rosy,

    Use the following query and please let us know here if its useful.

    Code (SQL):
    SELECT DAT,PARTS,LOCATION,OPERATION,QTY,rank,VALUE, CASE WHEN rank = 1 THEN VALUE ELSE VALUE + nvl(lag(VALUE,1)OVER(ORDER BY location,rank),0) END cal
    FROM (SELECT dat, parts, location, operation, qty, decode(operation,'+',1,'-',-1)*qty VALUE, RANK() OVER (PARTITION BY location ORDER BY ROWNUM) RANK
    FROM <table_name>) ORDER BY location,rank;
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I don't really follow the logic of column "I". This spreadsheet seems to be trying to track parts being added and used. The way I read it, in row three, 100 of part ABCZ1234A are added to inventory. In row four, 10 parts are used, leaving 90. So far so good.

    However, in row five, 2 parts from inventory are used, leaving -12. I don't understand going from 90 parts to -12 after using 2. I would think that the formula in column "I" should be: '=IF(E3<>E2,I3, I3+H2)'. That formula would take the calculated total from the previous row and add the change (column H) to generate a new total (if the current row is the same part number as the previous row).

    Mind you, I may misunderstand the purpose of the spreadsheet since you didn't actually provide any details.
     
  4. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Thank you so much for your answer, I already used it but result is diffirent, could you help me revise it?
    Thank you
     
    Last edited: Jul 23, 2015
  5. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Thank for your commentation I understood, exactly below is correct. Please help me. The purpose of it is using calculation inventory Part in my company.
    1.png
     
  6. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Dear Everyone could you help me? it is necessary with my job. thank you
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The people who supply answers on this forum do so on a volunteer basis. Answering questions takes up time and returns essentially nothing to us.

    I have a fair idea of a SQL statement that should work. However, it is not so simple that I am positive it will without testing. I can't test without creating a table with the data from your first post and you have provided that data in the worst possible fashion. Since all you provided was a screenshot with data, it would take me about a half hour to create a table and manually create INSERT statements to fill the table in order to test a query. This is easily 15-20 minutes of my time before I can even start writing a query. I don't really want to do this. I have contributed to your answer already by pointing out the calculation problem.

    If you want an answer to problems of this nature, my recommendation is that when you ask the question that you supply the CREATE TABLE and INSERT statements required to build a test case. This will allow the people on this forum to construct and test an answer fairly easily, so more will be willing to offer suggestions.
     
    Bharat likes this.
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Have you tried modifying the sql I have posted before. I hope minor changes will fix this issue. Try many cases on the provided sql and check.
     
  9. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    I trying to modify you command as below
    but could not be run

    SELECT DAT,PARTS,LOCATION,OPERATION,QTY,rank,VALUE, CASE WHEN rank = 1 THEN VALUE ELSE VALUE + nvl(lag(cal,1)OVER(ORDER BY location,rank),0) END cal
    FROM (SELECT dat, parts, location, operation, qty, decode(operation,'+',1,'-',-1)*qty VALUE, RANK() OVER (PARTITION BY location ORDER BY ROWNUM) RANK
    FROM <table_name>) ORDER BY location,rank;
     
  10. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese


    Create Table

    CREATE TABLE Inventory (

    Applie_Date datetime NOT NULL,

    Parts nchar(10) NOT NULL,

    Location nchar(10) NOT NULL,

    Operation nchar(1) NOT NULL,

    Qty int NOT NULL
    )

    Insert command


    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,A1,+,100)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,A1,-,10)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,A1,-,2)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,A1,+,43)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,A1,-,40)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,A1,-,30)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,A1,-,12)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,A1,-,3)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,B1,+,90)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,B1,-,1)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,B1,+,2)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,B1,+,3)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,B1,+,4)
    INSERT INTO Inventory (Applie_Date,Parts,Location,Operation,Qty) VALUES (07/22/2015 ,ABC1234A,C1,+,9)

    Please help thank you for your comments.
     
  11. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Rosy,

    Please use the following query and let us know if it works fine or not.
    Code (SQL):
    SELECT DAT,PARTS,LOCATION,OPERATION,QTY,VALUE,
    (
    SELECT SUM(VALUE) FROM (SELECT dat, parts, location, operation, qty, decode(operation,'+',1,'-',-1)*qty VALUE, RANK() OVER (PARTITION BY location ORDER BY ROWNUM) RANK
    FROM test_test)a WHERE a.rank <= (b.rank) AND a.parts = b.parts AND a.location = b.location
    )cal
    FROM (SELECT dat, parts, location, operation, qty, decode(operation,'+',1,'-',-1)*qty VALUE, RANK() OVER (PARTITION BY location ORDER BY ROWNUM) RANK
    FROM <table_name>) b ORDER BY location,rank;
     
    rosy likes this.
  12. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    A test case that doesn't work is only a small change from no test case at all. The data types are not even valid for Oracle. SQL Server would be my guess, but possibly something else. The INSERTs wouldn't have worked on any DB I know of because the strings weren't enclosed in quotes. That said, fixing it was easier than creating one from scratch.

    Bharat's query -- with some minor fixes and changes to fit your column/table names, seems to provide the requested results:

    Code (Text):

    CREATE TABLE Inventory (
    Applie_Date  date NOT NULL,
    Parts        char(10) NOT NULL,
    Location     char(10) NOT NULL,
    Operation    char(1) NOT NULL,
    Qty          NUMBER NOT NULL
    );

         
    SELECT Applie_Date, Parts, Location, Operation, Qty,
           (SELECT SUM(inv_change)
            FROM   (SELECT Applie_Date, Parts, Location, Operation, Qty,
                           DECODE(operation,'+',1,-1)*qty inv_change,
                           RANK() OVER (PARTITION BY location ORDER BY ROWNUM) RANK
                    FROM   Inventory) inv1
                    WHERE  inv1.rank <= (inv2.rank)
                    AND    inv1.parts = inv2.parts
                    AND    inv1.location = inv2.location
            ) calc
    FROM   (SELECT Applie_Date, Parts, Location, Operation, Qty,
                   DECODE(operation,'+',1,-1)*qty inv_change,
                   RANK() OVER (PARTITION BY location ORDER BY ROWNUM) RANK
            FROM   Inventory) inv2
    ORDER BY location, rank;

    APPLIE_DATE PARTS      LOCATION   OPERATION        QTY       CALC
    ----------- ---------- ---------- --------- ---------- ----------
    22-JUL-15   ABC1234A   A1         +                100        100
    22-JUL-15   ABC1234A   A1         -                 10         90
    22-JUL-15   ABC1234A   A1         -                  2         88
    22-JUL-15   ABC1234A   A1         +                 43        131
    22-JUL-15   ABC1234A   A1         -                 40         91
    22-JUL-15   ABC1234A   A1         -                 30         61
    22-JUL-15   ABC1234A   A1         -                 12         49
    22-JUL-15   ABC1234A   A1         -                  3         46
    22-JUL-15   ABC1234A   B1         +                 90         90
    22-JUL-15   ABC1234A   B1         -                  1         89
    22-JUL-15   ABC1234A   B1         +                  2         91
    22-JUL-15   ABC1234A   B1         +                  3         94
    22-JUL-15   ABC1234A   B1         +                  4         98
    22-JUL-15   ABC1234A   C1         +                  9          9

    14 rows selected
     
    rosy likes this.
  13. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Thank you so much for your help it very good result. But if I want add ORDER BY Applie_Date,Parts, location, rank; then result is diffirent. So could you help me again.
    I also try to change but result not correct.
     
  14. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    So you want to change the Data also ? Please confirm you just want to order the data after calculating or with calculation part you want to order by.
     
  15. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Hi Bharat, thank you for reply my answer, exactly I want to column CAL also change when ORDER BY change, detail please check attached file.
    The data must sort according to first Location, Applied_date, Transaction_id and CAL also calculate according to sort.

    and if I have alot of parts what do i must to do? Please help and give suggestion. thank you
     

    Attached Files:

    • tmp.xls
      File size:
      113 KB
      Views:
      2
  16. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    No. A query has been provided which returns data that matches what you initially requested. Now that you have that, you have changed the requirements. I have to deal with changing requirements (as annoying as they are) when they come from people who are paying my salary. I don't have to deal with it for volunteer work. I'm done with this thread. Bharat may continue to help or not as he pleases.
     
    rosy likes this.
  17. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Thank you so much for your supported.
    I already finished to completed this Oracle command. by using ROW_NUMBER() OVER ( ORDER BY B.DATE_APPLIED,B.TRANSACTION_ID) RANK with your help command.