Using 'Select.. Where... LIKE'

itsonlyme44 · #1 (**permalink**) 10-07-10, 10:54

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???

Lenny77 · #2 (**permalink**) 10-07-10, 11:35

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

itsonlyme44 · #3 (**permalink**) 10-07-10, 11:48

Thank you!!!! Between will work !

Lenny77 · #4 (**permalink**) 10-07-10, 12:02

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

itsonlyme44 · #5 (**permalink**) 10-07-10, 12:13

Awesome! That worked beautifully!

Lenny77 · #6 (**permalink**) 10-07-10, 12:54

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

Code:

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

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech