Home » SQL & PL/SQL » SQL & PL/SQL » over partition by
over partition by [message #667404] Wed, 27 December 2017 01:02 Go to next message
sshree12
Messages: 20
Registered: December 2017
Junior Member
Hi guys

create table test (PO varchar2(30), amount1 int,amount2 int,amount3 int, as_of_date Date)
;
with the following data:;



insert into test values ( 'a', 400, 100,50,to_date('02-JUN-2008'));

insert into test values ( 'a', 500, 200,100,to_date('02-AUG-2008'));

insert into test values ( 'a', 600, 300,150,to_date('09-SEP-2008'));
insert into test values ( 'b', 700, 400,200,to_date('10-OCT-2008'));

insert into test values ( 'b', 800, 500,300,to_date('12-NOV-2008'));

i wanted to add amount1+amount2+amount3 over partition by PO order by as_of_date but getting an error message

ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 3 Column: 19

[Updated on: Wed, 27 December 2017 01:06]

Report message to a moderator

Re: over partition by [message #667405 is a reply to message #667404] Wed, 27 December 2017 01:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is difficult to debug code you can't see; so, would you mind and post a query which results in that error?
Re: over partition by [message #667406 is a reply to message #667405] Wed, 27 December 2017 01:28 Go to previous messageGo to next message
sshree12
Messages: 20
Registered: December 2017
Junior Member
SELECT PO,AMOUNT1+AMOUNT2+AMOUNT3 OVER (PARTITION BY PO) FROM TEST;

ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error at Line: 18 Column: 40
Re: over partition by [message #667407 is a reply to message #667406] Wed, 27 December 2017 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"OVER (PARTITION BY)" part suggests that you want to use analytic function; well, syntax is wrong. Have a look at this article, it contains nice examples.

What result do you expect, based on sample data you posted?
Re: over partition by [message #667408 is a reply to message #667407] Wed, 27 December 2017 01:47 Go to previous messageGo to next message
sshree12
Messages: 20
Registered: December 2017
Junior Member
SELECT PO,SUM(AMOUNT1+AMOUNT2+AMOUNT3) OVER (PARTITION BY PO)*365/'09-SEP-2008'-'31-DEC-2008' FROM TEST;

i am expecting to calculate sum *365 divided by no of days in that year...unable to do so
Re: over partition by [message #667409 is a reply to message #667404] Wed, 27 December 2017 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why do you refuse to follow the rules we pointed you to in your topic yesterday?
Why do you refuse to post your Oracle version we asked you in your topic yesterday?
Why do you refuse to format your code we repeatedly asked you in your topic yesterday?
Why do you refuse to post a correct test case we repeatedly asked you in your topic yesterday?


SQL> create table test (PO varchar2(30), amount1 int,amount2 int,amount3 int, as_of_date Date);

Table created.

SQL> insert into test values ( 'a', 400, 100,50,to_date('02-JUN-2008'));
insert into test values ( 'a', 400, 100,50,to_date('02-JUN-2008'))
                                                   *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

[Updated on: Wed, 27 December 2017 02:15]

Report message to a moderator

Re: over partition by [message #667411 is a reply to message #667409] Wed, 27 December 2017 02:30 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That looks like an ordinary SUM, not its analytic form; though, you'd probably want to "GROUP BY test.po".

As of "days" component:
  • why 365? What about leap years?
  • why did you subtract 09-sep-2008 from 31-dec-2008? This is date in the third INSERT INTO statement. Do you need MAX date per test.po, or what?
Re: over partition by [message #667412 is a reply to message #667411] Wed, 27 December 2017 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you really expect some consistent or accurate answer from him when you had none yesterday? Wink

Re: over partition by [message #667414 is a reply to message #667412] Wed, 27 December 2017 02:55 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Honestly, no.
Re: over partition by [message #667415 is a reply to message #667412] Wed, 27 December 2017 02:57 Go to previous messageGo to next message
sshree12
Messages: 20
Registered: December 2017
Junior Member
Smile hope for the best..

dear even to_date function works in sql developer for inserting records..never mind the query is corrected

insert into test values ( 'a', 400, 100,50,('02-JUN-2008'));

insert into test values ( 'a', 500, 200,100,('02-AUG-2008'));

insert into test values ( 'a', 600, 300,150,('09-SEP-2008'));
insert into test values ( 'b', 700, 400,200,('10-OCT-2008'));

insert into test values ( 'b', 800, 500,300,('12-NOV-2008'));

this is the calculation required irrespective of leap year or fiscal. I am able to do sum of amount over partition by but divsion

SELECT PO,SUM(AMOUNT1+AMOUNT2+AMOUNT3) OVER (PARTITION BY PO)*365/'09-SEP-2008'-'31-DEC-2008' from test
getting error
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:

[Updated on: Wed, 27 December 2017 03:08]

Report message to a moderator

Re: over partition by [message #667416 is a reply to message #667415] Wed, 27 December 2017 03:41 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
INSERTs are even worse than before.

As of "invalid number": what do you expect, when subtracting two strings? What is "sshr - ee12", in your opinion? The same goes for "dates" you use - those are strings, you "forgot" TO_DATE function AND appropriate format mask AND NLS information.
Re: over partition by [message #667420 is a reply to message #667416] Wed, 27 December 2017 06:32 Go to previous messageGo to next message
sshree12
Messages: 20
Registered: December 2017
Junior Member

why you are behaving like this?this insert statement is working fine and i do not want to consider leap year
insert into test values ( 'a', 400, 100,50,('02-JUN-2008'));

insert into test values ( 'a', 500, 200,100,('02-AUG-2008'));

insert into test values ( 'a', 600, 300,150,('09-SEP-2008'));
insert into test values ( 'b', 700, 400,200,('10-OCT-2008'));

insert into test values ( 'b', 800, 500,300,('12-NOV-2008'));

SELECT PO,SUM(AMOUNT1+AMOUNT2+AMOUNT3) OVER (PARTITION BY PO)/'09-SEP-2008'-'31-DEC-2008' FROM TEST;

can you at least help me with the logic that how can we divide 2 values in a same statements

thanks
Re: over partition by [message #667421 is a reply to message #667420] Wed, 27 December 2017 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why are you behaving like this:

Michel Cadot wrote on Wed, 27 December 2017 09:14

Why do you refuse to follow the rules we pointed you to in your topic yesterday?
Why do you refuse to post your Oracle version we asked you in your topic yesterday?
Why do you refuse to format your code we repeatedly asked you in your topic yesterday?
Why do you refuse to post a correct test case we repeatedly asked you in your topic yesterday?
Re: over partition by [message #667422 is a reply to message #667420] Wed, 27 December 2017 06:57 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, I wonder the same, why are YOU behaving like this?

sshree12

this insert statement is working fine
SQL> create table test (PO varchar2(30), amount1 int,amount2 int,amount3 int, as_of_date Date);

Table created.

SQL> insert into test values ( 'a', 400, 100,50,('02-JUN-2008'));
insert into test values ( 'a', 400, 100,50,('02-JUN-2008'))
                                            *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>

No, it is not working fine.


Subtract dates:
SQL> select to_date('31.12.2008', 'dd.mm.yyyy') - to_date('09.09.2008', 'dd.mm.yyyy') days
  2  from dual;

      DAYS
----------
       113


Dividing:
SQL> select deptno, sum(sal) over (partition by deptno) /
  2    (to_date('31.12.2008', 'dd.mm.yyyy') - to_date('09.09.2008', 'dd.mm.yyyy')) result
  3  from emp;

    DEPTNO     RESULT
---------- ----------
        10 77,4336283
        10 77,4336283
        10 77,4336283

Re: over partition by [message #667427 is a reply to message #667422] Wed, 27 December 2017 09:02 Go to previous message
sshree12
Messages: 20
Registered: December 2017
Junior Member
Thank you very much
Previous Topic: Open cursor
Next Topic: current date - preovious date calculations
Goto Forum:
  


Current Time: Thu Mar 28 04:54:46 CDT 2024