oraram9
asked on
PL/SQL, row_number () over (partition by ......)
I have a scprit (Oracle 8i), and part of it looks like this
--test.sql
SET SERVEROUTPUT ON
BEGIN
.............
............
............
insert into temp_emp (emp_cd,dep_num_1,emp_dt_1
dep_num_3,emp_dt_3)
(select base.emp_cd,
max(decode(base.rn, 1, dep_num, null)) dep_num_1,
max(decode(base.rn, 1, emp_dt, null)) emp_dt_1,
max(decode(base.rn, 2, dep_num, null)) dep_num2,
max(decode(base.rn, 2, emp_dt, null)) emp_dt_2,
max(decode(base.rn, 3 dep_num, null)) dep_num3,
max(decode(base.rn, 3 emp_dt, null)) emp_dt_3
from
(select distinct a.emp_cd,b.dep_num,a.emp_d
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_d
order by a.emp_cd,b.dep_num,a.emp_d
group by base.emp_cd
order by base.emp_cd);
COMMIT;
..........................
..........................
..........................
END;
/
when I run the above script , I get the following error
ORA-06550: line #, column #:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from into bulk
the above works fine when I run it as a SQL statement, but doesn't work in the script,is there any work around for it???
Thanks in advance
--test.sql
SET SERVEROUTPUT ON
BEGIN
.............
............
............
insert into temp_emp (emp_cd,dep_num_1,emp_dt_1
dep_num_3,emp_dt_3)
(select base.emp_cd,
max(decode(base.rn, 1, dep_num, null)) dep_num_1,
max(decode(base.rn, 1, emp_dt, null)) emp_dt_1,
max(decode(base.rn, 2, dep_num, null)) dep_num2,
max(decode(base.rn, 2, emp_dt, null)) emp_dt_2,
max(decode(base.rn, 3 dep_num, null)) dep_num3,
max(decode(base.rn, 3 emp_dt, null)) emp_dt_3
from
(select distinct a.emp_cd,b.dep_num,a.emp_d
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_d
order by a.emp_cd,b.dep_num,a.emp_d
group by base.emp_cd
order by base.emp_cd);
COMMIT;
..........................
..........................
..........................
END;
/
when I run the above script , I get the following error
ORA-06550: line #, column #:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from into bulk
the above works fine when I run it as a SQL statement, but doesn't work in the script,is there any work around for it???
Thanks in advance
ASKER
below is the line causing the problem
(select distinct a.emp_cd,b.dep_num,a.emp_d
The " ( "just before partition by ......
is causing the error.
(select distinct a.emp_cd,b.dep_num,a.emp_d
The " ( "just before partition by ......
is causing the error.
could you just put this into the script to see how it goes:
insert into temp_emp (emp_cd,dep_num_1,emp_dt_1
dep_num_3,emp_dt_3)
(select base.emp_cd,
max(decode(base.rn, 1, dep_num, null)) dep_num_1,
max(decode(base.rn, 1, emp_dt, null)) emp_dt_1,
max(decode(base.rn, 2, dep_num, null)) dep_num2,
max(decode(base.rn, 2, emp_dt, null)) emp_dt_2,
max(decode(base.rn, 3 dep_num, null)) dep_num3,
max(decode(base.rn, 3 emp_dt, null)) emp_dt_3
from
(select distinct a.emp_cd,b.dep_num,a.emp_d
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_d
order by a.emp_cd,b.dep_num,a.emp_d
group by base.emp_cd
order by base.emp_cd) a
where 1=0
;
insert into temp_emp (emp_cd,dep_num_1,emp_dt_1
dep_num_3,emp_dt_3)
(select base.emp_cd,
max(decode(base.rn, 1, dep_num, null)) dep_num_1,
max(decode(base.rn, 1, emp_dt, null)) emp_dt_1,
max(decode(base.rn, 2, dep_num, null)) dep_num2,
max(decode(base.rn, 2, emp_dt, null)) emp_dt_2,
max(decode(base.rn, 3 dep_num, null)) dep_num3,
max(decode(base.rn, 3 emp_dt, null)) emp_dt_3
from
(select distinct a.emp_cd,b.dep_num,a.emp_d
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_d
order by a.emp_cd,b.dep_num,a.emp_d
group by base.emp_cd
order by base.emp_cd) a
where 1=0
;
HOLD ON, in your script :
you seem to miss a right parathensis ")" in the end :
it needs to be (in the end I added a ")"):
insert into temp_emp (emp_cd,dep_num_1,emp_dt_1
dep_num_3,emp_dt_3)
(select base.emp_cd,
max(decode(base.rn, 1, dep_num, null)) dep_num_1,
max(decode(base.rn, 1, emp_dt, null)) emp_dt_1,
max(decode(base.rn, 2, dep_num, null)) dep_num2,
max(decode(base.rn, 2, emp_dt, null)) emp_dt_2,
max(decode(base.rn, 3 dep_num, null)) dep_num3,
max(decode(base.rn, 3 emp_dt, null)) emp_dt_3
from
(select distinct a.emp_cd,b.dep_num,a.emp_d
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_d
order by a.emp_cd,b.dep_num,a.emp_d
group by base.emp_cd
order by base.emp_cd));
you seem to miss a right parathensis ")" in the end :
it needs to be (in the end I added a ")"):
insert into temp_emp (emp_cd,dep_num_1,emp_dt_1
dep_num_3,emp_dt_3)
(select base.emp_cd,
max(decode(base.rn, 1, dep_num, null)) dep_num_1,
max(decode(base.rn, 1, emp_dt, null)) emp_dt_1,
max(decode(base.rn, 2, dep_num, null)) dep_num2,
max(decode(base.rn, 2, emp_dt, null)) emp_dt_2,
max(decode(base.rn, 3 dep_num, null)) dep_num3,
max(decode(base.rn, 3 emp_dt, null)) emp_dt_3
from
(select distinct a.emp_cd,b.dep_num,a.emp_d
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_d
order by a.emp_cd,b.dep_num,a.emp_d
group by base.emp_cd
order by base.emp_cd));
sorry, my mistake. please ignore the above one and try the one before that.
Acton
Acton
ASKER
I can try that but I have a question,
order by base.emp_cd) a <------- why are we giving an alias here??
where 1=0 <----------- ???
;
just trying to understand..
anyways I'll try it and let you know how it goes
order by base.emp_cd) a <------- why are we giving an alias here??
where 1=0 <----------- ???
;
just trying to understand..
anyways I'll try it and let you know how it goes
ASKER
I tried to the script with an ")" added to the end
and got the following error
row_number() over (partition by itm_cd order by ent_dt desc)rn
ERROR at line #:
ORA-06550: line 76, column 28:
PLS-00103: Encountered the symbol "(" when expecting one of the following: , from
again its the "(" before partition by.......................
and got the following error
row_number() over (partition by itm_cd order by ent_dt desc)rn
ERROR at line #:
ORA-06550: line 76, column 28:
PLS-00103: Encountered the symbol "(" when expecting one of the following: , from
again its the "(" before partition by.......................
ASKER
I double checked the parathensis and I'm not missing any,
as I mentioned before I'm able to run the same as SQL query.
I was under the impression that ,
PL/SQL doesn't like row_number() over (partition ........
as I mentioned before I'm able to run the same as SQL query.
I was under the impression that ,
PL/SQL doesn't like row_number() over (partition ........
Yes. I looked wrong. see my previous post ...
Did you run it as PL/SQL block? Oh, I missed that, I got your problem. If you want to issue "Select ... " directly, you have to use :
Select .. into ...
or
Select ... bulk collect into ...
syntax.
That is why you got such error message.
Select .. into ...
or
Select ... bulk collect into ...
syntax.
That is why you got such error message.
you could issue the following simple test:
begin
select * from dual;
end;
/
you will get the same error.
you have to change it as:
declare
a varchar2(10);
begin
select * into a from dual;
end;
/
now you are ok.
begin
select * from dual;
end;
/
you will get the same error.
you have to change it as:
declare
a varchar2(10);
begin
select * into a from dual;
end;
/
now you are ok.
I guess that you intend to print out all results from PL/SQL block by issuing "set serveroutput on". Actually it will not.
Remember that PL/SQL block runs in server side as store procedure does. It will not give your output for select statment unless you use DBMS_OUPUT in general.
So you'd have to do :
1. use "select ... bulk collect into ..." (you have >=1 records) and show all records using DBMS_OUTPUT
or
2. use cursor to loop through all records.
Hope it clarifies everything for you.
Acton
Remember that PL/SQL block runs in server side as store procedure does. It will not give your output for select statment unless you use DBMS_OUPUT in general.
So you'd have to do :
1. use "select ... bulk collect into ..." (you have >=1 records) and show all records using DBMS_OUTPUT
or
2. use cursor to loop through all records.
Hope it clarifies everything for you.
Acton
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think that will fix the issue, thanks for the lead I'll try that and let you know how it goes.
ASKER
by the way the above comment is for actonwang
No problem. You can try anything you like but I can guarantee you that decodes like this one "max(decode(base.rn, 3 dep_num, null)) dep_num3," won't work for the next 100 years.
ASKER
paquicuba ,
I agree with you, but I just simulated my issue.(table names columns etc )
I didn't copy and paste my original script.
again in my original script the above part of script worked fine when I ran it as sql statement.
I think actonwang is right it is something to do with select ...bulk collect into..
anyway, Thank you for your suggestions :)
I agree with you, but I just simulated my issue.(table names columns etc )
I didn't copy and paste my original script.
again in my original script the above part of script worked fine when I ran it as sql statement.
I think actonwang is right it is something to do with select ...bulk collect into..
anyway, Thank you for your suggestions :)
Do you have a select statement somewhere?
ASKER
paquicuba ,
"max(decode(base.rn, 3 dep_num, null)) dep_num3," won't work for the next 100 years.
in reference to the above..
it works( I just tried it) but not as a PL/SQL block.
"max(decode(base.rn, 3 dep_num, null)) dep_num3," won't work for the next 100 years.
in reference to the above..
it works( I just tried it) but not as a PL/SQL block.
Can you post the output, see this simple example:
SQL > select decode(dummy,3 dummy,null) from dual;
select decode(dummy,3 dummy,null) from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL > select decode(dummy,3 dummy,null) from dual;
select decode(dummy,3 dummy,null) from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
The only way something like that would work is enclosing the whole expression with double-quotes:
TEST@PROD>create table mytab( "3 dep_num" number, dep_num number);
Table created.
Elapsed: 00:00:00.39
TEST@PROD>insert into mytab values( 10,10);
1 row created.
Elapsed: 00:00:00.00
TEST@PROD>select decode(10,3 dep_num,null) from mytab;
select decode(10,3 dep_num,null) from mytab
*
ERROR at line 1:
ORA-00907: missing right parenthesis
Elapsed: 00:00:00.01
TEST@PROD>select decode(10,"3 dep_num",null) from mytab;
D
-
1 row selected.
TEST@PROD>create table mytab( "3 dep_num" number, dep_num number);
Table created.
Elapsed: 00:00:00.39
TEST@PROD>insert into mytab values( 10,10);
1 row created.
Elapsed: 00:00:00.00
TEST@PROD>select decode(10,3 dep_num,null) from mytab;
select decode(10,3 dep_num,null) from mytab
*
ERROR at line 1:
ORA-00907: missing right parenthesis
Elapsed: 00:00:00.01
TEST@PROD>select decode(10,"3 dep_num",null) from mytab;
D
-
1 row selected.
My friend, getting that "from into bulk" as part of the error message doesn't mean that you're missing it. SQL*Plus is just giving you an option to correct the syntax error.
You would use a "Bulk Collect Into" in a case like this:
1 declare
2 type mytab is table of tab1%rowtype index by binary_integer;
3 tab1coll mytab;
4 begin
5 select col1 bulk collect into tab1coll from tab1;
6 for i in tab1coll.first..tab1coll.l
7 dbms_output.put_line( tab1coll(i).col1 );
8 end loop;
9* end;
TEST@PROD>/
Alex
Frank
Alex
PL/SQL procedure successfully completed.
You would use a "Bulk Collect Into" in a case like this:
1 declare
2 type mytab is table of tab1%rowtype index by binary_integer;
3 tab1coll mytab;
4 begin
5 select col1 bulk collect into tab1coll from tab1;
6 for i in tab1coll.first..tab1coll.l
7 dbms_output.put_line( tab1coll(i).col1 );
8 end loop;
9* end;
TEST@PROD>/
Alex
Frank
Alex
PL/SQL procedure successfully completed.
Not sure what version the problem was fixed in but there was a bug in the PL/SQL engine using data warehousing funcitons that generated this error.
The work-around was to place the SQL in a string and use execute immediate.
declare
mySQL varchar2(4000) := ' insert into ... row_number () over () ... ';
begin
...
execute immediate mySQL;
...
end;
/
The work-around was to place the SQL in a string and use execute immediate.
declare
mySQL varchar2(4000) := ' insert into ... row_number () over () ... ';
begin
...
execute immediate mySQL;
...
end;
/
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup Zone:
Accept paquicuba(16671869)
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
JimBrandley
Experts Exchange Cleanup Volunteer
I will leave the following recommendation for this question in the Cleanup Zone:
Accept paquicuba(16671869)
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
JimBrandley
Experts Exchange Cleanup Volunteer
which line, which column causes the problem?