JBDC Request results
I have a JDBC request that is returning the following: <Results> <ResultSet fetchSize="10"/> </Results> It relies on a DataSource that only returns one row. If I run the DataSource manuall...
Forum Discussion
I can't really send you my project because you won't have the database. This is my SQL statement in the JDBC DataSource:
select party_number
from
(
select *
from apps.hz_parties
sample block (.1)
where 1 = 1
and status = 'A'
and party_number > 0
)
where rownum = 1
This returns just one row (one field also, as you can see) which is then referenced in a JDBC request. partyNumber is set to ${DataSource_JDBC_PD#party_number} and the SQL is:
select HP.party_number
,HP.party_id
,HP.person_first_name
,HP.person_last_name
,HCP.phone_area_code||HCP.phone_number AS PHONE_NUMBER
,HP.email_address
,HP.address1
,HP.address2
,HP.address3
,HP.address4
,HP.city
,HP.county
,NVL(HP.state,HP.province) AS STATE
,HP.country
,HP.postal_code
,HP.party_name
,HP.party_type
from apps.hz_parties HP
,apps.hz_contact_points HCP
where 1 = 1
and HP.party_id = HCP.owner_table_id (+)
and HCP.contact_point_type = 'PHONE'
and HCP.primary_flag = 'Y'
and HCP.status = 'A'
and HCP.owner_table_name = 'HZ_PARTIES'
and rownum = 1
and HP.party_number = :partyNumber
If I run the DataSource manually and set the max rows to 1, I get what I expect. Because it defaults to 10, I need to be able to set that number to 1.
select party_number
from
(
select *
from apps.hz_parties
sample block (.1)
where 1 = 1
and status = 'A'
and party_number > 0
)
where rownum = 1
This returns just one row (one field also, as you can see) which is then referenced in a JDBC request. partyNumber is set to ${DataSource_JDBC_PD#party_number} and the SQL is:
select HP.party_number
,HP.party_id
,HP.person_first_name
,HP.person_last_name
,HCP.phone_area_code||HCP.phone_number AS PHONE_NUMBER
,HP.email_address
,HP.address1
,HP.address2
,HP.address3
,HP.address4
,HP.city
,HP.county
,NVL(HP.state,HP.province) AS STATE
,HP.country
,HP.postal_code
,HP.party_name
,HP.party_type
from apps.hz_parties HP
,apps.hz_contact_points HCP
where 1 = 1
and HP.party_id = HCP.owner_table_id (+)
and HCP.contact_point_type = 'PHONE'
and HCP.primary_flag = 'Y'
and HCP.status = 'A'
and HCP.owner_table_name = 'HZ_PARTIES'
and rownum = 1
and HP.party_number = :partyNumber
If I run the DataSource manually and set the max rows to 1, I get what I expect. Because it defaults to 10, I need to be able to set that number to 1.