Where clause with wildcard

[BryanY]

I would like to use a single prepared statement (JDBC).

For example: SELECT NAME WHERE REGION = ? AND SITE = ?

The issue is that the region and site qualifiers are optional. Is there a way that I could set the region/site values to a wildcard, so that they don't get used?

 

[r937]

what's JDBC? is that what handles the question marks?

(because the question marks aren't part of ANSI SQL)

you've also overlooked the FROM clause ;-)


SELECT NAME
FROM daTable
WHERE ( REGION = [red]?[/red] OR [red]?[/red] IS NULL )
AND ( SITE = [red]?[/red] OR [red]?[/red] IS NULL )


r937.com | rudy.ca

 

[PHV]

Another possible way:
SELECT NAME FROM daTable WHERE REGION Like ? || '%' AND SITE Like ? || '%'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

and if ? is null?

this isn't an sql question is it

do we have a JDBC forum?

r937.com | rudy.ca

 

[PHV]

and if ? is null?
I don't think a parameter may be null
this isn't an sql question is it
definitively not

 

[r937]

I don't think a parameter may be null

"The issue is that the region and site qualifiers are optional."



perhaps forum197 or forum269

r937.com | rudy.ca

 

[JarlH]

Try this (untested):
[tt]
SELECT NAME FROM sometable
WHERE COALESCE(REGION = ?,TRUE) AND COALESCE(SITE = ?, TRUE)
[/tt]

(If a parameter is not null, the "REGION/SITE = value" part will be evaluated. If the parameter is null, the COALESCE part will be TRUE for all values of REGION/SITE.)

The ? are used in dynamic SQL, just as specified by the ISO/ANSI SQL standard.
(Look for "<dynamic parameter specification> ::= <question mark>".)