ORA-00936 missing expression
-
When run the following script returns an ORA-00936 missing expression error. [Code] select MemNumber, Turnover from ( select mem_number MemNumber, sum(trans_turnover) Turnover from members join transact on mem_number = trans_code where Trans_date between '&Start' and '&End' and mem_barred = 0 group by mem_number order by turnover desc ) where rownum <=200 / [/code] Please tell me how I should fix it.
Regards, Alf Stockton
-
When run the following script returns an ORA-00936 missing expression error. [Code] select MemNumber, Turnover from ( select mem_number MemNumber, sum(trans_turnover) Turnover from members join transact on mem_number = trans_code where Trans_date between '&Start' and '&End' and mem_barred = 0 group by mem_number order by turnover desc ) where rownum <=200 / [/code] Please tell me how I should fix it.
Regards, Alf Stockton
-
[Code] where rownum <=200 [/code] Your where clause references a "rownum" column... Your select statement does not produce a column named "rownum"...
hth Al
-
When run the following script returns an ORA-00936 missing expression error. [Code] select MemNumber, Turnover from ( select mem_number MemNumber, sum(trans_turnover) Turnover from members join transact on mem_number = trans_code where Trans_date between '&Start' and '&End' and mem_barred = 0 group by mem_number order by turnover desc ) where rownum <=200 / [/code] Please tell me how I should fix it.
Regards, Alf Stockton
If you're running this in SQL*Plus or a program based on that, I would guess that the problem is in & character. This is normally reserved for prompting values so your statement may evaluate to
where Trans_date between '' and ''
Also you 'should' provide an alias for sub select in FROM. If you're using tools mentioned above, they should show you the line where the problem is.
The need to optimize rises from a bad design
-
[Code] where rownum <=200 [/code] Your where clause references a "rownum" column... Your select statement does not produce a column named "rownum"...
hth Al
-
rownum is a pseudocolumn so it's created automatically and defining it in the inner select would change the logic of the statement.
The need to optimize rises from a bad design
-
Good catch, try asking tom.... http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html[^]
hth Al
Didn't quite understand your point? There's a good example in the article:
select *
from
(select *
from t
order by id)
where rownum <= 10which returns top 10 rows. Rownum is placed on the outer query and the inner query selects only columns from the table t. Rownum is used on the ordered result set to ensure that really top 10 rows based on id column are returned. I understood that the original query in this question was doing top N fetch.
The need to optimize rises from a bad design
-
Didn't quite understand your point? There's a good example in the article:
select *
from
(select *
from t
order by id)
where rownum <= 10which returns top 10 rows. Rownum is placed on the outer query and the inner query selects only columns from the table t. Rownum is used on the ordered result set to ensure that really top 10 rows based on id column are returned. I understood that the original query in this question was doing top N fetch.
The need to optimize rises from a bad design
-
I know that's why I said good catch... when I saw the rownum I was thinking of it as ( SELECT col1, col2,ROW_NUMBER() OVER (PARTITION BY col1_id ORDER BY col1) AS rownum FROM employee ) where rownum < 200 My bad..
hth Al