Powershell finalizing the end of an array.

Zack
Zack used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Don't concatenate yourself; that's what the -join operator is for.
Replace lines 38-48 in your script above with the following; it will create an array with the extracted phone extensions, and then just join it:
$phextension = ($Results | ForEach-Object {
	$_.Split(',')[0]
}) -join ','

Write-Host "phextension: $phextension"

$sql2 = "select encntr_id 
from encntr_alias ea 
WHERE ea.alias IN ($($phextension))
and ea.alias_pool_cd = 11"

Open in new window

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
Top Expert 2010
Commented:
You just need a small tweak to oBdA's example.
$phextension = ($Results | ForEach-Object {
	$_.Split(',')[0]
}) -join "','"
$phextension = "'" + $phextension + "'"

Open in new window

The join operator has been tweaked to join on on a comma surrounded by quotes. All that's left is to add a quote to be beginning and the end of the joined string (which is accomplished by the simple concatenation at the end).
ZackGeneral IT Goto Guy

Author

Commented:
Thank you very much for your assistance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial