Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag for Australia

asked on

Powershell - Running two SQL queries in a script, passing the results of the 1st SQL Query into 2nd SQL query.

Hi EE,

I have the following script:

[Reflection.Assembly]::LoadFile("D:\app\icameron\product\11.2.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll")

$userId = ''
$password = ''
$host2 = ''
$port = ''
$serviceName = ''

$constr = "User Id=$userId;Password=$password;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host2)(PORT=$port))(CONNECT_DATA=(SERVICE_NAME=$serviceName)))"

$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)
$conn.Open()
$sql="select ph.extension Extension, to_char(elh.beg_effective_dt_tm,'yyyy/mm/dd hh24:mi:ss') StartDate 
,to_char(elh.end_effective_dt_tm,'yyyy/mm/dd hh24:mi:ss') EndDate 
from encntr_loc_hist elh, phone ph
WHERE elh.encntr_id = 3
and   elh.loc_facility_cd = 1
and   elh.active_ind=1
and   elh.loc_bed_cd = ph.parent_entity_id 
 and   ph.extension(+) is not null 
 and   ph.parent_entity_name(+) = 'LOCATION'
and   ph.phone_type_cd(+) = 160 
 and   ph.end_effective_dt_tm(+) >= elh.end_effective_dt_tm 
 and   ph.beg_effective_dt_tm(+) <= elh.beg_effective_dt_tm"
$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$reader=$command.ExecuteReader()

while($reader.Read()){
    $reader.GetString(0) + ', ' +,
    $reader.GetString(1) + ', ' +,
    $reader.GetString(2)

}
$conn.Close()

Open in new window


My query is how would I pass the results of the reader.GetString(0) into a second query as follows:

$sql= select encntr_id 
from encntr_alias ea 
WHERE ea.alias = GetString(0)
and ea.alias_pool_cd = 116

Open in new window


Running and outputting the SQL query results for each value returned reader.GetString(0).

Any assistance is welcome.

Using Powershell v3.

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Coralon
Coralon
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zack

ASKER

Thank you very much for information, I'll start debugging :)