What about something like

1: select table1.documentno,
2: table2.refno,
3: table2.chequestatus
4: from table1
5: join table2 on table2.tid = table1.tid
6: where table1.documentno = ?
7: and COALESCE(table2.refno, 'NonExistingValue') = ?

Then, setting variable2 to 'NonExistingValue' would get you the NULLs.

Though, notice two things (point 3 below is rather different, so I
didn't count it).

1) COALESCE will prevent the use of indexes on table2.refno
(hopefully, table1.documentno is indexed and pretty selective so that
this won't matter too much).
2) NULL is a still a state and not a value. It means 'UNKNOWN' or 'NOT
APPLICABLE' and is very different from a blank value. COALESCE is just
a handy workaround in some cases.
3) Use SQL-92 with JOIN rather than SQL-89 if possible.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Marvin" 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