Condensed Example & Explanation
I want to write a WHERE IN clause that selects from a pre-populated set of numbers
Here's some code. I want to store this set of numbers, and select from them so i don't have to repeat the query that generates this set of numbers.
ARRAY_OF_NUMBERS = Values from some select statement
-- SHIPMENTS CURSOR
OPEN O_SHIPMENTS_CURSOR FOR
SELECT *
FROM Q194977.AN_SHIPMENT_INFO SI
WHERE INTERNAL_ASN IN (ARRAY_OF_NUMBERS) -- need to populate something
ORDER BY INTERNAL_ASN;
-- ORDER CURSOR
OPEN O_ORDERS_CURSOR FOR
SELECT *
FROM Q194977.AN_ORDER_INFO OI
WHERE INTERNAL_ASN IN (ARRAY_OF_NUMBERS) -- need to populate something
ORDER BY INTERNAL_ASN;
I read something about using an array, but it said it had to be a global array instead of session level. I'm not sure how true this is, and I'm not sure what a global array even is, but i imagine this needs to be session level as it would change with each procedural call. Perhaps i could use a temporary table.
Any ideas on the best way i can accomplish this?
------------- EDIT ------------ (Adding detailed example)
Detailed Example and Explanation
I have 4 tables at 4 different hierarchical levels, and 4 stored procedures. Each procedure contains input criteria to build a selection of data at all 4 levels via criteria for a certain level.
In this example, my caller will input selection criteria that exists at the carton level. Then i will use the INTERNAL_ASN numbers narrowed from this selection, to move up hierarchical levels and retrieve: ORDERS this carton is on, SHIPMENTS that ORDER is on, and then down to retrieve: ITEMS on this CARTON.
I noticed when going up levels, i was repeating the same selection, and though i should somehow store this set of numbers, so i didn't rerun the selection each time to get them, but wasn't sure how.
-- SHIPMENTS CURSOR
OPEN O_SHIPMENTS_CURSOR FOR
SELECT *
FROM Q194977.AN_SHIPMENT_INFO SI
WHERE INTERNAL_ASN IN
(SELECT INTERNAL_ASN
FROM Q194977.AN_CARTON_INFO CI
WHERE (I_BOL IS NULL OR BILL_OF_LADING = I_BOL)
AND ( I_CARTON_NO IS NULL
OR CARTON_NO = I_CARTON_NO)
AND (I_PO_NO = 0 OR PO_NO = I_PO_NO)
AND (I_STORE_NO = 0 OR STORE_NO = I_STORE_NO))
ORDER BY INTERNAL_ASN;
-- ORDER CURSOR
OPEN O_ORDERS_CURSOR FOR
SELECT *
FROM Q194977.AN_ORDER_INFO OI
WHERE INTERNAL_ASN IN
(SELECT INTERNAL_ASN
FROM Q194977.AN_CARTON_INFO CI
WHERE (I_BOL IS NULL OR BILL_OF_LADING = I_BOL)
AND ( I_CARTON_NO IS NULL
OR CARTON_NO = I_CARTON_NO)
AND (I_PO_NO = 0 OR PO_NO = I_PO_NO)
AND (I_STORE_NO = 0 OR STORE_NO = I_STORE_NO))
AND (I_PO_NO = 0 OR PO_NO = I_PO_NO)
ORDER BY INTERNAL_ASN;
-- CARTONS CURSOR
OPEN O_CARTONS_CURSOR FOR
SELECT *
FROM Q194977.AN_CARTON_INFO CI
WHERE (I_BOL IS NULL OR BILL_OF_LADING = I_BOL)
AND (I_CARTON_NO IS NULL OR CARTON_NO = I_CARTON_NO)
AND (I_PO_NO = 0 OR PO_NO = I_PO_NO)
AND (I_STORE_NO = 0 OR STORE_NO = I_STORE_NO)
ORDER BY INTERNAL_ASN;
-- ITEMS CURSOR
OPEN O_ITEMS_CURSOR FOR
SELECT *
FROM Q194977.AN_ITEM_INFO II
WHERE CARTON_NO IN
(SELECT CARTON_NO
FROM Q194977.AN_CARTON_INFO CI
WHERE (I_BOL IS NULL OR BILL_OF_LADING = I_BOL)
AND ( I_CARTON_NO IS NULL
OR CARTON_NO = I_CARTON_NO)
AND (I_PO_NO = 0 OR PO_NO = I_PO_NO)
AND (I_STORE_NO = 0 OR STORE_NO = I_STORE_NO))
ORDER BY INTERNAL_ASN;
Assuming that you mean a collection of numbers (there are three collection types in PL/SQL, one of which is an associative array, but that doesn't sound like what you want here), you could do something like
CREATE OR REPLACE TYPE num_tbl
AS TABLE OF NUMBER;
Then, in your procedure
l_nums num_tbl;
BEGIN
SELECT some_number
BULK COLLECT INTO l_nums
FROM <<your query to get the numbers>>;
<<more code>>
OPEN O_SHIPMENTS_CURSOR FOR
SELECT *
FROM Q194977.AN_SHIPMENT_INFO SI
WHERE INTERNAL_ASN IN (SELECT column_value
FROM TABLE( l_nums ))
ORDER BY INTERNAL_ASN;
That is syntactically valid. Whether it is actually going to be useful to you, however, is a separate question.
internal_asn
values are used to probe the an_shipment_info
table using an index, that may not be a major concern. If you're not sure about what the best query plan is, and particularly if your actual queries are more complicated than what you posted, however, you might be preventing the optimizer from using the most efficient plan for each query.What is the problem that you're trying to solve? You talk about not wanting to duplicate code. That would lead me to suspect that you really just want a view that you can reference in your queries rather than repeating the code for a complicated SQL statement. But that presumes that the issue you're trying to solve is one of code elegance which may or may not be accurate.