Home » SQL & PL/SQL » SQL & PL/SQL » Problem with concatinatnating percentage in the select statement
Problem with concatinatnating percentage in the select statement [message #364941] Wed, 10 December 2008 09:26 Go to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi all,

i have a select statement like this in the backend, which is fetching me the required output.

select * from organisation_units where offical_name like upper('%bost%');



now i am actually trying to write the same statement in the forms like
	V_QRY := V_QRY ||' AND UPPER(OFFICIAL_NAME) LIKE UPPER("%'||:ORGANISATION_UNITS.OFFICIAL_NAME||'%")';


but i am not getting the required output. can any one please help me out on this.

v_qry is a variable which holds the previous value and :organisation_units.official_name is the block_name.field_name
Re: Problem with concatinatnating percentage in the select statement [message #364948 is a reply to message #364941] Wed, 10 December 2008 09:52 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
Assuming those double quotes are actually two single quotes I can't see anything obviously wrong.
As a general rule with dynamic sql problems the simplest way to diagnose the problem is to get whatever tool you're using (I assume forms in this case) to print out / record the full query string before executing it.
Then you can copy the string into sqlplus or similar and see what the problem is.
Re: Problem with concatinatnating percentage in the select statement [message #364949 is a reply to message #364948] Wed, 10 December 2008 09:56 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi,

Thanks for replying,
but then when i tried to fetch the query which is geting formed at run time, noting is being fetched.

but i believe there is a different way to concatenate these special characters, but i tried various ways, noting is working out. Please help me out.
Re: Problem with concatinatnating percentage in the select statement [message #364950 is a reply to message #364941] Wed, 10 December 2008 10:11 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
How about you show us the full query it's actually executing?
There is no special method for concatenating %'s into strings.
Re: Problem with concatinatnating percentage in the select statement [message #364951 is a reply to message #364949] Wed, 10 December 2008 10:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's the advantage with getting the code to dump the whole query - you can run the query yourself and se why no records are returned.
Re: Problem with concatinatnating percentage in the select statement [message #365077 is a reply to message #364949] Wed, 10 December 2008 21:28 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You need to replace your double quotes " with two single quotes ''. They are not the same thing and that is what is causing your problem.
Previous Topic: Displaying the foreign key details in oracle 9i
Next Topic: need help on creating a view excluding some columns from the table
Goto Forum:
  


Current Time: Thu Dec 08 04:30:30 CST 2016

Total time taken to generate the page: 0.08642 seconds