I am using Postgres 12.6 in a Java7 application using a hibernate Native query.

I am trying to construct a SQL statement that reads results from a table journalheader where it receives one parameter. The parameter values can be 'Matched' or 'Unmatched'.

The following solution would be inadequate, because it does not cater for null values:

select * from journalheader where matched_status = ?1;

The table does have some rows with the matched_status column of null. I would like these rows to be part of the 'Unmatched' filter.

i.e.

• If the parameter (?1) is 'Matched', then it will only return rows where matched_status = 'Matched'.
• If the parameter (?1) is 'Unmatched', then it will only return rows where matched_status = 'Unmatched' or null.

Question

How do I also check for the null values?

Possible solutions

I think I may need to add a check on the parameter value, and if it is 'Unmatched' include or matched_status is null in the statement. I am just not sure how to check the value of the parameter in the sql statement.

select * from journalheader where matched_status = ?1 or <if ?1 equals 'Unmatched'> matched_status is null;

CodePudding user response：

You can use the keyword COALESCE

select * from journalheader where COALESCE(matched_status, 'Unmatched') = ?1;

This mean use the value of matched_status unless the value is null, if the value is null then use the value Unmatched

CodePudding user response：

If i understand correctly your question you could try using COALESCE

select * 
from journalheader 
where matched_status = COALESCE(?1,'Unmatched') 

or case when

select * 
from journalheader 
where matched_status =CASE WHEN ?1 is null THEN 'Unmatched' ELSE ?1 END