how to set powershell variables from multiple column in sql query

Question

I have the following query that I run in powershell:

$Query = "SELECT 
             t1.BSM_NM
            ,t1.D_DTM AS MAXDATETIME
            ,CASE
                WHEN SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_BLOCK) AND SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_BLOCK)
                     OR
                     SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_DROP) AND SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_DROP)
                     OR
                     SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.MAJOR_VOICE_AXSFAIL) AND SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) < MAX(t2.CRITICAL_VOICE_AXSFAIL)
                THEN 1
                WHEN SUM(t1.V_CUST_BLK_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_BLOCK)
                     OR
                     SUM(t1.V_DRP_CALL_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_DROP)
                     OR
                     SUM(t1.V_AXS_F_CNT)/SUM(t1.V_ATT_CNT) >= MAX(t2.CRITICAL_VOICE_AXSFAIL)
                THEN 2
                ELSE 0
             END MAJORCRITICAL
            FROM DMSN.DS3R_FH_1XRTT_BTS_LVL_KPI t1
            INNER JOIN
            ZDMSN.DS3R_1XRTT_TRIGGERS_THRESHOLD t2
            ON
            t1.BSM_NM = t2.BSC_NM
            WHERE t1.BSM_NM = 'ARL1' and t1.D_DTM = (SELECT MAX(D_DTM) FROM DS3R_FH_1XRTT_BTS_LVL_KPI WHERE BSM_NM = 'ARL1')
            GROUP BY
            t1.BSM_NM, t1.D_DTM"      

$data_set = new-object system.data.dataset
$adapter = new-object system.data.oracleclient.oracledataadapter ($Query, $Connection)
[void] $adapter.Fill($data_set)
$table = new-object system.data.datatable
$table = $data_set.Tables[0]

but how would I set BSM_NM, MAXDATETIME, and MAJORCRITICAL as variables in powershell. I want to eventually use them in a email further down the script.

It is not clear what you need to do. Do you want to replace BSM_NM etc with their values? — ravikanth
– ravikanth, Commented May 22, 2013 at 14:58
no, the query returns one row of data. I want to set variables for the as the values in BSM_NM, MAXDATETIME, and MAJORCRITICAL — lightweight
– lightweight, Commented May 22, 2013 at 15:06

Bruce · Accepted Answer · 2013-05-22 15:26:18Z

2

For 1 row try this

$bsmNM = $data_set.Tables[0].Rows[0].BSM_NM
$maxDT = $data_set.Tables[0].Rows[0].MAXDATETIME
$majorC = $data_set.Tables[0].Rows[0].MAJORCRITICAL

answered May 22, 2013 at 15:26

Bruce

1,6231 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to set powershell variables from multiple column in sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related