Home » SQL & PL/SQL » SQL & PL/SQL » Single quotes in a SQL query
Single quotes in a SQL query [message #202632] Fri, 10 November 2006 11:17 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I have q query which will return the name & the short_name of the set of books from gl_sets_of_books table depending upon the value of the parameter provided for &ES

select gl.name,gl.short_name
from gl_sets_of_books gl
where SUBSTR (gl.short_name, 1, 4) like &ES

If i ran this query by providing 'FR' , then it should pick the records which has the short_name like 'FR%' but the query is picking only one record which has short_name as 'FR' neglecting the short_names like 'FRI', FR01,FR02,etc//

Name | Short_Name
---------------------------------
FR Operations | FR

there are some more records present in the gl_sets_of_books like,

Name | Short_Name
---------------------------------
ESI FR Operation | FRI
FR Buildings | FR01

and so on.

Please let me know how to pick all the records starting with 'FR'
(ie FR01,FR_02,FR_03,...)

Thanks
Safeeq




Re: Single quotes in a SQL query [message #202634 is a reply to message #202632] Fri, 10 November 2006 11:28 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
you should pass value for &ES as 'FS%'.Then you will get all the rows like fr02,fr01 etc..
Re: Single quotes in a SQL query [message #202637 is a reply to message #202632] Fri, 10 November 2006 11:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can avoid the use of SUBSTR and specify the WHERE clause as follows:

SQL> select * from gl_sets_of_books;

NAME                 SHORT_NAME
-------------------- ------------------
ESI FR Operation     FRI
FR Buildings         FR01
FR Operations        FR

SQL> select gl.name,gl.short_name
  2  from gl_sets_of_books gl
  3  where gl.short_name like '&ES%';
Enter value for es: FR
old   3: where gl.short_name like '&ES%'
new   3: where gl.short_name like 'FR%'

NAME                 SHORT_NAME
-------------------- --------------------
ESI FR Operation     FRI
FR Buildings         FR01
FR Operations        FR
Re: Single quotes in a SQL query [message #202641 is a reply to message #202634] Fri, 10 November 2006 11:55 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

No, i dont want to explicitly provide a value like 'FR%'.

since the parameter value can be changed depending upon the scenario. I used 'FR' as an example.

Is there anyother way to accomplish this?
Re: Single quotes in a SQL query [message #202643 is a reply to message #202641] Fri, 10 November 2006 12:05 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As indicated above, use a WHERE clause as:

where gl.short_name like '&ES%'

and this way you only have to enter FR as the search term.
Re: Single quotes in a SQL query [message #202645 is a reply to message #202643] Fri, 10 November 2006 12:06 Go to previous message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Yeah, got it. Its a simple query but dont know i'd struggled a bit Smile

Thanks a lot
Previous Topic: Is the line of code below redundant?
Next Topic: Getting PROBE TIMEOUT Error....please help me out...It is urgent
Goto Forum:
  


Current Time: Sat Dec 03 16:18:54 CST 2016

Total time taken to generate the page: 0.09020 seconds