I have a query in which get some parameters now I have some entries with org_id and some entries without org_id but when I run this query it is not showing data that has no org_id I just want to run query if data has org_id or not. Query is here
SELECT ii.item_desc,
II.ITEM_CODE,
ii.uom,
TO_CHAR (ISL.TRAN_DATE, 'Mon-rr') month,
TO_CHAR (ISL.TRAN_DATE, 'rr-mm') mon,
MONTHS_BETWEEN
(TO_DATE(:edate),
TO_DATE(:sdate) ) Months,
ABS (SUM (isl.qty)) qty
FROM inv_stock_ledger isl, inv_items ii
WHERE isl.item_id = II.ITEM_ID
AND ISL.TRAN_TYPE IN ('SIN', 'SRN', 'STN')
and II.IS_ACTIVE = 'Y'
AND ISL.TRAN_DATE BETWEEN '01-jan-20' AND '31-may-20'
AND isl.item_id = NVL ( :item, isl.item_id)
AND isl.company_id = NVL ( :comp, isl.company_id)
AND isl.branch_id = NVL ( :branch, isl.branch_id)
AND isl.store_id = NVL ( :store, isl.store_id)
AND II.ITEM_CATEGORY_ID = NVL( :VAL_ID, II.ITEM_CATEGORY_ID)
AND isl.ORG_ID = NVL ( :dept, isl.ORG_ID)
GROUP BY ii.item_desc,
II.ITEM_CODE,
ii.uom,
TO_CHAR (ISL.TRAN_DATE, 'Mon-rr'),
TO_CHAR (ISL.TRAN_DATE, 'rr-mm')
ORDER BY TO_CHAR (ISL.TRAN_DATE, 'rr-mm'), II.ITEM_DESC asc
As far as I understood, it is isl.org_id that has value in some rows, and is NULL in others.
If that's so, your
AND isl.ORG_ID = NVL ( :dept, isl.ORG_ID)
takes care about the :dept parameter being null, not the other way round. Should probably be something like this (presuming that ID's datatype is number):
and nvl(isl.org_id, -1) = nvl(:dept, -1)