CODEWITHSUNDEEP
arraysoracle-databasepowershellforeachscripting
billhubb84
billhubb84

Reputation: 195

PowerShell ForEach loop returning only One Result

I feel like perhaps I am overlooking something simple here, but I am having trouble with a ForEach loop in PowerShell actually returning all items that I expect. I have a script that will query an Oracle database and gather up the base data set. Once this is gathered, I will need to perform some adjustments to what is returned and build an additional bit of information (not in the script currently, working through the basics so far)

What I am doing is adding the data to an array, then trying to use a ForEach loop to examine each item in the array and pump that data out to another array that will have the new properties that I need to populate based on some calculations of the base data set. What I am getting returned to the variable $finaloutput is only one line of the data (for the example I am posting here I simply look for one reportnumber equal to CPOD-018, which there are 5 of in the data set with varying other properties populated including the sitename which I am populate as well, but still only get one result).

I've tried going about this using nested if statements within the ForEach loop instead of the piped Where-Object, but received the same results. Below is the current version of the script, any assistance would be greatly appreciated.

param(
    [parameter(mandatory=$True)]$username,
    [parameter(mandatory=$True)]$password
    )

# setup the finaloutput variable
$finaloutput = New-Object psobject

$finaloutput | Add-Member -MemberType NoteProperty -name ReportNumber -value NotSet
$finaloutput | Add-Member -MemberType NoteProperty -name sitename -value NotSet

# the connection string to be used by the OlEDB connection
$connString = @"
    Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="host.host.host")(PORT="1521"))
    (CONNECT_DATA=(SERVICE_NAME="name.name.name")));User ID="$username";Password="$password"
"@

# the query that will be used to gather data from Oracle
$qry= @"
    select VP_EXPECTED_DETAILS.REPORTNUMBER, VP_EXPECTED_DETAILS.SITE_NAME, VP_ACTUAL_FILENAME_DETAILS.FILE_NAME, VP_EXPECTED_DETAILS.MAX_EXPECTED_LOAD_TIME, VP_EXPECTED_DETAILS.EXPECTED_FREQUENCY, 
    VP_EXPECTED_DETAILS.DATE_TIMING, VP_EXPECTED_DETAILS.FREQUENCY_DAY, VP_EXPECTED_DETAILS.JOB_NO, 
    TO_CHAR(VP_ACTUAL_RPT_DETAILS.GEN_PARSE_IN,'YYYYMMDDHH24MISS') AS GEN_PARSE_IN, 
    TO_CHAR(VP_ACTUAL_RPT_DETAILS.GEN_PARSE_OUT,'YYYYMMDDHH24MISS') AS GEN_PARSE_OUT, 
    TO_CHAR(VP_ACTUAL_RPT_DETAILS.ETLLOADER_IN,'YYYYMMDDHH24MISS') AS ETLLOADER_IN, 
    TO_CHAR(VP_ACTUAL_RPT_DETAILS.ETLLOADER_OUT,'YYYYMMDDHH24MISS') AS ETLLOADER_OUT

    from MONITOR.VP_EXPECTED_DETAILS

    LEFT JOIN MONITOR.VP_ACTUAL_RPT_DETAILS on VP_EXPECTED_DETAILS.REPORTNUMBER = VP_ACTUAL_RPT_DETAILS.REPORTNUMBER and VP_EXPECTED_DETAILS.SITE_NAME = VP_ACTUAL_RPT_DETAILS.SITE_NAME

    LEFT JOIN MONITOR.VP_ACTUAL_FILENAME_DETAILS on VP_ACTUAL_RPT_DETAILS.FNKEY = VP_ACTUAL_FILENAME_DETAILS.FNKEY where VP_EXPECTED_DETAILS.EXPECTED_FREQUENCY = 'DAILY' or 
    (VP_EXPECTED_DETAILS.EXPECTED_FREQUENCY = 'MONTHLY' AND VP_EXPECTED_DETAILS.FREQUENCY_DAY = EXTRACT(DAY from SYSDATE))
"@

# the function that will open the database connection and execute the query
function Get-OLEDBData ($connectstring, $sql) {
    $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
    $OLEDBConn.open()
    $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
    $readcmd.CommandTimeout = '300'
    $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
    $dt = New-Object System.Data.DataTable
    [void]$da.fill($dt)
    $OLEDBConn.close()
    return $dt
}

# populate $output with the data from the Get-OLEDBData function
$output = Get-OLEDBData $connString $qry

# build the final output that will generate alerts
ForEach ($lines in $output | Where-Object {$_.reportnumber -eq "CPOD-018"})
    {
        $finaloutput.reportnumber = $lines.reportnumber
        $finaloutput.sitename = $lines.SITE_NAME
    }

$finaloutput

Upvotes: 0

Views: 6920

Answers (1)

Noah Sparks
Noah Sparks

Reputation: 1762

It looks like what is happening is you are doing the object creation incorrectly. Inside the foreach loop you keep overwriting the same values rather than appending new objects

It should look more like this:

$FinalOutput = ForEach ($lines in $output | Where-Object {$_.reportnumber -eq "CPOD-018"})
    {
        $Prop = @{
        'reportnumber' = $lines.reportnumber
        'sitename' = $lines.SITE_NAME
        }
        New-Object -Type PSObject -Property $Prop
    }
$FinalOutput

You would need to comment out the finaloutput lines earlier in your script.

Upvotes: 2

Related Questions