0 Replies Latest reply: Sep 3, 2010 12:16 PM by 761308 RSS

    issue with LOWER function in JPQL

    761308
      hi all,

      i need your all on an issue that i ran into using LOWER function in JPQL. Basically i have a named query something like
      @NamedQuery(name="user_userByName", query="select u from User u  where  lower(u.vcusername)=?1
      {code}
      
      I was hoping that if i give the username value as 'tom' (all lower case) and in the data base if there is a record with a user name 'Tom'  then LOWER(vcusername) function will convert the username in the data base to all lower case and will find a matching record for the query.
      
      But the result is " no mathing records" . when i see the logs the sql query that gets generated is 
      
      
      {code}   SELECT t0.iusermetaid, t1.iaddressid, t0.vcdepartment, t0.vcemail, t0.vcfirstname, t0.vcjobtype, t0.vclastname, t0.vcpassword, t0.vcprefix, t0.vctitle, t0.vcusername FROM USERMETA t0, Address t1, userrole t2 WHERE t0.vcusername = ? AND t0.iaddressid = t1.iaddressid(+) AND t0.iusermetaid = t2.iusermetaid(+) [params=(String) tom  (long) 0]
      {code}
      
      I am not sure why lower(vcusername) is  ignored when i the actual query is generated. Can someone please shed some light.
      
      Thank you for your great help.
      
      Edited by: twisai on Sep 3, 2010 10:15 AM