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

[vega83]

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

 

[MarcLodge]

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

 

[mikrom]

You have tu use % with LIKE.

For example I have following physical file
MYPF

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

select * from QGPL/MYPF   
where EMPNAM = 'MOHAMMAD%'

because this searches literally what is in '...'

But this works well

select * from QGPL/MYPF       
where EMPNAM [COLOR=red]like 'MOHAMMAD%'[/color]

and returns

EMPID     EMPNAM                EMPADD     
E1239879  [COLOR=red]MOHAMMAD[/color] YAHYA        AUSTRAILIA 
E2349870  [COLOR=red]MOHAMMAD[/color] HAARIS       AUSTRAILIA

Similar, I can search for the names containg KH with

select * from QGPL/MYPF 
where EMPNAM [COLOR=red]like '%KH%'[/color]

and I get

EMPID     EMPNAM                EMPADD                       
E1000000  RAJ BHADUR GOR[COLOR=red]KH[/color]A     CHANDNI CHOWK - DELHI = 6    
E3000000  MOHSIN [COLOR=red]KH[/color]AN           342,B BLOCK, RAJ GHAT, DELHI

 

[vega83]

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

 

[papadba]

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.