Be consistent in the operators you are using either AND, OR or &&, || but don't mix and match
Based on the example in your comment and the code you have posted I'm presuming that you have two parameters to this code
:param_productid
:param_producttype
If :param_productid is not null then filter the table on the value contained within it.
If :param_producttype is not null then filter the table on the value contained within it.
If both parameters are null then return the entire table.
If that is the case then this will work
WHERE (
CASE WHEN :param_productid IS NULL AND :param_producttype IS NULL THEN 1
WHEN UPPER(product.digital_product_id) = :param_productid AND :param_producttype IS NULL THEN 1
WHEN UPPER(product_type) = :param_producttype AND :param_productid IS NULL THEN 1
WHEN UPPER(product.digital_product_id) = :param_productid AND UPPER(product_type) = :param_producttype THEN 1
ELSE 0 END) = 1
Caveat - I haven't tested all of the scenarios and I might have the syntax of the parameters incorrect (I tested this in SQL server)
Basically reproducing the
1=1
test, but instead of an explicit
1=
working out whether or not it should be 1 or 0