Home » SQL & PL/SQL » SQL & PL/SQL » Cant Find Missing parenthesis
Cant Find Missing parenthesis [message #196092] Wed, 04 October 2006 02:24 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
why am i getting this error? ive closed all the parenthesis..when i removed order by claused it worked fine, what could be the explanation for that? and why does it complains about the parenthesis?


SQL> select f.effective_start, f.fcl_rate
  2  from ft_wharfage_rate f
  3  where f.port_code = 'BCD'
  4   and f.cosi_code = '10FTR'
  5   and f.status = 'A'
  6   and f.effective_start = (select max(g.effective_start)
  7                          from ft_wharfage_rate g
  8                          where g.port_code = f.port_code
  9                            and g.cosi_code = f.cosi_code
 10                            and g.status = 'A'
 11                          order by g.effective_start desc)
 12  /

select f.effective_start, f.fcl_rate
from ft_wharfage_rate f
where f.port_code = 'BCD'
 and f.cosi_code = '10FTR'
 and f.status = 'A'
 and f.effective_start = (select max(g.effective_start)
                        from ft_wharfage_rate g
                        where g.port_code = f.port_code
                          and g.cosi_code = f.cosi_code
                          and g.status = 'A'
                        order by g.effective_start desc)

ORA-00907: missing right parenthesis

SQL> 

[Updated on: Wed, 04 October 2006 02:37]

Report message to a moderator

Re: Cant Find Missing parenthesis [message #196098 is a reply to message #196092] Wed, 04 October 2006 02:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When using the syntax WHERE col = (subquery), the col is compared to the entire subquery - not just the first row. For an equals (=) clause like this, it requires that only a single row is returned (more than one would cause a failure). But even if it was an IN clause, the order would be unimportant - col is either IN the sub-query or it isn't.

For this reason, sub-queries do not support the ORDER BY syntax. Frankly, there is no point.


Ross Leishman
Re: Cant Find Missing parenthesis [message #196099 is a reply to message #196092] Wed, 04 October 2006 02:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you don't need the order by at all, as it doesn't make any difference.

You can also rewrite the query as
SELECT * 
FROM   (select f.effective_start, f.fcl_rate
        from   ft_wharfage_rate f
        where  f.port_code = 'BCD'
        and    f.cosi_code = '10FTR'
        and    f.status = 'A'
        order by f.effective_start desc)
where rownum = 1;


But you're right - It doesn't seem to like the order by in the subquery. I get the error too.
Re: Cant Find Missing parenthesis [message #196105 is a reply to message #196099] Wed, 04 October 2006 03:02 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for the reply, also this observation, when i do this

select fcl_rate, effective_start
from ft_wharfage_rate
where port_code = 'BCD'
and cosi_code = '10FTR'
and status = 'A'
--and rownum = 1
order by effective_start desc


i get

27.00 8/22/2006 2:13:56 PM
27.00 5/9/2006 9:18:45 AM

and so on...

but when i uncomment the rownum = 1 i get a different record..

34.00 11/17/2005 5:08:25 PM

what could be the reason? thanks again =)



i need to stick to my query with max, dont know why this code wont work on reports


select fcl_rate 
  	into n_orig
  	from (select fcl_rate   	
			  	from ft_wharfage_rate 
					where port_code = :origin
					 and cosi_code = :cosi_code
					 and status = 'A'		 
					order by effective_start desc)
		where rownum = 1;

[Updated on: Wed, 04 October 2006 03:07]

Report message to a moderator

Re: Cant Find Missing parenthesis [message #196106 is a reply to message #196105] Wed, 04 October 2006 03:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Because the ROWNUM is applied before the ORDER BY. In your example, it is returning the first row that satisfies your WHERE clause, and then sorting it. Your (incorrect) expectation is that is finds ALL of the rows, sorts them, and returns the first one.

Do a search, this topic has been covered many, many times.

Ross Leishman
Re: Cant Find Missing parenthesis [message #196115 is a reply to message #196106] Wed, 04 October 2006 03:24 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for the explanation, can u tell what topic of sql is that? the precedence of the where clause order by and others? thanks again, also the report doesnt seem to recognize the select * from (select from).

THE error in the oracle reports, it doesnt seem to accept order by clause in select * from (select ...order by..) when i remove the order by it works fine, but the output will be wrong.

this link is ok too

http://www.orafaq.com/forum/m/191210/74539/?srch=rownum+order+by#msg_191210

[Updated on: Wed, 04 October 2006 04:06]

Report message to a moderator

Re: Cant Find Missing parenthesis [message #196165 is a reply to message #196092] Wed, 04 October 2006 06:46 Go to previous messageGo to next message
vijayanarayanan
Messages: 11
Registered: September 2006
Location: chennai
Junior Member
THE
select f.effective_start, f.fcl_rate
2 from ft_wharfage_rate f
3 where f.port_code = 'BCD'
4 and f.cosi_code = '10FTR'
5 and f.status = 'A'
6 and f.effective_start = (select max(g.effective_start)
7 from ft_wharfage_rate g
8 where g.port_code = f.port_code
9 and g.cosi_code = f.cosi_code
10 and g.status = 'A'
11 order by max(g.effective_start))
Re: Cant Find Missing parenthesis [message #196179 is a reply to message #196165] Wed, 04 October 2006 07:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@vijayanarayanan:

I'm guessing you didn't test your suggestion before you posted it.

SQL> create table temp_order (col_1  varchar2(10), col_2  varchar2(10));

Table created.

SQL> insert into temp_order (select mod(level,10), level from dual connect by level <= 100);

100 rows created.

SQL> select col_1,col_2
  2  from   temp_order t1
  3  where  col_2 = (select max(col_2)
  4                  from   temp_order t2
  5                  where  t1.col_1 = t2.col_1);

COL_1      COL_2
---------- ----------
0          90
1          91
2          92
3          93
4          94
5          95
6          96
7          97
8          98
9          99

10 rows selected.

SQL> 
SQL> select col_1,col_2
  2  from   temp_order t1
  3  where  col_2 = (select max(col_2)
  4                  from   temp_order t2
  5                  where  t1.col_1 = t2.col_1
  6                  order by max(col_2));
                order by max(col_2))
                *
ERROR at line 6:
ORA-00907: missing right parenthesis

Previous Topic: Outer Join Question
Next Topic: Urgent Help on Sql Query
Goto Forum:
  


Current Time: Thu Dec 08 12:14:17 CST 2016

Total time taken to generate the page: 0.05866 seconds