[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.

[cis_groupie]

Why have you got a stray right parenthesis on the second to last line?

[straygrey]

Slip of the finger or was it slip of the brain?
Removing it however did not fix the problem. I am still getting ORA-00936.

[flyboy]

Do you have any idea, what this query shall do and what the current query really does? It shall be syntactically incorrect even after removing the mentioned parenthesis, as it is not allowed to have multiple WHERE clauses in one query.

If you want to get top 200 turnover rows, you miss the very first row, something like

select * from (

[beilstwh]

Try the following

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

[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 
) 

caused it to work. Thank you all especially beilstwh.

[Littlefoot]

Just beware of using "dates" the way you do:

where Trans_date between '01-Jan-2004' and '09-Oct-2008'

because 01-Jan-2004 looks like a date to you or me, but - as far as Oracle is concerned - it is just a character STRING (not a date).

The fact that your query runs well means nothing but that you have used format which is set to be a default date format on your database. For example, it would not run on my database:

SQL> select * from emp
  2  where hiredate between '01-jan-2004' and '09-oct-2008';
where hiredate between '01-jan-2004' and '09-oct-2008'
                       *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>

Therefore, you'd rather use the TO_DATE function and take control over dates. This is one of acceptable solutions:

... where trans_date between to_date('01.01.2004', 'dd.mm.yyyy') 
                         and to_date('09.10.2008', 'dd.mm.yyyy')

[straygrey]

I have hard coded the dates for test purposes but thank you for showing me how I should do it.