I need a dynamical Query SQL Statement in JDBC-Channel ?

[Former Member]

Hi Gurus,
My scenario looks ist:
MS SQL Database -> JDBC ->PI 7.31 -> IDOC PORDCR -> SAP Retail
The data structure on database is similar as below:

NO  Vendor     Orderdate     Item     Quantity     Unit     Satus
1     100        01.07.2013      item01      10       PCE      0
1      100       01.07.2013      item02      10       PCE      0
1      100       01.07.2013      item01      20       PCE      0
2      100       10.07.2013      item01      10       PCE      0
2      100       10.07.2013      item02      15       PCE      0
3      200       01.07.2013      item01      10       PCE      0
3      200       01.07.2013      item03      5         PCE      0
3      200       01.07.2013      item02      8         PCE      0
….

The lines with same NO would be created to an IDOC, i.e. the firs lines with NO=1 would be created to an IDoc and the 4. Line and 5. Line would be 2. IDoc. … since they have the same header information: Vendor and Order date.
I can import all data on one shoot in PI, then make a n:m mapping. But I do not think it’s a good idea. Normally there are more than 20,000 lines to be imported. The message would be too big to PI and not easy to monitor and find error.
ccBPM I also do not prefer to take.
Could anyone give me a magic Query SQL Satement, with which I can select data dynamically. It means, the data in database would be selected in batches according to the NO.,
Something like: select * from tablename where NO= ? and Status ='0'; UPDATE tablename SET Status = '1' where Status ='0'
Or whether  data can be selected in a loop?

Or i must realise this with stored presedure or a module in CC or a java extension in JDBC-driver? If so, please give me more details.

Thanks a lot!

Regards

Rene

[Former Member · Accepted solution]

Did you try with a subquery where you each time select a set of the records with the lowest NO.

select * from tablename where Status ='0' and NO in (select min(NO) from tablename where Status ='0')
UPDATE tablename SET Status = '1' where Status ='0' and NO in (select min(NO) from tablename where Status ='0')

[Former Member · Accepted solution]

Hi Martin,

thank you very much for your promt reply!

your statement selects one row.

How can I select top 50 NO.?

I tried select top (50) * from tablename ...

but i get just the top 50 rows. what I want is all rows with top 50 NO.

Could you give me any tipps?

thanks!

Rene

[Former Member · Accepted solution]

could you be more specific?

select top 50 * from tablename always gets the TOP 50 rows. I think you want to get the TOP 50 different NO, or something like that, don't you?

Cheers,

Edu

[rajasekhar_reddy14 · Accepted solution]

http://www.techonthenet.com/oracle/questions/top_records.php

[Former Member · Accepted solution]

your statement selects one row.

Can you please post the exact query you are using? Which database is it?

The query works like this (given your example):

1) the subquery (select min(NO) from tablename where Status ='0') returns '1'

2) then the main query is interpreted like select * from tablename where Status ='0' and NO in ('1') which returns the first 3 records

[Former Member]

Hi,

I got a perfect SQL Query from a colleague as below:

SELECT      *

      FROM  tablename

      WHERE NO in (

            SELECT DISTINCT TOP 10 NO

                  FROM  tablename

                  WHERE Status = 0

                  ORDER BY NO)

      ORDER BY NO

UPDATE tablename SET Status = 1 where NO in (select Distinct top 10 NO from tablename where Status = 0 order by NO)

It delievers all rows with top 10 NO, i.e.

first time          rows of NO from 1 to 10

second time     rows with NO from 11 to 20.

...

What I must pay attention is the time interval of JDBC sender channel. say, every 180 seconds executed,

A further test would be taken.

Thank u for your input!

Rene

[ambrish_mishra]

Hi Rene,

I understand that you want to read records in chunks and also pick up records related to the same order in those chunks based on NO field.

This is not possible through an SQL query. You can ask the database team to update the records in  staging table in batches and make sure the JDBC adapter polls in tandem and pick up the records and marks them as red.

Few more observations:

• you won't need a n:m mapping for your scenario. You need to just change the IDoc(I assume ORDERS03 or 05) schema to 1..unbounded and then from the same source message, you can create multiple IDocs by using standard Node functions.
• 
  Just need to make sure the records are updated in database (grouped by NO) which is simple statement in SQL.
• I don't think 20k records are too much. Even if there is an issue, you can take chunks and debug the issue out. Most issues are ironed out in Testing cycles so it should be clean in production.

Hope it helps!

Ambrish

[rajasekhar_reddy14]

1)You have to build select query to pick up 1000 records at a time(chose right number) .

2)You can build mapping logic to sort based on NO value then create IDocs.

3)Change IDoc occurance to 1 to unbounded.