We help IT Professionals succeed at work.

Powershell finalizing the end of an array.

Zack
Zack asked
on
144 Views
Last Modified: 2017-04-03
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()

Open in new window


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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2019
Most Valuable Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ZackGeneral IT Goto Guy

Author

Commented:
Hi oBda,

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.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ZackGeneral IT Goto Guy

Author

Commented:
Thank you very much for your assistance.