Hi everyone,
I have a pretty big sql statement which returns a list of id's. I need this id-list as base for other statements. Like this:
open crs_result1 for ' select * from ... where id in ( select <ids> from <base_statement> ) ';
open crs_result2 for ' select * from ... where id in ( select <ids> from <base_statement> ) ';
open crs_result3 for ' select * from ... where id in ( select <ids> from <base_statement> ) ';
...
Of course I don't want to select the whole id-list each time for the different selects.
So, my idea was to use a table/array:
TYPE gt_result_rec IS RECORD
(
id NUMBER
);
TYPE gt_result_tab IS TABLE OF gt_result_rec INDEX BY BINARY_INTEGER;
t_results gt_result_tab;
execute immediate 'insert into t_results select <ids> from <base_statement>';
And than use it for all other statements:
open crs_result1 for ' select * from ... where id in ( select id from t_results ) ';
...
But this doesn't really works.
Does anyone know the problem OR has a better solution for this?
Thx 4 answers