INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to use % in where clause in paramter to get all data

How to use % in where clause in paramter to get all data

How to use % in where clause in paramter to get all data

(OP)
Hello friends I am trying to use % for all in my SQL statement
in where caluse I have
AND B.County_Id in {?County} or {?County} = '%'
B.County_Id is numeric and {?County} is string. Any ideas?.

I also tried
cast(B.COUNTY_ID as varchar(32)) in {?County} or {?County} = '%'

and cast(B.COUNTY_ID as varchar(32))  like {?County}

But I get errors

RE: How to use % in where clause in paramter to get all data

Hi vega83,
the use of a '%' in sql is a way of saying 'give me everything that begins with.....'.
For instance is you wished to pick up all the U.S counties that began with an 'L' then you would code WHERE COUNTY = 'L%'.

Your code 'or {?County} = '%' does not therefore make sense as you are saying 'give me everything on the table'.

What I think is your problem is that the column COUNTY_ID is a number, yet the variable you are supplying to it is a string.

What value can be found in {?County}? Is it a number or a literal? If a literal, then you need to look on the table to see if there is another column that contains that literal. If it is a number then try converting the char to a number using the DIGITS function.

Hope this help, if not, come back to us.

Marc  

RE: How to use % in where clause in paramter to get all data

You have tu use % with LIKE.

For example I have following physical file
MYPF

CODE

EMPID     EMPNAM                EMPADD                        
E1000000  RAJ BHADUR GORKHA     CHANDNI CHOWK - DELHI = 6     
E2000000  SALEHA BEGUM          GF CHANDNI CHOWK, BALLIMARAN  
E3000000  MOHSIN KHAN           342,B BLOCK, RAJ GHAT, DELHI  
E1239879  MOHAMMAD YAHYA        AUSTRAILIA                    
E2349870  MOHAMMAD HAARIS       AUSTRAILIA                    
E2308594  ABIDA BEGUM           GROUND FLOOR DELHI            
E9823590  MEHMOOD AHMED         BALLIMARAN, CHANDNI CHOWK     
if I want to select all names beginning with MOHAMMAD, then this doesn't work

CODE

select * from QGPL/MYPF   
where EMPNAM = 'MOHAMMAD%'
because this searches literally what is in '...'

But this works well

CODE

select * from QGPL/MYPF       
where EMPNAM like 'MOHAMMAD%'
and returns

CODE

EMPID     EMPNAM                EMPADD     
E1239879  MOHAMMAD YAHYA        AUSTRAILIA
E2349870  MOHAMMAD HAARIS       AUSTRAILIA
Similar, I can search for the names containg KH with

CODE

select * from QGPL/MYPF
where EMPNAM like '%KH%'
and I get

CODE

EMPID     EMPNAM                EMPADD                       
E1000000  RAJ BHADUR GORKHA     CHANDNI CHOWK - DELHI = 6    
E3000000  MOHSIN KHAN           342,B BLOCK, RAJ GHAT, DELHI

RE: How to use % in where clause in paramter to get all data

(OP)
Hello I have use CHAR(my field) like'?Param' it works

RE: How to use % in where clause in paramter to get all data

What does this "I have use CHAR(my field) like'?Param' it works " have to do with "%"?

Suggest you take a couple of minutes and more completey post what you are trying to say/ask.

" I am trying to use % for all in my SQL statement
" This escapes me - if "all/everything" is wanted, why specify a predicate?

It will help if you show some sample data and what your query should return when that sample data is processed.

The better you explain what you really want to do, the more likely useful replies will be posted.



 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close