?
Solved

PL/SQL, row_number () over (partition by ......)

Posted on 2006-05-12
25
Medium Priority
?
7,408 Views
Last Modified: 2012-06-27
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_2,emp_dt_2,
                             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_dt, row_number () over    (partition by emp_cd order by emp_dt desc)rn
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_dt
order by a.emp_cd,b.dep_num,a.emp_dt)base
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



0
Comment
Question by:oraram9
  • 8
  • 8
  • 6
  • +2
24 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16671500
>>ORA-06550: line #, column #:

    which line, which column causes the problem?
0
 

Author Comment

by:oraram9
ID: 16671534
below is the line causing the problem

(select distinct a.emp_cd,b.dep_num,a.emp_dt, row_number () over    (partition by emp_cd order by emp_dt desc)rn


The  " (  "just before partition by ......
 is causing the error.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16671588
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_2,emp_dt_2,
                             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_dt, row_number () over    (partition by emp_cd order by emp_dt desc)rn
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_dt
order by a.emp_cd,b.dep_num,a.emp_dt)base
group by base.emp_cd
order by base.emp_cd) a
where 1=0
;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:actonwang
ID: 16671605
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_2,emp_dt_2,
                             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_dt, row_number () over    (partition by emp_cd order by emp_dt desc)rn
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_dt
order by a.emp_cd,b.dep_num,a.emp_dt)base
group by base.emp_cd
order by base.emp_cd));
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16671622
sorry, my mistake. please ignore the above one and try the one before that.

Acton
0
 

Author Comment

by:oraram9
ID: 16671626
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
0
 

Author Comment

by:oraram9
ID: 16671654
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.......................
0
 

Author Comment

by:oraram9
ID: 16671685
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 ........
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16671838
Yes. I looked wrong. see my previous post ...
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16671844
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.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16671849
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.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16671853
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
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 16671869
It's impossible that your insert statement can run as an standalone DML statement. Take a closer look at it:


insert into temp_emp (emp_cd,dep_num_1,emp_dt_1,dep_num_2,emp_dt_2,
                             dep_num_3,emp_dt_3)
(                                                           ------------- <<----------- Error # 1  : You cannot open parenthesis here unless you use the VALUES keyword
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,       ------------- <<----------- Error # 2 : Missing comma (,) between 3 and dept_num
         max(decode(base.rn, 3 emp_dt, null)) emp_dt_3           ------------- <<----------- Error # 3 : Missing comma (,) betwwen 3 and emp_dt
from
(select distinct a.emp_cd,b.dep_num,a.emp_dt, row_number () over    (partition by emp_cd order by emp_dt desc)rn
from emp a , dep b
where a.emp_num = b.emp_num
group by a.emp_cd,b.dep_num,a.emp_dt
order by a.emp_cd,b.dep_num,a.emp_dt)base
group by base.emp_cd
order by base.emp_cd
)                                             ------------- <<----------- Error # 4 : If the opening parenthesis above is worng then this one is a no go as well
;


Try this statement:

 insert into temp_emp (emp_cd,dep_num_1,emp_dt_1,dep_num_2,emp_dt_2,
                              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_dt, row_number () over (partition by emp_cd order by emp_dt desc) rn
 from emp a , dep b
 where a.emp_num = b.emp_num
 group by a.emp_cd,b.dep_num,a.emp_dt
 order by a.emp_cd,b.dep_num,a.emp_dt)base
 group by base.emp_cd
 order by base.emp_cd
0
 

Author Comment

by:oraram9
ID: 16671885
I think that will fix the issue, thanks for the lead I'll try that and let you know how it goes.
0
 

Author Comment

by:oraram9
ID: 16671892
by the way the above comment is for actonwang
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16671904
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.
0
 

Author Comment

by:oraram9
ID: 16671916
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 :)


0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16671930
Do you have a select statement somewhere?
0
 

Author Comment

by:oraram9
ID: 16671935
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.

0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16671946
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
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16671953
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.  
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16672034
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.last loop
  7  dbms_output.put_line( tab1coll(i).col1 );
  8  end loop;
  9* end;
TEST@PROD>/
Alex
Frank
Alex

PL/SQL procedure successfully completed.


0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16682567
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;
/
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19743690
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question