cancel
Showing results for 
Search instead for 
Did you mean: 

Sender JDBC Select Query

former_member184619
Active Contributor
0 Kudos

Hi Folks,

I have couple of requirements, where I am fetching data from DB2 database. Stored procedure option not available to me.

1. I am fetching PO data from a single table (header repeated + line item) and passing to IDOC.

Query - How to restrict data to fetch data for one PO header and corresponding line items at a time.

2. I am fetching statistical data  (monthly basis - more than 50K records) and passing to proxy. It is having same structure as above.

     Query - How to restrict to fetch unique combinations (ex. 100 PO and corresponding line items) or shall I go by 1 record at a time.

Any optimal solution is welcome.

Regards,

Sachin Dhingra

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member184720
Active Contributor
0 Kudos

>>>1. I am fetching PO data from a single table (header repeated + line item) and passing to IDOC.

Query - How to restrict data to fetch data for one PO header and corresponding line items at a time.

Ideally this is something your DB guys should assist you with as this can only be done via a select query.


However based on my understanding, you could try something like below -


select * from table where PO_number in (select distinct PO_number from tablename where ROWNUM <=1 and status = 'N' order by PO_number) ;


You can use the ROWNUM function to restrict the resultset.


I believe you can the same for your second requirement too.


http://www.toadworld.com/platforms/ibmdb2/w/wiki/7705.rownum-function.aspx


If this is not what you are looking for, i would request you to share some sample data and expected resultset

former_member184619
Active Contributor
0 Kudos

Hi Hareesh,

my database is DB2 so, ROWNUM didn't work. Although, what I have understood from the query is that it well fetch me all the distinct PO's in one JDBC call. Ex. 5 different PO's in one call.

Here is an example:

I need to select all Legacy_num '000003172' in first call and next set in corresponding call.

Regards,

Sachin Dhingra

former_member184720
Active Contributor
0 Kudos

>>>my database is DB2 so, ROWNUM didn't work.

Are you sure? The link i shared corresponds to DB2 itself.

>>>what I have understood from the query is that it well fetch me all the distinct PO's in one JDBC call. Ex. 5 different PO's in one call.

No. One PO in one call.  As we set the rownum <=1 so only one PO will be returned.

If rownum works then you should get the expected result set.

former_member184619
Active Contributor
0 Kudos

>>>my database is DB2 so, ROWNUM didn't work.

Are you sure? The link i shared corresponds to DB2 itself.

Thank you Hareesh.

But, It gives me an error, Column or global variable ROWNUM not found. However, when I have try a select query with row_number() over() . I was able to find the row number.

Let me check, with DB guys if they can help, as link suggest "The compatibility vector must be set to 0x01 to enable this feature."

Regards,

Sachin Dhingra