| Subject | Re: Conditional conundrum |
|---|---|
| Author | Adam |
| Post date | 2005-10-22T07:26:36Z |
--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@r...> wrote:
You are right. The approach that Helen suggested works only for the
most simple of queries, and if you have multiple conditional
parameters in the single query, you would potentially be writing
virtually the same query 4, 8, 16 or even 32 times with different
permutations. This is a disaster waiting to happen, and makes simple
tasks like fetching an additional field a lot of work.
But you probably didn't do yourself any favours by simplifying it to
the level you did.
So firstly addressing the conditional part of the query. Firebird 1.5
introduces the Execute Statement command to PSQL. You can read all
about it in the 1.5 release notes. See page 20 for full details.
Basically you create a varchar variable big enough to fit your query,
and put the query into the varchar. You can then use if statements to
append conditions to the query.
eg
DECLARE VARIABLE STMT VARCHAR(500);
BEGIN
STMT = 'SELECT A.FIELD1, A.FIELD2, B.FIELD3, B.FIELD4
FROM TABLEA A
JOIN TABLEB B ON (A.ID=B.TABLEAID)';
IF (:SOMEVALUE = 'U') THEN
BEGIN
STMT = :STMT || ' WHERE SOMEFIELD=''SOMETHING'''
END
EXECUTE STATEMENT STMT INTO .....
END
There are a couple of weaknesses in this approach.
1. You need to know how big the query can possibly get, and make sure
the variable is big enough to handle it.
2. You can not use parameters inside STMT. Actually that is
misleading. You can use parameters in the string, but you will need to
quote them etc.
eg
STMT = STMT || ' WHERE SOMEFIELD = :SOMEINPUTPARAMETER'; -- wont work
STMT = STMT || ' WHERE SOMEFIELD = ' || :SOMEINPUTPARAMETER; -- will
work for integers, but you will need to insert some apostraphies if it
is a timestamp or varchar etc.
3. You need to remember to include the additional white space between
lines. You will notice I have included a space before the "where".
4. You will be tempted to "just pass through the entire where clause".
Don't! You will open up a security hole to your application, and may
lose track of dependencies, particularily if you go the whole hog and
pass through a query to run.
5. The statement will need to be prepared every time the SP is run,
this may impact performance.
6. You have no record of the dependancy this stored procedure may now
have on another table.
Secondly, I will address the logic you used
Where ... OR :param = 'U'
Apart from Firebird not supporting parametised field names so it will
not work anyway, even if it did, you may be left with something like
eg1
where ... OR 'U' = 'U'
eg2.
where ... OR 'not U' = 'U'
Now most people have been or will go down this road sometime, but the
Firebird optimiser does not take the shortcut of ignoring the second
condition in the first example, but rather will attempt to evaluate
'U' = 'U' for every record in the resultset. By the same token, in the
second case, something that is logically always false will be
evaluated for every record.
Until the optimiser is adjusted to deal with this scenario better,
doing the logic in one SQL statement will guarantee the query can't
use any available index. But in your case, I am sure you can use
execute statement to do what you need.
Adam
>Hello Rick,
> Ann answered the question with:
>
> > NULL is a separate discussion, which has been held more time than I
> can bear to contemplate.
> > Firebird can certainly handle 'X' = 'U', though it does not currently
> recognize the tautology and checks each candidate row.
>
> > You appear to be using the IN() predicate to test for a 1:1 equality.
>
> Right now I'm testing for one character. Last month the rule allowed
> 'U' or 'X', hence the IN statement. I'm leaving it as the business
> logic may change again.
>
> > Then branch the logic before you do the query
>
> If it were that easy, I wouldn't have needed to post :-)
> The actual query and associated logic is too cumbersome to break out,
> and would be a maintenance nightmare it I decided to do it.
>
You are right. The approach that Helen suggested works only for the
most simple of queries, and if you have multiple conditional
parameters in the single query, you would potentially be writing
virtually the same query 4, 8, 16 or even 32 times with different
permutations. This is a disaster waiting to happen, and makes simple
tasks like fetching an additional field a lot of work.
But you probably didn't do yourself any favours by simplifying it to
the level you did.
So firstly addressing the conditional part of the query. Firebird 1.5
introduces the Execute Statement command to PSQL. You can read all
about it in the 1.5 release notes. See page 20 for full details.
Basically you create a varchar variable big enough to fit your query,
and put the query into the varchar. You can then use if statements to
append conditions to the query.
eg
DECLARE VARIABLE STMT VARCHAR(500);
BEGIN
STMT = 'SELECT A.FIELD1, A.FIELD2, B.FIELD3, B.FIELD4
FROM TABLEA A
JOIN TABLEB B ON (A.ID=B.TABLEAID)';
IF (:SOMEVALUE = 'U') THEN
BEGIN
STMT = :STMT || ' WHERE SOMEFIELD=''SOMETHING'''
END
EXECUTE STATEMENT STMT INTO .....
END
There are a couple of weaknesses in this approach.
1. You need to know how big the query can possibly get, and make sure
the variable is big enough to handle it.
2. You can not use parameters inside STMT. Actually that is
misleading. You can use parameters in the string, but you will need to
quote them etc.
eg
STMT = STMT || ' WHERE SOMEFIELD = :SOMEINPUTPARAMETER'; -- wont work
STMT = STMT || ' WHERE SOMEFIELD = ' || :SOMEINPUTPARAMETER; -- will
work for integers, but you will need to insert some apostraphies if it
is a timestamp or varchar etc.
3. You need to remember to include the additional white space between
lines. You will notice I have included a space before the "where".
4. You will be tempted to "just pass through the entire where clause".
Don't! You will open up a security hole to your application, and may
lose track of dependencies, particularily if you go the whole hog and
pass through a query to run.
5. The statement will need to be prepared every time the SP is run,
this may impact performance.
6. You have no record of the dependancy this stored procedure may now
have on another table.
Secondly, I will address the logic you used
Where ... OR :param = 'U'
Apart from Firebird not supporting parametised field names so it will
not work anyway, even if it did, you may be left with something like
eg1
where ... OR 'U' = 'U'
eg2.
where ... OR 'not U' = 'U'
Now most people have been or will go down this road sometime, but the
Firebird optimiser does not take the shortcut of ignoring the second
condition in the first example, but rather will attempt to evaluate
'U' = 'U' for every record in the resultset. By the same token, in the
second case, something that is logically always false will be
evaluated for every record.
Until the optimiser is adjusted to deal with this scenario better,
doing the logic in one SQL statement will guarantee the query can't
use any available index. But in your case, I am sure you can use
execute statement to do what you need.
Adam
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
> Sent: Thursday, October 20, 2005 8:12 PM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Re: Conditional conundrum
>
> At 02:46 PM 20/10/2005 -0400, you wrote:
> >Select
> > *
> >From
> > some_big_join
> >Where
> > (unit_use in ('U') and :param in ('U')) or
> > (unit_use not in ('U') and :param not in ('U'))
> >
> >The portion of the query that tests a parameter against a value isn't
> >supported in Firebird.
>
> A parameter *is* a value. Is your error in supposing that your :param is
> (or is allowed to be) a column name? That is what is not supported.
>
> I don't seem to be alone is missing what you're trying to get here. You
> appear to be using the IN() predicate to test for a 1:1 equality.
>
> Suppose for a moment that you are, after all, passing a value in :param
> that might be 'U' or some other character. You want to find (presumably
> multiple) rows where one of the following two conditions applies
> exclusively. You have decided to use a stored procedure to get this set
> because SQL doesn't support XOR.
>
> unit_use = 'U' and the value in :param is 'U'
> unit_use <> 'U' and the value in :param is anything but 'U'
>
> In the SP, you're not interested in any rows where unit_use is 'U' if
> the value in :param isn't 'U', right? Then branch the logic before you
> do the
> query:
>
> begin
> if (param = 'U') then
> for select <set-delimited-list> from some_big_join
> where delimited.unit_use = 'U'
> into <list-of-variables> do
> begin
> ....
> end
> else
> begin
> for select <set-delimited-list> from some_big_join
> where delimited.unit_use <> 'U'
> into <list-of-variables> do
> begin
> ....
> end
> end
> end
>
> Or something else?
>
> ./heLen
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor --------------------~-->
> Most low income households are not online. Help bridge the digital
> divide today!
> http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/67folB/TM
> --------------------------------------------------------------------~->
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item on
> the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>