Use Recordset in another query

[BMitch]

I have an Oracle table query that the resulting Recordset could be used in three further queries. Is there a way to reference the first Recordset in another query that also pulls data from Oracle tables.

Example:
Dim Cmd1, Cmd2 As ADODB.Command


Set Cmd1 = New ADODB.Command
Set Cmd1.ActiveConnection = MyConn
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "Select * from Tbl1 where product=abc"
Set Rs1 = Cmd1.Execute()

Set Cmd2 = New ADODB.Command
Set Cmd2.ActiveConnection = MyConn
Cmd2.CommandType = adCmdText
Cmd2.CommandText = "Select * from Rs1, Tbl2 where Rs1.product=Rs2.product"
Set Rs2 = Cmd2.Execute()



I am using Excel 2013 and accessing Oracle tables using InstantClient 11.2. Connecting to the Oracle tables is not an issue, nor is returning the first recordset, but when I attempt the second query I get Run-time error '-2147217865 (80040e37': Automation error.

As I have used this sight to solve many problems, your insights are greatly appreciated!

BMitch

 

[xenou]

Hi,
You should attempt this with a better SQL query, using subqueries. As far as I know, you cannot reference a recordset as a "table". In any case, even it were possible, it is not the name of a table in your database and you certainly cannot use Rs1 as a table name in raw SQL sent to the Oracle server.

SELECT * FROM
 (Select * from Tbl1 where product='abc') As SUB
 INNER JOIN
 (Select * FROM tbl2)
 ON SUB.product = tbl2.product

ξ

 

[BMitch]

xenou,

I do something similar to that in my SQL, but it does not resolve the issue of reuse in Excel. For clarification, I have two other queries that are similar, but use different tables (Tbl3 & Tbl4). This results in reusing the subquery three times, which is what I was attempting to avoid. It seems like an inefficient method and slower than pulling the data once and using it against the three other queries (same as using With As in SQL).

BMitch

Hi,
You should attempt this with a better SQL query, using subqueries. As far as I know, you cannot reference a recordset as a "table". In any case, even it were possible, it is not the name of a table in your database and you certainly cannot use Rs1 as a table name in raw SQL sent to the Oracle server.

SELECT * FROM
 (Select * from Tbl1 where product='abc') As SUB
 INNER JOIN
 (Select * FROM tbl2)
 ON SUB.product = tbl2.product

ξ

 

[Kyle123]

You can't do this, however I think you need the oracle version (whatever that may be) of a table value function. Or you could simply return multiple recordsets from your query having cached the temp table / table variable on the server

 

[xenou]

I do something similar to that in my SQL, but it does not resolve the issue of reuse in Excel. For clarification, I have two other queries that are similar, but use different tables (Tbl3 & Tbl4). This results in reusing the subquery three times, which is what I was attempting to avoid. It seems like an inefficient method and slower than pulling the data once and using it against the three other queries (same as using With As in SQL).

If "rs1" is included in all three queries you should be able to query for the data all at once - possibly with joins but certainly with unions. Whether this would be better performance or not I couldn't say. It's hard to say without knowing particulars.