Looking for ideas on how to store returned data for large SQL queries.

Below I have two different queries. The first one is for multiple column
values across multiple rows. The second one is to only return one value
across multiple rows. Both Queries work in returning the data, but not
sure how to set the first example to a value / node set to be consumed
by the driver.


EXAMPLE 1)
<do-set-local-variable disabled="true" name="sqlstatement"
scope="driver">
<arg-node-set>
<token-xml-parse>
<token-text xml:space="preserve">&lt;input
xmlns:jdbc="urn:dirxml:jdbc">
&lt;jdbc:statement jdbc:type="query">
&lt;jdbc:sql>SELECT EMPL_RCD, PK_EMPLID, UID_IAM, EFFDT,
TERMINATION_DT,JOB_DESCR, MV_JOB_FUNCTION, MV_JOBCODE, RR_STATUS_DESCR,
JOB_DESCR, BUSINESS_TITLE, PAYGROUP, US_SOC_CD, EMPL_TYPE,
BENEFIT_PROGRAM, US_SOC_CD_DESCR, DEPTNAME FROM sysadm.PS_RR_IAM_VW
WHERE PK_EMPLID = </token-text>
<token-attr name="RREmplID"/>
<token-text xml:space="preserve">&lt;/jdbc:sql>
&lt;/jdbc:statement>



EXAMPLE 2)

<do-set-local-variable name="sqlstatement" scope="driver">
<arg-node-set>
<token-xml-parse>
<token-text xml:space="preserve">&lt;input
xmlns:jdbc="urn:dirxml:jdbc">
&lt;jdbc:statement jdbc:type="query">
&lt;jdbc:sql>SELECT MV_JOBCODE FROM sysadm.PS_RR_IAM_VW WHERE
PK_EMPLID = </token-text>
<token-attr name="RREmplID"/>
<token-text xml:space="preserve">&lt;/jdbc:sql>
&lt;/jdbc:statement>
&lt;/input></token-text>


Here is a snippet of the data returned from the first example with
multiple columns. Notice in this instance there is only one row.
However, there are multiple job records and job codes for a given user
across multiple rows, so we are wanting to capture all of the data
within a given row into a node set that can contain each of the
different values.

<output>
<jdbc:result-set event-id="0" jdbc:number-of-rows="1">
<jdbc:row jdbc:number="1">
<jdbc:column jdbc:name="EMPL_RCD" jdbcosition="1"
jdbc:type="java.sql.Types.NUMERIC">
<jdbc:value>0</jdbc:value>
</jdbc:column>
<jdbc:column jdbc:name="PK_EMPLID" jdbcosition="2"
jdbc:type="java.sql.Types.VARCHAR">
<jdbc:value>401358</jdbc:value>
</jdbc:column>
<jdbc:column jdbc:name="UID_IAM" jdbcosition="3"
jdbc:type="java.sql.Types.VARCHAR">
<jdbc:value>401358-0-2014-06-28</jdbc:value>
</jdbc:column>
<jdbc:column jdbc:name="EFFDT" jdbcosition="4"
jdbc:type="java.sql.Types.TIMESTAMP">
<jdbc:value>2014-06-28 00:00:00.0</jdbc:value>
</jdbc:column>
<jdbc:column jdbc:name="TERMINATION_DT" jdbcosition="5"
jdbc:type="java.sql.Types.TIMESTAMP"/>
<jdbc:column jdbc:name="JOB_DESCR" jdbcosition="6"
jdbc:type="java.sql.Types.VARCHAR">
<jdbc:value>Public Safety Officer I</jdbc:value>
</jdbc:column>
<jdbc:column jdbc:name="MV_JOB_FUNCTION" jdbcosition="7"
jdbc:type="java.sql.Types.VARCHAR">
<jdbc:value>TEC</jdbc:value>
</jdbc:column>
<jdbc:column jdbc:name="MV_JOBCODE" jdbcosition="8"
jdbc:type="java.sql.Types.VARCHAR">
<jdbc:value>0767</jdbc:value>
</jdbc:column>
<jdbc:column jdbc:name="RR_STATUS_DESCR" jdbcosition="9"
jdbc:type="java.sql.Types.VARCHAR">
<jdbc:value>Active</jdbc:value>
</jdbc:column>
<jdbc:column jdbc:name="JOB_DESCR" jdbcosition="10"
jdbc:type="java.sql.Types.VARCHAR">
<jdbc:value>Public Safety Officer I</jdbc:value>
</jdbc:column>


With the first query, with multiple columns being searched, we see the
output of the node set having only the first columns value, the
EMPL_RCD. But we miss all of the other column values. I would prefer a
node set contain all of the values for a given row to allign the data
for a given EMPL_RCD on a given row.

Thanks,
Fred


--
fp_IDMWORKS
------------------------------------------------------------------------
fp_IDMWORKS's Profile: https://forums.netiq.com/member.php?userid=9869
View this thread: https://forums.netiq.com/showthread.php?t=57022