How to use RECORD or CURSOR in where conduction
<p>I am writing a stored procedure which needs to return document id
this document id is can be any of the two fields.
APP_DOC_ID (added from app) BTCH_DOC_D(added from batch) both are different fields in same table.</p>

<p>I need to get those docid's based on subquery.</p>

<pre><code>SELECT DISTINCT APP_DOC_ID FROM
(SELECT APP_DOC_ID FROM XXXXXX BC WHERE BC.XX||BC.XY||BC.XZ IN 
(SUBQUERY))
UNION ALL
SELECT DISTINCT BTCH_DOC_ID FROM
(SELECT BTCH_DOC_ID FROM XXXXXX BC WHERE BC.XX||BC.XY||BC.XZ IN 
(SUBQUERY))
</code></pre>

<p>My SUBQUERY is little complicated so I don’t want to run that query 2 time 1 APP_DOC_ID and BTCH_DOC_ID,
Is it possible to save subquery results in RECORD or CURSOR and use it both query’s</p>

<p>Like</p>

<pre><code>BEGIN
OPEN P_CURSOR FOR (Select AB.XX||AB.XY||AB.XZ from ....)
SELECT DISTINCT APP_DOC_ID FROM
    (SELECT APP_DOC_ID FROM XXXXXX BC WHERE BC.XX||BC.XY||BC.XZ IN P_CURSOR)
UNION ALL
SELECT DISTINCT BTCH_DOC_ID FROM
(SELECT BTCH_DOC_ID FROM XXXXXX BC WHERE BC.XX||BC.XY||BC.XZ IN P_CURSOR)
</code></pre>

<p>this this approach I am not running my complex subquery 2 times. </p>

<p>Thanks for your help and time</p>