[SQL] Conditionally execute 2nd query/Use Results IF First query returns no results

I have the following query that has an inline view which has two Select statements that are UNIONed together. What I want to do is execute the first query inside this view and if it results in no rows of data being returned Then execute the 2nd query.

If the first query inside the Inline view does return data then I either want to skip executing the 2nd query, or at least filter out any results from it in the outer query. I don't need to execute (or use the results from) the 2nd query IF the first query returns data.

( SELECT ESI.item_number,
ESI.LONG_DESCRIPTION item_DESCRIPTION,
TC.TRANSACTION_COST
FROM INV_ITEM_SUB_INVENTORIES IOQD
,egp_system_items ESI
-----BEGINNING OF INLINE VIEW WITH FIRST QUERY UTILIZING UNION BELOW:----------------------
, (SELECT * FROM
(SELECT B.INVENTORY_ITEM_ID
,C.SUBINVENTORY_CODE,
B.TRANSACTION_COST TRANSACTION_COST
,ROW_NUMBER() OVER (PARTITION BY B.INVENTORY_ITEM_ID, C.SUBINVENTORY_CODE
ORDER BY B.INVENTORY_ITEM_ID, B.COST_DATE DESC) rownum1
,'' SECONDARY_INVENTORY
FROM CST_ITEM_COST_HISTORY_V B,
CST_TXN_LAYER_DTLS_V C
WHERE 1=1
AND B.INVENTORY_ITEM_ID= _inv_num
AND B.TRANSACTION_ID = C.REC_TRXN_ID
AND C.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
ORDER BY B.COST_DATE DESC NULLS LAST
)
WHERE rownum1 = 1 AND TRANSACTION_COST IS NOT NULL
---2ND (CONDITIONAL QUERY BELOW, ONLY RUN/USE DATA IN OUTER QUERY IF FIRST QUERY HAS NO DATA RETURNED---

UNION

SELECT * FROM
( SELECT INV.INVENTORY_ITEM_ID,
SEC.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
LN.UNIT_PRICE TRANSACTION_COST
, ROW_NUMBER() OVER(PARTITION BY INV.INVENTORY_ITEM_ID , SEC.SECONDARY_INVENTORY_NAME
ORDER BY INV.INVENTORY_ITEM_ID, SEC.SECONDARY_INVENTORY_NAME ) rownumber
,INV.SECONDARY_INVENTORY SECONDARY_INVENTORY
FROM INV_ITEM_SUB_INVENTORIES INV
,inv_secondary_inventories SEC
,PO_LINES_ALL LN
,PO_HEADERS_ALL HDR
,INV_UOM_CONVERSIONS UOM

WHERE SEC.SECONDARY_INVENTORY_NAME = INV.SECONDARY_INVENTORY
AND SEC.organization_id = INV.organization_id
AND LN.ITEM_ID = INV.INVENTORY_ITEM_ID
AND HDR.PO_HEADER_ID = LN.PO_HEADER_ID
AND UOM.INVENTORY_ITEM_ID(+) = INV.INVENTORY_ITEM_ID
AND UOM.UOM_CODE(+) = LN.UOM_CODE
AND INV.INVENTORY_ITEM_ID = _inv_num
)
WHERE rownumber = 1
) TC
----END OF INLINE VIEW (DATA JOINED BELOW IN OUTER QUERY)----------------------------
WHERE IOQD.inventory_item_id = ESI.inventory_item_id
AND TC.subinventory_code(+) = IOQD.secondary_inventory )


I don't have the ability to create stored procedures, declare functions or variables, but can use CTE's potentially. Appreciate your assistance with this.

Continue reading...