jQuery in Action, 2nd edition*
The moose likes Oracle/OAS and the fly likes how to use variable with oracle like Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "how to use variable with oracle like" Watch "how to use variable with oracle like" New topic
Author

how to use variable with oracle like

Ashish Shinde
Ranch Hand

Joined: Dec 08, 2002
Posts: 34
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

Joined: Oct 10, 2000
Posts: 1364
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

Joined: Dec 08, 2002
Posts: 34
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

Joined: Feb 05, 2001
Posts: 17249
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60775
    
  65

"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


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to use variable with oracle like
 
Similar Threads
URL encoding and arguments
Search Strategy
SQL statement to java
Escaping Sequence problem.
how add data in database through jsp