Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

    Unanswered: ORA-00936 with Oracle script

    When run the following script returns an ORA-00936 missing expression error.
    Code:
    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.

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Why have you got a stray right parenthesis on the second to last line?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

    ORA-00936 with Oracle script

    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.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    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
    Code:
    select * from (

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try the following

    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 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;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22

    ORA-00936 with Oracle script

    I have now got the code to look like
    Code:
    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.

  7. #7
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22
    Changing my script to:-
    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 '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.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just beware of using "dates" the way you do:
    Code:
    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:
    Code:
    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:
    Code:
    ... where trans_date between to_date('01.01.2004', 'dd.mm.yyyy') 
                             and to_date('09.10.2008', 'dd.mm.yyyy')

  9. #9
    Join Date
    Oct 2008
    Location
    Midrand, South Africa
    Posts
    22
    I have hard coded the dates for test purposes but thank you for showing me how I should do it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •