1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00936 with Oracle script

Discussion in 'SQL PL/SQL' started by straygrey, Oct 11, 2008.

  1. straygrey

    straygrey Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    When run the following script returns an ORA-00936 missing expression error

    Code (Text):
    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. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    There is an extra paranthesis in second last line
     
  3. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    This one is wrong.
     
  4. bhushanpandharkar

    bhushanpandharkar Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    I guess this is query for getting first 200(top 200) rows.
    according to me,just give a try for following -

    Code (Text):
    select  MemNumber,Turnover 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 <=200;
     
    Cheers,
    Bhushan
     
  5. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    '01-Jan-2004' is not a date , it is a string and hence above query will most possibly return date format error. I assume after naming column name Trans_date as a date not a char or varchar2.
     
  6. straygrey

    straygrey Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Changing my script to:-

    Code (Text):

    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 bhushan
     
  7. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Here is the test without using to_date as well as using to_date conversion.
    You have to do to_date comnversion.
    Code (Text):
    SQL> alter session set nls_date_format='YY MM DD';

    Session altered.

    SQL> 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
    )  2    3    4    5    6    7    8    9   10   11   12   13  ;
    where Trans_date between '01-Jan-2004' and '09-Oct-2008'
                             *
    ERROR at line 9:
    ORA-01858: a non-numeric character was found where a numeric was expected


    SQL> alter session set nls_date_format='DD MM YY';

    Session altered.

    SQL> 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
    )  2    3    4    5    6    7    8    9   10   11   12   13  ;

    no rows selected

    SQL> 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','DD-MON-YYYY') and to_date('09-Oct-2008','DD-MON-YYYY')
    and mem_barred = 0
    group by mem_number
    order by turnover desc
    )   2    3    4    5    6    7    8    9   10   11   12   13  ;

    no rows selected

    SQL> alter session set nls_date_format='YY MM DD';

    Session altered.

    SQL> 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','DD-MON-YYYY') and to_date('09-Oct-2008','DD-MON-YYYY')
    and mem_barred = 0
    group by mem_number
    order by turnover desc
    )   2    3    4    5    6    7    8    9   10   11   12   13
     14  ;

    no rows selected
     
    So always the safer way is to use of to_date conversion.
    By the way query are not tuned.

    And I think a new section need to be opened performance tuning.