Zack
asked on
Powershell finalizing the end of an array.
Hi EE,
I have the following code:
The code results in the following error see attached.
The error is caused the last value of the phone extension field having a ',' in it. How do I determine what the final value is in the array I am concatenating into the SQL string I am parsing so I can remove the last '.'
Any assistance is appreciated.
Thank you.
Error.PNG
I have the following code:
$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 = 32866515
and elh.loc_facility_cd = 11664
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()
$Results = @()
While ($reader.Read())
{
Write-Host 'Reading from reader1' -fore Yellow
Write-Host $temp
$temp = ''
$temp += $( $reader.GetString(0)) + ', '
$temp += $( $reader.GetString(1)) + ', '
$temp += $($reader.GetString(2))
$Results += $temp
}
Write-Host "Results: $Results"
$phextension = ''
$Results | ForEach-Object {
$phextension += $_.Split(',')[0] + ','
}
Write-Host "phextension: $phextension"
$sql2 = "select encntr_id
from encntr_alias ea
WHERE ea.alias IN ($([string]$phextension))
and ea.alias_pool_cd = 11"
Write-Host "phextension: $sql2"
$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql2,$conn)
$reader2 = $command.ExecuteReader()
$QueryResult2 = ''
while($reader2.Read()) {
Write-Host 'Reading from reader2' -fore Green
$QueryResult2 += $reader2.GetString(0)
}
Write-Host $QueryResult2
$conn.Close()
The code results in the following error see attached.
The error is caused the last value of the phone extension field having a ',' in it. How do I determine what the final value is in the array I am concatenating into the SQL string I am parsing so I can remove the last '.'
Any assistance is appreciated.
Thank you.
Error.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for your assistance.
ASKER
Thank you for that an additional query if I may if I wanted to put quotes around each of the numbers in the array e.g. '6747' is there a command for that?
Cheers.