ORA-00936 with Oracle script

[straygrey]

When run the following script returns an ORA-00936 missing expression error.

select  mem_number AS MemNumber,
        sum(trans_turnover) AS Turnover
from members
join transact
on mem_number = trans_code
where Trans_date between '01-Jan-2004' and '09-Oct-2008'
and mem_barred = 0
group by mem_number,
        order by turnover desc
)
where rownum <=200;

Please tell me what I have done wrong and how do I fix this.

[PAVB]

Two thing pop up...

1- You are missing a...

select *
from
(

...at the beginning of your script.

2- You want to do "where rownum < 201;"

[straygrey]

I do not understand how one would incorporate the select * from (
and I do not see too much difference between rownum < 201 and rownum <= 200

Please give me more detail.

[PAVB]

I do not understand how one would incorporate the select * from (
and I do not see too much difference between rownum < 201 and rownum <= 200

Please give me more detail.

Don't worry, I got there are a lot of things you are not understanding, try this

select *
from
(
select  mem_number AS MemNumber,
        sum(trans_turnover) AS Turnover
from members
join transact
on (mem_number = trans_code)
where Trans_date between '01-Jan-2004' and '09-Oct-2008'
and mem_barred = 0
group by mem_number,
order by turnover desc
)
where rownum < 201;

[straygrey]

I tried your suggestions and still get the same error message.

[PAVB]

Okay...

Would you please post DESC of both tables as well as complete sql*plus session showing the error?

[chrisrlong]

group by mem_number, <--- Remove this comma

[gandolf989]

group by mem_number, <--- Remove this comma

Chrisrlong, as usual, is correct.

[straygrey]

I have now got the code to look like

select * from 
(
mem_number AS MemNumber,
sum(trans_turnover) AS Turnover
from members
join transact
on mem_number = trans_code
where Trans_date between '01-Jan-2004' and '09-Oct-2008'
and mem_barred = 0
group by mem_number
order by turnover desc 
) 
where rownum <=200;

and now the error I get is ORA-00907: missing right parenthesis.
Help again please.

[straygrey]

Changing my script to:-

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 '01-Jan-2004' and '09-Oct-2008'
and mem_barred = 0
group by mem_number
order by turnover desc 
) 
where rownum <=200;

caused it to work. Thank you for all your suggestions.