• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to use variable with oracle like

 
Ashish Shinde
Ranch Hand
Posts: 34
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I need to use a substring while doing a slect which involves a like statment.
Something like "select id from ....... where uid like '%'||<variable>.
where <variable> is a substring of some other column.

I tried using the above but it does not work since query just does not
return.
There is no issue with query because if I insert hardcoded value expected from substring into the variable,
it returns the required result immediately.

Does anybody know how to go about this?using a variable in like clause
 
Carol Enderlin
drifter
Ranch Hand
Posts: 1364
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That looks like pseudo-code, hard to comment. It would help if you would post a real sample, maybe you can come up with something really simple that you don't have to edit out for posting.

Just does not return? Do you mean it is slow?

Are you doing this in sql or in jdbc or?
[ January 31, 2007: Message edited by: Carol Enderlin ]
 
Ashish Shinde
Ranch Hand
Posts: 34
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
a sample would be:

select x.uname,(select count(*) from table2 where id like (
'%'||substr(x.uid,instr(x.uid,'#',1,1))
))
from table1 x

Here the expression "'%'||substr(x.uid,instr(x.uid,'#',1,1))" is variable in the like clause.However this does not work as expected.
When it is hardcoded as :
select x.uname,(select count(*) from table2 where id like (
'%'||'#XYZ'
))
from table1 x.
This works and returns the result.
The query takes a very long time i guess. I am executing this as a sql stmt and not jdbc.
 
Mark Spritzler
ranger
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you have a subquery.

How does the subquery know what "x" means?

I was just thinking about what happends if the two tables are in the FROM clause and there is a join on your subqueries where clause (Theta-style join) and then have your count() in the select, and a GROUP BY clause. Just an idea.

Mark
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64178
83
IntelliJ IDE Java jQuery Mac Mac OS X
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"ash sh", you have previously been warned on one or more occasions regarding adjusting your display name to meet JavaRanch standards. This is not optional. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it prior to your next post.

Your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Be aware that accounts with invalid display names are removed.

bear
JavaRanch Sheriff
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic