SQL quering - how to use results of one query in the following one

[unbeliever]

Dear All,

Looking forward to your help regarding issue described below.

I need to:
- query database for specific results
- select value of one of the columns for one particular record
- use this value in the next query.

I am connecting to Oracle 10 DB and example of queries I need to run are below:

=====
1. Get the order ID by execute:
select * from v_order where USERID_REQUESTOR = ‘TESTXXX’
Note the ordered in ‘WKF_ORDER_ID’.

Expected outcome:
The Order ID ‘WKF_ORDER_ID’ is displayed for the order where COMPLETION_TIME is today.

2. With the noted OrderID (‘WKF_ORDER_ID’), execute following statement:
select * from taudit where USERID_USR = ‘TESTYYY’ and AUDITSINGLEID = ‘< orderid>’
Make sure there is a ‘IPS’ entry in the request component.

Expected outcome:
In the audit table is a ‘IPS’ entry in the request component.
=====


Can you please provide me with methods and sample code for such operation?

Thanks in advance

[Saket]

what is issue you are facing with this?
have you tried anything so far? paste your code here

[unbeliever]

I can run only simple queries using objDB.Execute() procedure.

One of the solutions I am considering is building complex sql query, but that is not appreciated.

I am interested how to input results of one execute procedure into succeeding procedure and to execute it with this input. In other words I want to build a connector and I have no idea so far how to do it.

If you can point me to some specific manual dedicated to this topic that would be greatly appreciated.

[Saket]

[unbeliever]

Thanks for advice

I will do further research on ADODB

[unbeliever]

I have done some further reading and the result is as follows.

Code:

Dim oConn Dim oRecSet Dim strQuery Set oConn = CreateObject("ADODB.Connection") Set oRecSet = CreateObject("ADODB.Recordset") strQuery = "Select * from somewhere where location = 'Moon'" oConn.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;" oRecSet.Open strQuery, objConn

I need to select a value for a column 'Mountain' for the one record that is generated for this query and then move it to QTP Data Table. Let's call this value BigX

Afterwards I need to get results of query "Select * from elsewhere where population = 'BigX'". BigX here is the value from QTP Data Table, not DB, in other words variable dependent on previous query.

Can you please provide me with some sample code for this case?

[unbeliever]

Hello,

Does silence mean that I asked something stupid or something impossible?

I beg for your answer.

[unbeliever]

I have unworking solution, perhaps you can advise me how to correct it? I am getting Type Mismatch run error in red row. Perhaps you can help me to indicate exact cell from which I need a value?

Code:

Dim Conn Dim Rs Dim strQuery Set Conn = CreateObject("ADODB.Connection") Set Rs = CreateObject("ADODB.Recordset") strQuery = "Select column from table where USERID = 'testuser" Rs.CursorType = 1 Conn.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;" Rs.Open strQuery, Conn Recordcount = Rs.RecordCount DataTable.GlobalSheet.AddParameter "column","" While (NOT Rs.EOF) For i=1 to Recordcount DataTable.SetCurrentRow(i) DataTable("WKF_ORDER_ID",DtGlobalSheet) = Rs.Fields("WKF_ORDER_ID") Rs.MoveNext Next Wend Conn.close Set Conn = Nothing Set Rs = Nothing Dim Conn2 Dim Rs2 Dim strQuery2 Set Conn2 = CreateObject("ADODB.Connection") Set Rs2 = CreateObject("ADODB.Recordset") [color=#FF0000]strcolumn DataTable.Value("column")[/color] strQuery2 = "select * from table2 where USERID_R = 'test2' and AUDITCOLUMN = '" & strcolumn & "'" Rs2.CursorType = 1 Conn2.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;" Rs2.Open strQuery2, Conn2 Recordcount2 = Rs2.RecordCount DataTable.GlobalSheet.AddParameter "REQUEST_COMPONENT", "" While (NOT Rs.EOF) For i=1 to Recordcount2 DataTable.SetCurrentRow(i) DataTable("REQUEST_COMPONENT",DtGlobalSheet) = Rs.Fields("REQUEST_COMPONENT") Rs.MoveNext Next Wend Conn.close Set Conn = Nothing Set Rs = Nothing

[Saket]

i think, you are missing a "=" there in your statement in red.
btw where to set values to 'column' parameter? i can see you are settign it in WKF_ORDER_ID with first query result.

[unbeliever]

Sorry I have pasted here a mixed version - changed some authorization details, I have tried "=" and the subsequent Run Error was generated. Please see row in red. Now everything will look consistent. As for Run Error it states that "object is required", sql is parametrized correctly, but cannot pass data to Data Table.

Code:

Dim Conn Dim Rs Dim strQuery Set Conn = CreateObject("ADODB.Connection") Set Rs = CreateObject("ADODB.Recordset") strQuery = "Select wkf_order_id, completion_time from v_order where USERID_REQUESTOR = 'ILMT12' and SERVICE_NAME = 'Mailbox'" Rs.CursorType = 1 Conn.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;" Rs.Open strQuery, Conn Recordcount = Rs.RecordCount DataTable.GlobalSheet.AddParameter "WKF_ORDER_ID","" While (NOT Rs.EOF) For i=1 to Recordcount DataTable.SetCurrentRow(i) DataTable("WKF_ORDER_ID", DtGlobalSheet) = Rs.Fields("WKF_ORDER_ID") Rs.MoveNext Next Wend 'DataTable.Export "C:\results.xls" Conn.close Set Conn = Nothing Set Rs = Nothing Dim Conn2 Dim Rs2 Dim strQuery2 Set Conn2 = CreateObject("ADODB.Connection") Set Rs2 = CreateObject("ADODB.Recordset") WKF_ORDER_ID = DataTable.GlobalSheet.GetParameter("WKF_ORDER_ID").ValueByRow(1) 'strWKF_ORDER_ID DataTable.Value("WKF_ORDER_ID") strQuery2 = "select REQUEST_COMPONENT from taudit where USERID_USR = 'ILMT21' and AUDITSINGLEID = '" & WKF_ORDER_ID & "' and userid_actor = 'ILMT12'" Rs2.CursorType = 1 Conn2.Open "Driver={Microsoft ODBC for Oracle};Server=myserver.com;Uid=user;Pwd=pwd;" Rs2.Open strQuery2, Conn2 'strQuery2 "select * from taudit where USERID_USR = 'ILMT21' and AUDITSINGLEID = '" & WKF_ORDER_ID & "'" Recordcount2 = Rs2.RecordCount DataTable.GlobalSheet.AddParameter "REQUEST_COMPONENT", "" [color=#FF0000]While (NOT Rs.EOF)[/color] For i=1 to Recordcount2 DataTable.SetCurrentRow(i) DataTable("REQUEST_COMPONENT",DtGlobalSheet) = Rs.Fields("REQUEST_COMPONENT") Rs.MoveNext Next Wend Conn.close Set Conn = Nothing Set Rs = Nothing

---

Please don't bother problem resolved.

Why can't I edit or delete my posts, by the way?