Then go Winnie Pooh, choose both!

1: select table1.documentno,
2: table2.refno,
3: table2.chequestatus
4: from table1,table2
5: where table2.tid = table1.tid
6: and table1.documentno = ?
7: and ( table2.refno = ?
8: OR table2.refno is null)
9: and COALESCE(table2.refno, 'NonExistingValue') = ?

That way, line 7 & 8 may use indexes to help performance, whereas
line 9 ascertains the result you want. Even though it is very
simple - if I've ever seen such a solution before, I've forgotten
it. Thanks for showing me something new, Marvin!

Set

--- In firebird-support@yahoogroups.com, "Marvin" wrote:
> Well Svein, I guess i've gotta work more on my accuracy.
> Initially, i was trying to find a way to select records when
> variable2 is null as my current implementation at that time was
> unable to do so. The solution that you suggested,
>
> 7: and COALESCE(table2.refno, 'NonExistingValue') = ?
>
> is most accurate functionally in comparison to my original query.
> However, at the cost of performance(a search took on average 8 - 10
> seconds). So, a little give-and-take is required.
> The solution i chosed is ASSUMING that the documentno maps to
> only 1 refno which is either null or not null, as this is the
> expected result. But i guess i gotta add some mechanism in case it
> returns more than 1 result set.