Home » SQL & PL/SQL » SQL & PL/SQL » Help on Query - analytic? (10g)
Help on Query - analytic? [message #392836] Thu, 19 March 2009 08:10 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
I have these table:


drop table t;

create table t
(
    cob_dt  date,
    account varchar2(20),    
    pf_commodity  varchar2(30),    
    ote number
);

insert into t values( '16-mar-09', 'ACCT', 'COM1', 600 );
insert into t values( '16-mar-09', 'ACCT', 'COM1', -4500 );
insert into t values( '17-mar-09', 'ACCT', 'COM1', -470 );
insert into t values( '17-mar-09', 'ACCT', 'COM1', 500 );

select cob_dt,
       account,
       pf_commodity,
       ote,
       sum( ote ) over ( partition by cob_dt, account, pf_commodity ) s_ote
from t
;




Now I'm trying to get a sum of the OTE per cobdt, account, pfcommodity, then subtract that sum to the sum of the previous day. Problem is I want it to appear only once per partition, but its repeating. My desired output is below. Is there a command to just make it appear once?


cob_dt    account pf_commodity  ote   sumper

16-MAR-09	ACCT	COM1	600	    -3900
16-MAR-09	ACCT	COM1	-4500	  null
17-MAR-09	ACCT	COM1	-470	  -3870 -- the sum on mar17 is 30 minus previous day ( -3900 )
17-MAR-09	ACCT	COM1	500	    null




Thank you very much.

Regards,
Rhani
Re: Help on Query - analytic? [message #392850 is a reply to message #392836] Thu, 19 March 2009 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without an order in the rows you can't do your output.

If you want a line per day, then in an inline view makes the sum per day then you have 1 row per date.

In addition, we more than 400+ you should know what is a date:
SQL> create table t
  2  (
  3      cob_dt  date,
  4      account varchar2(20),    
  5      pf_commodity  varchar2(30),    
  6      ote number
  7  );

Table created.

SQL> 
SQL> insert into t values( '16-mar-09', 'ACCT', 'COM1', 600 );
insert into t values( '16-mar-09', 'ACCT', 'COM1', 600 )
                      *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Re: Help on Query - analytic? [message #392854 is a reply to message #392850] Thu, 19 March 2009 08:49 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks for the reply Michel. Sorry again for the wrong practice of date. I just do that for quicker creation of test data.

I didn't quite get what you said, you mean a query like this?

select ...
from table a,
( select sum(ote)
from table
where days...
)

I'll check on this if it would make the other rows null. Thanks again!
Re: Help on Query - analytic? [message #392858 is a reply to message #392854] Thu, 19 March 2009 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use SQL*Plus you can just do a "break on sumper";

Regards
Michel
Re: Help on Query - analytic? [message #392863 is a reply to message #392858] Thu, 19 March 2009 09:10 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks for that, but problem is I'm feeding the cursor to a reporting tool, ( pivot type ) that's why I need that one column have null on other rows, so that when it sums up that column per day it doesn't multiply the value of the difference per day. Thanks again.

Regards,
Rhani
Re: Help on Query - analytic? [message #392870 is a reply to message #392863] Thu, 19 March 2009 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select cob_dt,
  2         account,
  3         pf_commodity,
  4         ote,
  5         decode(row_number() over (partition by account, pf_commodity, cob_dt order by null),
  6                1, sum(ote) over (partition by account, pf_commodity order by cob_dt
  7                                  range between unbounded preceding 
  8                                    and numtodsinterval(86399,'SECOND') following))
  9           sum_ote
 10  from t
 11  /
COB_DT      ACCOUNT              PF_COMMODITY                          OTE    SUM_OTE
----------- -------------------- ------------------------------ ---------- ----------
16-MAR-0009 ACCT                 COM1                                  600      -3900
16-MAR-0009 ACCT                 COM1                                -4500
17-MAR-0009 ACCT                 COM1                                 -470      -3870
17-MAR-0009 ACCT                 COM1                                  500

4 rows selected.

Regards
Michel
Re: Help on Query - analytic? [message #392881 is a reply to message #392870] Thu, 19 March 2009 09:50 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you very much Michel! Are you a super saiyan or something ?? =)

I think this solves it, I'll check the reason for this:

range between unbounded preceding
8 and numtodsinterval(86399,'SECOND') following))

I'll check on what this does. Never thought "order by null" was possible =)


Thanks again!
Re: Help on Query - analytic? [message #392883 is a reply to message #392881] Thu, 19 March 2009 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that this works only because all your dates as no time, if they might have time some changes have to be made.

Quote:
range between unbounded preceding and numtodsinterval(86399,'SECOND') following))

Means take the sum for all rows before the current day and up to the end of the current day (assuming time is 00:00).

Quote:
Never thought "order by null" was possible

"order by" clause is mandatory; "order by null" means "I don't care of the order but I have to put it as the syntax impose it".

Regards
Michel
Re: Help on Query - analytic? [message #392889 is a reply to message #392883] Thu, 19 March 2009 10:13 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you for the explanation! I always thought I need to put a column on that order by =)

I'll check if this fix also the other cases =) Thanks again.


Now I'm confused ( need to learn math again! )

SQL> select  -- ( -8330 ) - ( 67780 ) c,
  2          ( 600 + ( -4500 ) )  ote16,
  3          ( -470 ) + 500 ote17,
  4          ( 30 ) - ( -3900 ) diff1,
  5          ( -3900 ) - ( 30) dif2
  6  from dual
  7  /

     OTE16      OTE17      DIFF1       DIF2
---------- ---------- ---------- ----------
     -3900         30       3930      -3930


Why does it say -3870 on resultset? The correct computation should be 3930.

[Updated on: Thu, 19 March 2009 10:37]

Report message to a moderator

Re: Help on Query - analytic? [message #392910 is a reply to message #392889] Thu, 19 March 2009 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
-3900 + 30 = -3870

But maybe the result you posted (cumulative sum) does not reflect the requirement you have.

What is the clear requirement?

Regards
Michel
Re: Help on Query - analytic? [message #392924 is a reply to message #392910] Thu, 19 March 2009 12:02 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks again It should be

sum17th - sum16th

( 30 ) - ( -3900 ) = 3930

Regards,
Rhani
Re: Help on Query - analytic? [message #392926 is a reply to message #392924] Thu, 19 March 2009 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if you add a third date, what should be the result of this one?

Regards
Michel
Re: Help on Query - analytic? [message #392927 is a reply to message #392926] Thu, 19 March 2009 12:13 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
It should be day - previous day.

In case of three days:

17th - 16th
18th - 17th

Thank you very much again.

Actually my current code works for those which only has 1 row per day, and just a usual ( current - lag() ) works ok. It's just that they split it now per day of records thats why it broke my code, thats why I had to set the other rows to null.


Played with what you gave Michel, looks like its showing something =)



SQL> select cob_dt, account, pf_commodity, ote, sum_ote, c, r
  2          , lag( c ) over ( partition by account, pf_commodity, r order by cob_dt ) l
  3          , nvl( ( c -  lag( c ) over ( partition by account, pf_commodity, r order by cob_dt ) ), c ) l2
  4  from (
  5  select x.cob_dt,
  6         x.account,
  7         x.pf_commodity,
  8         -- x.ote,
  9         x.ote,
 10         decode(row_number() over (partition by account, pf_commodity, cob_dt order by null),
 11                   1, sum(ote) over (partition by account, pf_commodity order by cob_dt
 12                                     range between unbounded preceding
 13                                       and numtodsinterval(86399,'SECOND') following))
 14              sum_ote
 15        ,decode(row_number() over (partition by cob_dt, account, pf_commodity, cob_dt order by null),
 16                   1, sum(ote) over (partition by cob_dt, account, pf_commodity order by cob_dt
 17                                     range between unbounded preceding
 18                                       and numtodsinterval(86399,'SECOND') following)) c
 19        ,row_number() over (partition by account, pf_commodity, cob_dt order by null) r
 20  from t x
 21  -- group by x.account, x.pf_commodity, rollup ( x.cob_dt )
 22  -- rollup ( x.cob_dt, x.account, x.pf_commodity )
 23  )
 24  ;

COB_DT    ACCOUNT              PF_COMMODITY                          OTE    SUM_OTE          C       R     L         L2
--------- -------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
16-MAR-09 ACCT                 COM1                                  600      -3900      -3900       1            -3900
17-MAR-09 ACCT                 COM1                                 -470      -3870         30       1      -3900          3930
16-MAR-09 ACCT                 COM1                                -4500                             2
17-MAR-09 ACCT                 COM1                                  500                             2


[Updated on: Thu, 19 March 2009 12:43]

Report message to a moderator

Re: Help on Query - analytic? [message #392936 is a reply to message #392927] Thu, 19 March 2009 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select cob_dt,
  4             account,
  5             pf_commodity,
  6             ote,
  7             row_number() over (partition by account, pf_commodity, cob_dt order by null) rn,
  8             sum(ote) over (partition by account, pf_commodity order by trunc(cob_dt)
  9                            range between current row and current row) sum_ote
 10      from t
 11    )
 12  select cob_dt,
 13         account,
 14         pf_commodity,
 15         ote,
 16         decode (rn, 
 17                 1, sum_ote 
 18                    - nvl(lag(sum_ote) 
 19                            over (partition by account, pf_commodity order by trunc(cob_dt)),
 20                          0)) diff
 21  from data
 22  order by 1, 2, 3, rn
 23  /
COB_DT      ACCOUNT              PF_COMMODITY                          OTE       DIFF
----------- -------------------- ------------------------------ ---------- ----------
16-MAR-0009 ACCT                 COM1                                  600      -3900
16-MAR-0009 ACCT                 COM1                                -4500
17-MAR-0009 ACCT                 COM1                                 -470       3930
17-MAR-0009 ACCT                 COM1                                  500

4 rows selected.

Regards
Michel
Re: Help on Query - analytic? [message #392937 is a reply to message #392936] Thu, 19 March 2009 13:17 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you very much again =)

Didn't know there was a "current row".
Previous Topic: Question is simple but answer is confusing
Next Topic: Storing table coumn name from source table into column value in target table
Goto Forum:
  


Current Time: Sat Dec 03 01:21:29 CST 2016

Total time taken to generate the page: 0.16232 seconds