Storing select query result into array and using in another query.

[paho]

Hi,

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)


Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?Please advice.

Thanks,

[PAVB]

The closest solution would be to resort to cursors.

Depending on table size, percentage of deletes over total population and available indexes this might be either a great idea or a performance nightmare.

[tamilselvan]

Hi,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

can be rewritten as:

delete from a 
where exists 
         (select 'x' from b
           where b.bid = 0 and
                    b.id in (a.a1, a.a2) 
         )

[lancee]

hello!I'm new to oracle triggers.
I'm currently making "when-validate-item"
where it will prompt a message verifying that the input data is actually what the user want to input.
this is my code:
Declare
BEGIN
SELECT CPL_DTL FROM COIL_HISTORY
IF CPL_DTL='ST'
THEN
MESSAGE ('This coil undergone side trimming.')
else
MESSAGE ('This coil undergone side trimming.')
EXCEPTION
WHEN OTHERS THEN MESSAGE('Your input is wrong!);
END;
____________
the result I want is like this:
Please input ' ST' for side trimming and 'NST' for no side trimming
CPL_DTL:_______

Once the user input 'ST' a window will prompt:
"The coil has undergone Side Trimming (ST). "
with ok and cancel button.
How should I code it..?
Thanks!

[LKBrwn_DBA]

lancee,
You should start your own thread on this matter!
Do NOT hijack somene else's thread.

[lancee]

Im really sorry. My mistake.. I thought this will make a new post.