troubleshooting Question

Powershell finalizing the end of an array.

Avatar of Zack
ZackFlag for Australia asked on
PowershellSQL
4 Comments2 Solutions148 ViewsLast Modified:
Hi EE,

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
Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros