--- In firebird-support@yahoogroups.com, "Marvin" <gallagher_98@...>
wrote:

>
> Hey guys, been cracking my head trying to solve this problem.
> Wondering if anyone can help me shed some light into this.
>
> -[The Situation]-
>
> I'm developing in Java and using firebird 1.5. I have a prepared
> statement with the sql set to sth like :
>
> 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 = ?
>
> the prepared statement is then fill with parameters like so:
>
> pstmt.setString(1,variable1);
> pstmt.setString(2,variable2);
>
> The problem here happens when, sometimes, variable2 is null. when i
> execute the prepared statement, it would not return any resultset. I
> noticed that to get resultsets, i'd have to use the following SQL in
> line 7:
>
> " and table2.refno is null "
>
> I tried using
>
> pstmt.setNull(2,Types.VARCHAR);
>
> if variable2 is null, but it still returns no resultset.
>
>
> -[The Question]-
>
> My question here is, is there a String or character in firebird that
> represents NULL for the type varchar such that i am able to do a '='
> operation to evaluate to a NULL value, instead of using 'is' operator?
> If there isn't, anyone have a better idea instead of creating 2
> prepared statements (1 more to cater for variable2 = null) ???
>
>
> Thanks a million in advance!!
>
> Regards,
> Marvin
>

Have you tried something like this
(formatted for readability)

select t1.DocumentNo, t2.RefNo, t2.ChequeStatus
from table1 t1 inner join table2 t2 on (t1.TID = t2.TID)
where
(
(
(coalesce(:DocumentNo, 0) = 0) and
(t1.DocumentNo is null)
)
OR
(
(coalesce(:DocumentNo, 0) = 1) and
(t1.DocumentNo = :DocumentNo)
)
)

In DSQL it only seems to work with integers, in procedures it works
great with varchars as well, especially doing stuff like optional
filters e.g

where
(
(coalesce(:DocumentNo, '') = '') or
(t1.DocumentNo = :DocumentNo)
)

I'm not certain how this affects firebirds optimization, but it works
fine in the places i use it

cheers, Terry