Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Problem Passing value to LIKE in SQl query

    I have a problem executing below query. Showing ORA-01722: invalid number 01722.00000-"invalid number"

    QUERY:

    SELECT a.id JOBID ,
    a.resolveddatetime RESOLVEDDATE,
    b.city CITY ,
    b.managerdate MANAGERDATE ,
    b.unit ,
    b.checkinfo TYPE ,
    c.key
    FROM schema1.a_b_test a ,
    schema1.a_b_sample b,
    schema1.newtest c
    WHERE a.resolveddatetime > TO_DATE('20101001','yyyy/mm/dd')
    AND b.city ='Tokyo'
    AND a.id = b.id
    AND c.note LIKE '%simple text%'
    and c.key like '%'+ a.id '+%'
    ;


    Its Urgent. pls help !!

    Thanks,
    Sirisha J

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Please post structures of all three tables used in the query (output of: DESC tablename).

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    + is used to add numbers.

    If you want to concatenate character values you have to use ||:

    Code:
    and c.key like '%' || a.id || '%'
    And please read: http://www.dbforums.com/oracle/10316...s-posters.html

    Especially the part about using [code] tags to make your SQL readable.

  4. #4
    Join Date
    Apr 2012
    Posts
    3
    Problem is at last statement before ;
    If I give direct Id value the query executes well.



    SELECT a.id JOBID ,
    a.resolveddatetime RESOLVEDDATE,
    b.city CITY ,
    b.managerdate MANAGERDATE ,
    b.unit ,
    b.checkinfo TYPE ,
    c.key
    FROM schema1.a_b_test a ,
    schema1.a_b_sample b,
    schema1.newtest c
    WHERE a.resolveddatetime > TO_DATE('20101001','yyyy/mm/dd')
    AND b.city ='Tokyo'
    AND a.id = b.id
    AND c.note LIKE '%simple text%'
    and c.key like '%A-6%'
    ;

  5. #5
    Join Date
    Apr 2012
    Posts
    3
    Quote Originally Posted by shammat View Post
    + is used to add numbers.

    If you want to concatenate character values you have to use ||:

    Code:
    and c.key like '%' || a.id || '%'
    And please read: http://www.dbforums.com/oracle/10316...s-posters.html

    Especially the part about using [code] tags to make your SQL readable.
    This worked !!!! Thank you Shammat !!

Posting Permissions

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