Problem Passing value to LIKE in SQl query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem Passing value to LIKE in SQl query

Author

Topic

sirishaj
Starting Member

3 Posts

Posted - 2012-04-17 : 09:51:42

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-17 : 09:52:31

SQLTeam is a Microsoft SQL Server web site. For Oracle questions try here: http://dbforums.com/

sirishaj
Starting Member

3 Posts

Posted - 2012-04-17 : 09:54:47

Sorry ! thank you for the info

Thanks,
Sirisha J

grmnsplx
Starting Member

8 Posts

Posted - 2012-04-17 : 10:05:59

The is here:
and c.key like '%'+ a.id '+%'

Oracle thinks you are trying to add stings to numbers.
Second, this is not what you want to do anyway.

In the DBforums, please describe what that last line is supposed to mean and they'll help you.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-17 : 10:21:27

quote:
Originally posted by grmnsplx

The is here:
and c.key like '%'+ a.id '+%'

Oracle thinks you are trying to add stings to numbers.
Second, this is not what you want to do anyway.

In the DBforums, please describe what that last line is supposed to mean and they'll help you.

That could probably be fixed with:

and c.key like '%' || a.id || '%'

You may still get a conversion error if the id column is numeric.

sirishaj
Starting Member

3 Posts

Posted - 2012-04-17 : 10:22:04

Thanks all... I got info from DBForums...
It shud be
c.key like '%' || a.id || '%'

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

Thanks,
Sirisha J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources