where子句中用于返回结果集的case语句包含空值

Question

我正在做一个jasper报告，它通过where子句中的case语句过滤多个参数，这些参数是可选的。如果没有输入参数，我希望结果集满足其他参数也包括空值。

我在where clause.Excluding中做了一个case语句，我的最后一个条件是我得到了我想要的结果。当参数为空时，我希望在结果中满足其他条件，包括where子句中的其他条件满足的空记录

SELECT
cert.ice_certificate_date_issued AS "START_DATE",
cert.ice_current_license_expiry AS "END_DATE",
--licence_type,
ib.name AS "SHOP",
cert.ice_license_fee AS "SUPERMARKETPRICE",
dist.name AS "DISTRICT",
,iba.ice_price)
,0) AS "ACTIVITY_PRICE",
(select nvl(sum(taxamt),0) from c_ordertax where c_order_id = cert.c_order_id) AS "TAXAMT"
FROM icp_certificate cert
LEFT JOIN c_order ord ON cert.c_order_id = ord.c_order_id
LEFT JOIN icp_business ib ON cert.ice_foreign_trx_entity_id = 
ib.icp_business_id
LEFT JOIN c_bpartner cbp ON cert.c_bpartner_id = cbp.c_bpartner_id
LEFT JOIN c_bpartner_location cbpl ON cert.c_bpartner_id = 
cbpl.c_bpartner_id
LEFT JOIN icp_district dist ON cbpl.ice_district_id = 
dist.ice_district_id
LEFT JOIN c_location loc on ib.c_location_id = loc.c_location_id
LEFT JOIN icp_business_activity_link ibal ON ib.icp_business_id = 
ibal.icp_business_id
LEFT JOIN icp_business_activity iba ON ibal.icp_business_activity_id = 
iba.icp_business_activity_id
WHERE
cert.ad_client_id = 1000555 AND
cert.ad_org_id = 2010520 AND
cert.icp_certificate_status = '01' AND
cert.isactive = 'Y' AND
cert.ice_certificate_type = '0051' AND
cert.ice_currentcertificate = 'Y' AND
cert.icp_certificate_date_issued BETWEEN '01-JAN-19' AND '23-MAY-19' AND
ib.ICp_District_ID = CASE WHEN 0>0 THEN 46445665 else ib.ICp_District_ID 
END

--我希望else部分包含ib.ICp_District_ID为空的记录

返回所有符合其他条件的结果，包括地区id为空的记录

Answer 1

在我看来你可以用

.
.
.
(ib.ICp_District_ID = CASE
                        WHEN 0>0 THEN 46445665
                        else ib.ICp_District_ID 
                      END
 OR
 ib.ICp_District_ID IS NULL)

它可以简化为

NVL(ib.ICp_District_ID, -99) = NVL(ib.ICp_District_ID, -99)

因为CASE表达式总是返回ib.ICp_District_ID。它可以进一步简化，只需省略完全检查ib.ICp_District_ID的条件，因为它总是会得到满足。(当然，假设您打算将条件WHEN 0 > 0保留在CASE表达式中，该条件始终为FALSE，因此它将始终落入ELSE)。

祝你好运。