CODEWITHSUNDEEP
javasqlpostgresql
Richard
Richard

Reputation: 8955

Postgres SQL - check if parameter is null

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.

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;

Upvotes: 1

Views: 1043

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

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

Upvotes: 1

Scary Wombat
Scary Wombat

Reputation: 44854

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

Upvotes: 1

Related Questions