Null variables in where part
David wanted a single prepared SQL statement where a NULL host variable effectively disables a WHERE filter (e.g. "WHERE prodno = ?" behaving as no filter when ? is NULL), rather than building the WHERE clause in application code. Art Kagel argued for assembling the string in code or preparing two statements; one poster suggested NVL(?,1)=1, which David said didn't help; Fernando Nunes offered "WHERE (? IS NULL OR prodno = ?)", warning it may force a full table scan. David preferred keeping the query with the entity; no definitive resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I've seen functions like IsNULL for other databases. Is is possible to build a dynamic SQL statement as a string and pass this to the engine and have it handle whether the paramter was null? (its not a stored procedure.) For example "SELECT * FROM product WHERE prodno = ?1" If the parameter is null then you'd want the database to perform "SELECT * FROM product WHERE 1=1" I've seen something along the lines of "SELECT * FROM product WHERE (LEN(?1) OR prodno = ?1)" ie if the parameter is null then it uses prodno = prodno. I couldn't seem to get this to work. Basically I was hoping to do this in SQL rather than having to use String functions and build up a string and where part depending on the values of the parameters passed in. Any Suggestions? Thanks David
One question: Why? What's wrong with: if (strlen( input_param_1 ) != 0) { snprintf( SQL_Where_End, sizeof SQL_Where_End, " AND some_col = %s", input_param_1 ); } else { SQL_Str_End[0] == (char)0; } snprintf( SQL_Str, sizeof SQL_Str, "%s %s %s", SQL_Start, SQL_Where_End, SQL_Rest ); ... EXEC SQL PREPARE sql_stmt FROM SQL_Str; .... And all of the work's being done in code instead of being done in the engine depending on the parameter. Or, even better, PREPARE both versions of the SQL string, one with the filter on the possibly NULL parameter and one without it. Then you can dynamically decide which to execute or open a cursor against and both were PREPARED only once at application startup instead of the engine having to make decisions every time the query is executed. Art On Thu, Jun 12, 2008 at 11:51 PM, DAVID PLEYDELL < david.pleydell@reece.com.au> wrote: > Hi, > > I've seen functions like IsNULL for other databases. Is is possible to > build a > dynamic SQL statement as a string and pass this to the engine and have it > handle whether the paramter was null? (its not a stored procedure.) > > For example > > "SELECT * FROM product WHERE prodno = ?1" > If the parameter is null then you'd want the database to perform "SELECT * > FROM product WHERE 1=1" > > I've seen something along the lines of > > "SELECT * FROM product WHERE (LEN(?1) OR prodno = ?1)" > > ie if the parameter is null then it uses prodno = prodno. I couldn't seem > to > get this to work. > > Basically I was hoping to do this in SQL rather than having to use String > functions and build up a string and where part depending on the values of > the > parameters passed in. > > Any Suggestions? > Thanks > David > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Maybe you can use the NVL function. For example:
SELECT * FROM product WHERE NVL(?,1)=1
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVID
PLEYDELL
Sent: Jueves, 12 de Junio de 2008 10:51 p.m.
To: ids@iiug.org
Subject: Null variables in where part [12409]
Hi,
I've seen functions like IsNULL for other databases. Is is possible to build a
dynamic SQL statement as a string and pass this to the engine and have it
handle whether the paramter was null? (its not a stored procedure.)
For example
"SELECT * FROM product WHERE prodno = ?1"
If the parameter is null then you'd want the database to perform "SELECT *
FROM product WHERE 1=1"
I've seen something along the lines of
"SELECT * FROM product WHERE (LEN(?1) OR prodno = ?1)"
ie if the parameter is null then it uses prodno = prodno. I couldn't seem to
get this to work.
Basically I was hoping to do this in SQL rather than having to use String
functions and build up a string and where part depending on the values of the
parameters passed in.
Any Suggestions?
Thanks
David
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
SELECT * FROM product WHERE (?1 is null or prodno = ?1 );
But this may not be efficient... If you have other conditions on the WHERE
clause it may be good enough, but otherwise it can scan the full table.
Test it.
Regards
On Fri, Jun 13, 2008 at 4:51 AM, DAVID PLEYDELL <david.pleydell@reece.com.au>
wrote:
> Hi,
>
> I've seen functions like IsNULL for other databases. Is is possible to
> build a
> dynamic SQL statement as a string and pass this to the engine and have it
> handle whether the paramter was null? (its not a stored procedure.)
>
> For example
>
> "SELECT * FROM product WHERE prodno = ?1"
> If the parameter is null then you'd want the database to perform "SELECT *
> FROM product WHERE 1=1"
>
> I've seen something along the lines of
>
> "SELECT * FROM product WHERE (LEN(?1) OR prodno = ?1)"
>
> ie if the parameter is null then it uses prodno = prodno. I couldn't seem
> to
> get this to work.
>
> Basically I was hoping to do this in SQL rather than having to use String
> functions and build up a string and where part depending on the values of
> the
> parameters passed in.
>
> Any Suggestions?
> Thanks
> David
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
The main reason is that you don't have to build the SQL in the code. You can create a named query that stays with the entity rather than in the web service. Otherwise to reuse the query you have to include or use the code that builds the SQL. David
I think I tried that and it was accepted. NVL I think only works in the SELECT clause. David