Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Using 'Select.. Where... LIKE'

    Running DB2 on Windows V 9.5

    using this SQL
    Select * from schema.table where columnname like '%2010-10-01%'

    Getting this error
    42884(-440)[IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "LIKE" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
    (0.20 secs)

    Is my Syntax wrong???

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    If columnname has type DATE or TIMESTAMP you can't use LIKE.

    You have to use LIKE only for type CHAR and VARCHAR.

    For type DATE or TIMESTAMP operation could be =, >=, <=, >, <, BETWEEN.

    Lenny

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    Thank you!!!! Between will work !

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb CHAR(columnname) like

    You can also use for any types:

    Code:
    Select * from schema.table 
    where CHAR(columnname) like '%2010-10-01%'
    But it could also badly affect performance of the query.

    Lenny

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    Awesome! That worked beautifully!

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Next step to understanding

    Also, you don't need to use first "%" in the statement:

    Code:
    Select * from schema.table 
    where CHAR(columnname) like '2010-10-01%'
    Lenny

Posting Permissions

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