Cant Find Missing parenthesis [message #196092] |
Wed, 04 October 2006 02:24  |
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   |
rleishman
Messages: 3728 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   |
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   |
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   |
rleishman
Messages: 3728 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 #196165 is a reply to message #196092] |
Wed, 04 October 2006 06:46   |
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  |
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
|
|
|