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.

-----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