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]

you can explore more on using adodb objects and get the query results in recordset, that will definitely help you.
I have listed a peice of code here,

Code:

Set ConObj = CreateObject("ADODB.Connection")
     ConObj.Provider = "MSDAORA.1"
     ConObj.Properties("Data Source").Value = Service name
   ConObj.Properties("User ID").Value = UserName

    ConObj.Properties("Password").Value = Password
  
    ConObj.Open

   Set RecObj = CreateObject("ADODB.Recordset")

RecObj.Open <<Your SQL query>>ConObj,adLockPessimistic

Hopefully we will have a series of tutorial Posts on the same topic, soon at http://www.learnqtp.com
register you mail there to receive the notification

[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?