Thread: sql query

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    76
    Rep Power
    12

    sql query


    hi..i'm using the oracle db. The following sql quey can run in php(but returns the wrong result), but cannot run in oracle sql plus. Can someone tell me what's wrong?
    Thank you!
    Code:
    SQL> select distinct( acav_cardno ), acch_icno, acch_accesscode, acav_cardholdername from ac_cardholder, ac_activity where trunc(acav_trxdatetime) between to_date('01-11-2003','dd-mm-yyyy') and to_date('30-11-2003','dd-mm-yyyy') and acch_cardno = acav_cardno and acav_readertype='I' order by acch_icno;
      2  ;
    select distinct( acav_cardno ), acch_icno, acch_accesscode, acav_cardholdername from ac_cardholder, 
                                                                                                        
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Looks like you have two ; in your statement? Leave out the second one, or leave them out completely and try to terminate the query with / (in SQL*Plus)
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Posts
    76
    Rep Power
    12
    actually it cannot work. when i put first ";" and press enter, the oracle sql query seek waiting the input from me. so i put the second ";" and the error messages come out.
    Here I have try the simply sql. and it can work.
    select acav_cardno, acav_cardholdername, acch_icno, acch_accesscode from ac_cardholder, ac_activity where acch_cardno = acav_cardno and trunc(acav_trxdatetime) between to_date('01-11-2003', 'dd-mm-yyyy') and to_date('30-11-2003', 'dd-mm-yyyy');
    but when i try(as follow, i added the "acav_readertype = 'I'"), the error message come out said "ERROR at line 2:
    ORA-00936: missing expression"
    select acav_cardno, acav_cardholdername, acch_icno, acch_accesscode from ac_cardholder, ac_activity where acch_cardno = acav_cardno and acav_readertype = 'I' and trunc(acav_trxdatetime) between to_date('01-11-2003', 'dd-mm-yyyy') and to_date('30-11-2003', 'dd-mm-yyyy');
    hope that u can help me. Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo