| ORA-00907: missing right parenthesis [message #604899] |
Sun, 05 January 2014 23:46  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
I got ORA-00907: missing right parenthesis error in LEAD Function can you please suggest me?
select empno,comm,
lead(comm ignore nulls) over(order by empno)next_comm
from emp
order by empno
Thank you
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00907: missing right parenthesis [message #604911 is a reply to message #604903] |
Mon, 06 January 2014 01:54  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mist598 wrote on Mon, 06 January 2014 11:36 Can you please suggest how can i overcome this problem?
Since you are not in Oracle DB version 11gR2, you cannot use IGNORE NULLS option with LEAD. The workaround for this is to use a CASE construct.
Something like :
SELECT id,
2 sal ,
3 deptno ,
4 comm ,
5 CASE
6 WHEN comm IS NULL
7 THEN lead(comm) over(partition BY deptno order by id)
8 ELSE comm
9 END AS lead_comm
10 FROM t
11 /
|
|
|
|
|
|
| Re: ORA-00907: missing right parenthesis [message #604946 is a reply to message #604911] |
Mon, 06 January 2014 06:58 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Mon, 06 January 2014 02:54Something like :
??? It is not even close. Compare:
SQL> select empno,
2 comm,
3 lead(comm ignore nulls) over(order by empno) next_comm
4 from emp
5 order by empno
6 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 500
7521 500 1400
7566 1400
7654 1400 0
7698 0
7782 0
7788 0
7839 0
7844 0
7876
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7900
7902
7934
14 rows selected.
SQL> select empno,
2 comm,
3 case
4 when comm is null
5 then lead(comm) over(order by empno)
6 else comm
7 end next_comm
8 from emp
9 order by empno
10 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 300
7521 500 500
7566 1400
7654 1400 1400
7698
7782
7788
7839 0
7844 0 0
7876
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7900
7902
7934
14 rows selected.
Workaround is to use first_value:
SQL> select empno,
2 comm,
3 first_value(comm ignore nulls) over(order by empno rows between 1 following and unbounded following) next_comm
4 from emp
5 order by empno
6 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 500
7521 500 1400
7566 1400
7654 1400 0
7698 0
7782 0
7788 0
7839 0
7844 0
7876
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7900
7902
7934
14 rows selected.
SQL>
SY.
|
|
|
|
| Re: ORA-00907: missing right parenthesis [message #604950 is a reply to message #604946] |
Mon, 06 January 2014 07:57 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
In fact, I suggest not to use LEAD/LAG with IGNORE NULLS unless you are on 12C:
SQL> select banner
2 from v$version
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Elapsed: 00:00:00.01
SQL> drop table tbl purge
2 /
Table dropped.
Elapsed: 00:00:00.10
SQL> create table tbl
2 as
3 select level id,
4 case mod(level,10)
5 when 0 then 'NAME' || level
6 end name
7 from dual
8 connect by level < 10001
9 /
Table created.
Elapsed: 00:00:00.11
SQL> drop table tbl1 purge
2 /
Table dropped.
Elapsed: 00:00:00.07
SQL> create table tbl1
2 as
3 select id,
4 name,
5 first_value(name ignore nulls) over(order by id rows between 1 following and unbounded following) next_name
6 from tbl
7 /
Table created.
Elapsed: 00:00:01.45
SQL> drop table tbl1 purge
2 /
Table dropped.
Elapsed: 00:00:00.07
SQL> create table tbl1
2 as
3 select id,
4 name,
5 lead(name ignore nulls) over(order by id) next_name
6 from tbl
7 /
Table created.
Elapsed: 00:00:14.83 <-- Huge performance hit
SQL>
12C:
SQL> select banner
2 from v$version
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
Elapsed: 00:00:00.13
SQL> drop table tbl purge
2 /
Table dropped.
Elapsed: 00:00:00.06
SQL> create table tbl
2 as
3 select level id,
4 case mod(level,10)
5 when 0 then 'NAME' || level
6 end name
7 from dual
8 connect by level < 10001
9 /
Table created.
Elapsed: 00:00:00.17
SQL> drop table tbl1 purge
2 /
Table dropped.
Elapsed: 00:00:00.03
SQL> create table tbl1
2 as
3 select id,
4 name,
5 first_value(name ignore nulls) over(order by id rows between 1 following and unbounded following) next_name
6 from tbl
7 /
Table created.
Elapsed: 00:00:00.16
SQL> drop table tbl1 purge
2 /
Table dropped.
Elapsed: 00:00:00.07
SQL> create table tbl1
2 as
3 select id,
4 name,
5 lead(name ignore nulls) over(order by id) next_name
6 from tbl
7 /
Table created.
Elapsed: 00:00:00.05
SQL>
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
