How to run a sql query multiple times and combine the results into single output?

Question

I have a list of 2500 obj numbers stored in Excel for which I need to run the below SQL:

SELECT    
    a.objno,
    a.table_comment,
    b.queue_comment
FROM
    aq$_queue_tables a 
JOIN 
    AQ$_QUEUES b ON a.objno = b.table_objno
WHERE
    a.objno = 19551;

Is there any way I can write a loop on above SQL with objno feeding from a list or from a different table? I also want to store/produce all the results from each loop run as a single output.

I considered the option to upload the numbers into a new table and add a where condition:

a.objno=(SELECT newtab.objectno FROM newtab);

However, the logic I'll be writing in the query would exclude certain objectno results. Let's say that the associated objectno has certain queue_comment as of certain date associated with that objectno. I do not want to pull that record. This condition would match with some objectno and wouldn't match with others. Having that condition and running the query against all the objectno is returning 0 results. I couldn't share the original logic as it would reveal certain business rules and it'll be a violation of some policy.

So, I need to run the query on each objectno separately and combine the results.

I'm totally new to SQL and got this task assigned. I'm aware of the regular loop, for in SQL, but I don't think I can apply them in this situation.

Any guidance or reference links to helpful topics is much appreciated as well.

Thanks in advance for the help.

You could run a single query using (eg) ...WHERE a.objno in (19551,19552,19553); — Tim Williams
– Tim Williams, Commented Feb 4, 2019 at 5:50
"the logic I'll be writing in the query would exclude certain objectno results" - it's not clear what you mean by this. Are you saying some of your id's have no match in the database? — Tim Williams
– Tim Williams, Commented Feb 4, 2019 at 5:53
They will have the match. But let's say the associated objectno has certain queue_comment as of certain date associated with that objectno. I do not want to pull that record. This condition would match with some objectno and wouldn't match with others. Having that condition and running the query against all the objectno is returning 0 results — Pramod
– Pramod, Commented Feb 4, 2019 at 5:58
So the query you posted is not the query you want to run? If you can't be specific in your question it's difficult to provide suggestions as to how you might solve your problem.... — Tim Williams
– Tim Williams, Commented Feb 4, 2019 at 5:59

vmaroli · Accepted Answer · 2019-02-04 07:57:35Z

1

One option is to upload the object numbers from Excel sheet to a table in the database and run the query as following. Assuming newtab is the table where the objectno are uploaded.

SELECT    
a.objno,
a.table_comment,
b.queue_comment

FROM
    aq$_queue_tables a JOIN AQ$_QUEUES b on a.objno = b.table_objno
WHERE
    a.objno IN (SELECT newtab.objectno FROM newtab);

I have used a subquery here, join to the aq$ can work as well.

edited Feb 4, 2019 at 7:57

answered Feb 4, 2019 at 4:28

vmaroli

62310 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pramod Over a year ago

I did consider this option. But according to the business logic and requirement, which I cannot share here due to confidentiality and other restrictions, the query has to be executed on one objectno at a time. The original query has a logic built that would exclude certain object numbers. So using the logic you provided would exclude all the objectno. I'm sorry I should've included this in my question. Will update it.

vmaroli Over a year ago

Instead of =, in should be IN. I have corrected that

vmaroli Over a year ago

From your comments & edited question, for me it looks like the filtering is not just based on objectno, but more than that. So have you considered writing a PL/SQL procedure for this ?

Ted at ORCL.Pro · Accepted Answer · 2019-02-04 12:00:31Z

0

Reading the comments and all I think you need to enhance your Excel with 2 additional columns and load to a new table.

IN can be used in the following way too:

SELECT    
    a.objno,
    a.table_comment,
    b.queue_comment
FROM
    aq$_queue_tables a 
JOIN 
    AQ$_QUEUES b ON a.objno = b.table_objno
WHERE
    (a.objno,a.table_comment,b.queue_comment) IN  (19551,'something','something');

so with the new table will be:

 WHERE
        (a.objno,a.table_comment,b.queue_comment) IN 
        (select n.objno, n.table_comment, n.queue_comment from new_table n)

answered Feb 4, 2019 at 12:00

Ted at ORCL.Pro

1,6121 gold badge11 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to run a sql query multiple times and combine the results into single output?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related