My query should not return 1 when both of the value of (srf1.shipment_refnum_qual_gid = 'DUCAB.EXCISE_INVOICE_ATTACHED' and srf2.shipment_refnum_qual_gid = 'DUCAB.BOL_ATTACHED') is equal to Y. But one of the Refnum can have one value as Y.
Currently my query is working when both of the values are N or one of the values are N but whenever my values of refnum are Y then my query is not working.
I am new to SQL - please help me to understand.
I have tried using IN, but it is not working.
select 1 from shipment sh, shipment_refnum srf1, shipment_refnum srf2, shipment_remark srk where sh.shipment_gid = srf1.shipment_gid and sh.shipment_gid = srf2.shipment_gid and srf1.shipment_gid = srk.shipment_gid and srf2.shipment_gid = srk.shipment_gid and srf1.shipment_refnum_qual_gid = 'DUCAB.EXCISE_INVOICE_ATTACHED' and srf2.shipment_refnum_qual_gid = 'DUCAB.BOL_ATTACHED' and srf1.shipment_refnum_value in ('Y','N') and srf2.shipment_refnum_value in ('Y','N') and srk.remark_qual_gid = 'DUCAB.REASON_FOR_REJECTION' and srk.remark_text not in ('NO_VALUE') and sh.shipment_gid = 'DUCAB.20110'
So, my desired outcome is if shipment_refnum_value of 'DUCAB.EXCISE_INVOICE_ATTACHED' is Y and shipment_refnum_value of 'DUCAB.BOL_ATTACHED' is Y then my query should not return 1.