Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Unanswered: Public variables

    I want to do something which i know can be done but How?

    I was thinking of using a public variable, but cant seem to find how to declare one, fill it and (re-)use it in a query....

    What i want to do is this:
    I have a list/table of incomming product and a table of sales.
    I would like to calculate the # in stock

    I allready have a junction view:
    ProdID, Date, In, Out
    1,01-09-2004, 20, 15
    1,02-09-2004, 25, 10
    1,03-09-2004, 16, 18
    1,04-09-2004, 11, 20

    In the end it should have a stock count of 9 with this layout:
    ProdID, Date, In, Out, InStock
    1,01-09-2004, 20, 15, 5
    1,02-09-2004, 25, 10, 20
    1,03-09-2004, 16, 18, 18
    1,04-09-2004, 11, 20, 9

    This might just sound like Homework, but let me assure you its not. Due to changes at the office i have had move from Access to Oracle, tho i am familiar and can manage most tasks (with some help of the forum and books) i cant seem to find how to work this.... In access its simple ... But now do it in Oracle

    Please help
    Last edited by namliam; 09-06-04 at 06:32.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Analytic functions are good for this kind of thing:
    Code:
    SQL> select prodid, stock_date, stock_in, stock_out
      2  ,      sum(stock_in-stock_out) over (partition by prodid order by stock_date) in_stock
      3  from   stock
      4  order by stock_date;
    
        PRODID STOCK_DATE    STOCK_IN  STOCK_OUT   IN_STOCK
    ---------- ----------- ---------- ---------- ----------
             1 01-SEP-2004         20         15          5
             1 02-SEP-2004         25         10         20
             1 03-SEP-2004         16         18         18
             1 04-SEP-2004         11         20          9

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    What is this "Over" part and or how does it work?

    Thanx will look into this...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    See Analytic Functions in the SQL Reference for more details.

    Code:
    sum(stock_in-stock_out) over (partition by prodid order by stock_date)
    This example gives the cumulative sum of (stock_in-stock_out) for each record, in order of stock_date, and within prodid (partition clause). i.e. the sum resets to 0 when the prodid changes from 1 to 2.

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Thanx a million, it works great!

    Now to integrate it... but that is up to me....

    I am pretty good with Access and with "Normal" sql in Oracle, once it gets down to the nitty gritty like this... I am stuck, I have a LOT to learn yet....

    Greetz

    P.S. Is there something like an Access turntable in Oracle? Without doing a multi-decode thing....??

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by namliam
    P.S. Is there something like an Access turntable in Oracle? Without doing a multi-decode thing....??
    You mean, to generate a "pivot" query like this?:
    Code:
    SQL> select deptno
      2  , sum(decode(job,'MANAGER',1,0)) managers
      3  , sum(decode(job,'CLERK',1,0)) clerks
      4  , sum(decode(job,'SALESMAN',1,0)) salesmen
      5  , sum(decode(job,'ANALYST',1,0)) analysts
      6  from emp
      7  group by deptno;
    
        DEPTNO   MANAGERS     CLERKS   SALESMEN   ANALYSTS
    ---------- ---------- ---------- ---------- ----------
            10          1          1          0          0
            20          1          2          0          2
            30          1          1          4          0
    No, you have to do the "multi-decode thing". Though of course you can use CASE instead of DECODE.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    BTW, I have previously posted the code for a package to generate pivot queries here For the example above you would run this:
    Code:
    SQL> exec pivot.print_pivot_query('DEPTNO','JOB','EMP',1)
    select DEPTNO
    ,      sum(DECODE(JOB,'ANALYST', 1)) as "ANALYST"
    ,      sum(DECODE(JOB,'CLERK', 1)) as "CLERK"
    ,      sum(DECODE(JOB,'MANAGER', 1)) as "MANAGER"
    ,      sum(DECODE(JOB,'SALESMAN', 1)) as "SALESMAN"
    from   EMP
    group by DEPTNO
    order by DEPTNO
    
    PL/SQL procedure successfully completed.
    Or:
    Code:
    SQL> var rc refcursor
    SQL> exec :rc := pivot.pivot_cursor('DEPTNO','JOB','EMP',1)
    
    PL/SQL procedure successfully completed.
    
    SQL> print rc
    
        DEPTNO    ANALYST      CLERK    MANAGER   SALESMAN
    ---------- ---------- ---------- ---------- ----------
            10                     1          1
            20          2          2          1
            30                     1          1          4

  8. #8
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Thanx,

    I guess i am still living the Access live

    I still am hoping to reach the same level in Oracle and/or (PL/)SQL as i have in Access and Excel with VBA....

    Thanx again for your help on this matter....

    The Mailman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •