on 05-06-2015 8:08 PM
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
>>>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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
>>>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.
>>>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
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.