For this simple case, a stored proc might work, but if I understand your
suggestion, it would involve enumerating the possible cases and using
different queries.
However, the example I used was trivial, useful only for asking the
question. What I actually need to do is a bit more complex.
Suppose you have a larger query, with multiple parameters that might or
might not be present, and so there are a lot of possible combinations
which would result in many different queries being generated. It'd be a
pain to enumerate them all. Would a store proc really help in that
situation? (I admit I'm fairly ignorant when it comes to stored procs,
but it seems you'd have to be able to generate the SQL on the fly, which
I doubt they can do).
-Vadim
Yee, Richard K CTR DMDC wrote:
You should do this inside a stored procedure instead of using iBATIS or your
code to do it.
-Richard
-----Original Message-----
From: Vadim Grinshpun [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 28, 2006 11:58 AM
To: [email protected]
Subject: flexible queries?
Hi everyone,
Suppose I have a query where I want a join to be performed only if *one
or more* of a set of parameters are present.
Is there a way in SqlMaps to do something like this? (below is an
example snippet of what I'd like to do, using some pseudocode in the
first <dynamic> section):
SELECT * FROM
table_foo f, table_bar b
<dynamic> <!-- if any of the params in the conditional are defined,
add one more table to join -->
<if>
<or>
<isEqual property="X.defined" compareValue="true" >
<isEqual property="X.defined" compareValue="true" >
</or>
<then>, table_baz z</then>
</if>
</dynamic>
WHERE f.field = b.field
<dynamic>
<isEqual property="X.defined" compareValue="true" prepend="AND"
>z.x_field > #X.value#</isEqual>
<isEqual property="Y.defined" compareValue="true" prepend="AND"
>z.y_field = #Y.value#</isEqual>
</dynamic>
Thanks,
--Vadim
