JBDC Request results

From what I understand is that the inner query needs to be refined more not get multiple rows. Or did you try with distinct ?
I happened to see this link regarding ROWNUM,
http://www.oracle.com/technetwork/issue ... 86197.html
where you might be interested and see if this somehow helps. Is it possible to use ROWID as well ?
------------------------
Pagination with ROWNUM

My all-time-favorite use of ROWNUM is pagination. In this case, I use ROWNUM to get rows N through M of a result set. The general form is as follows:

select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;

where

FIRST_ROWS(N) tells the optimizer, "Hey, I'm interested in getting the first rows, and I'll get N of them as fast as possible."

:MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you wanted rows 50 to 60 of the result set, you would set this to 60.

:MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.
------------------------

I really appreciate you trying to help me but the issue has nothing to do with the SQL statement in the DataSource. It works as it should.

After the DataSouce, I have 2 Property Transfer steps that work as they should. One passes the value to the GET REST step correctly and the GET REST step returns the correct XML. The other passes the value to the JDBC Request correctly (or so it seems) and the JDBC Request returns incorrect XML. See sample below:

<Results>
<ResultSet fetchSize="10"/>
</Results>

So I'm getting closer. I think that it has something to do with the SQL in the JDBC Request because the :partyNumber variable needs to be in quotes. I'm going to try to figure that out.

I am trying just jdbc query with a parameter like you mentioned, which is fine - no need to have quotes.

:partyNumber

So, this can be ruled out.

So that wasn't it (as you said). Since I had it narrowed down to the JDBC Request and it was working intermittently, I discussed it with our architect (as I have been all along this journey) and it turned out to be my SQL statement in the JDBC Request but not a quote issue. My SQL statement is now:

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
,(
select *
from apps.hz_contact_points
where 1 = 1
and contact_point_type = 'PHONE'
and primary_flag = 'Y'
and status = 'A'
and owner_table_name = 'HZ_PARTIES'
) HCP
where 1 = 1
and HP.party_id = HCP.owner_table_id (+)
and rownum = 1
and HP.party_number = :partyNumber

Without the "contact points" being an in line query, anyone without a primary phone number didn't return any data. So SoapUI was working perfectly/as it should but I wasn't understanding the "messaging".

Thanks so much for your time and effort. It helped narrow it down to the real issue.

This ticket can be resolved.

Is the target in the Property transfer correct? Can you pl try as highlighted in the attachment? And not sure if you may not even set value from Datasource in next Jdbc step.

nmrao wrote:
Is the target in the Property transfer correct? Can you pl try as highlighted in the attachment?

Yes, please see my previous post. You were probably typing when I posted it.

Glad you figured out with your team. Happy weekend

Ok, thanks nrmao for helping out.

Marking this ticket as resolved!

Renato
SmartBear Software