Argthtjtr

I am developing a web application (java-maven project) and there is a problem with the query I use. The UI of my web app has 2 search fields: PTT and ID.
The user needs to fill at least one of the fields to make a search. So both fields are nullable but not at the same time.
Before, I had only one field: PTR and it was showing a result array of size 52. (also getting the same number if
I execute select * from users where ptr='smthing' ). After that I added ID field and updated my query as below:

I execute this query in my webservice:

String query= "SELECT t.ptr, t.id ";

        query+= "FROM users t ";

        query+= "WHERE t.ptr = COALESCE(?, t.ptr) AND " ;

        query+= "t.id = COALESCE(?, t.id) ";

and set the fields with the help of Prepared Statement.

Now if the ptr field is filled, but id field is left blank (this can be null or empty string) on the UI and user makes a search, result array size becomes 30. I compared with database
and it does not fetch the rows where ID is null. So coalesce is not what I need when both of its parameters (?, t.ptr) is null.
How can I fix this problem, any suggestions?

I think the logic you want is:

WHERE (t.ptr = ? OR ? IS NULL) AND 

      (t.id = ? OR ? IS NULL)

I would recommend using named parameters, so you don't have to pass them in twice.

Check this statement:

String query= "SELECT t.ptr, t.id ";

query+= "FROM users t ";

query+= "WHERE (t.ptr = ? OR 1 = ?)" 

query+= " AND " ;

query+= "(t.id = ? OR 1 = ?)";

You see that for each of t.id and t.ptr there is a counterpart parameter.
In total there will be 4 parameters.

You say that at least 1 of t.id or t.ptr has a valid value, so there are 2 cases:

[1] t.id and t.ptr both have valid values.


For both the counterpart parameters you pass 0 and the query becomes:
"SELECT t.ptr, t.id FROM users t WHERE (t.ptr = valueptr OR 1 = 0) AND (t.id = valueid OR 1 = 0)"

In the WHERE part:
t.ptr = valueptr OR 1 = 0 is equivalent to t.ptr = valueptr, and
t.id = valueid OR 1 = 0 is equivalent to t.id = valueid,

and the query finally becomes:
"SELECT t.ptr, t.id FROM users t WHERE t.ptr = valueptr AND t.id = valueid"

[2] from t.id or t.ptr only one has a valid value, let's say this is t.ptr.


For the counterpart of t.ptr you pass 0, for t.id you pass -1 (or any other non existing value) and for the counterpart of t.id you pass 1 and the query becomes:
"SELECT t.ptr, t.id FROM users t WHERE (t.ptr = valueptr OR 1 = 0) AND (t.id = -1 OR 1 = 1)"

In the WHERE part:
t.ptr = valueptr OR 1 = 0 is equivalent to t.ptr = valueptr, and
t.id = -1 OR 1 = 1 is equivalent to true because 1 = 1 is always true,

and the query finally becomes:
"SELECT t.ptr, t.id FROM users t WHERE (t.ptr = valueptr OR 1 = 0)"

equivalent to:
"SELECT t.ptr, t.id FROM users t WHERE (t.ptr = valueptr)"


(In the case where only t.id has a valid value then you pass an invalid value for t.ptr and 1 for its counterpart and for the counterpart if t.id you pass 0.)


Maybe it seems complicated but it's working and it can be extended for more than 2 columns.

Oracle has build function for this. But it's hard to understand how to use its.

lnnvl(a = b) = true if (a != b ) or ( a = null ) or (b = null)

in your case

WHERE lnnvl(t.ptr != ? ) AND lnnvl( t.id != ?)

LNNVL