Powershell finalizing the end of an array.

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

$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"

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 88

oBdACommented: 2017-04-02

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:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

ZackGeneral IT Goto GuyAuthor Commented: 2017-04-02

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.

LVL 71

Chris DentPowerShell DeveloperCommented: 2017-04-03

You just need a small tweak to oBdA's example.

$phextension = ($Results | ForEach-Object {
	$_.Split(',')[0]
}) -join "','"
$phextension = "'" + $phextension + "'"

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 GuyAuthor Commented: 2017-04-03

Thank you very much for your assistance.

Powershell finalizing the end of an array.

Who is Participating?