Home » SQL & PL/SQL » SQL & PL/SQL » over partition by
| over partition by [message #667404] |
Wed, 27 December 2017 01:02  |
 |
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 #667409 is a reply to message #667404] |
Wed, 27 December 2017 02:14  |
 |
Michel Cadot
Messages: 68510
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 #667415 is a reply to message #667412] |
Wed, 27 December 2017 02:57 |
|
sshree12
Messages: 20
Registered: December 2017
|
Junior Member |
|
|
 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 #667420 is a reply to message #667416] |
Wed, 27 December 2017 06:32 |
|
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 #667422 is a reply to message #667420] |
Wed, 27 December 2017 06:57 |
 |
Littlefoot
Messages: 21783
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 30 13:47:54 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
