How to Batch 100 calls through tXMLMap

[Anonymous]

I have the above Job which will pick up more than 1000 rows from the database every night, the Soap service only accept 100 rows at a time, how should I perform this?

Your help is appreciated.

[Anonymous]

The easiest way to do this is to work out how you can limit your DB query to 100 rows of data. You might have an ID or a key somewhere else in the data set. You need to work out what you will use then add a new DB component before your Main DB component. Use this new DB component to simply return your Key values. So, for example, if your key is an Id and the ids are like below....

ID

1

2

3

4

5

6

7

8

9

10

....and you want to only return records in sets of 2, you could set your new query to simply return a 'from' and 'to' value using a query similar to this (pseudo code based on SQL Server)....

 select 
 ID-ID%2 as 'from',
 ID-(ID-1)%2 as 'to'
 from MyTable
 where ID%2=1

This will return something like below....

From To

1        2

3        4

5        6

7        8

9        10

Then connect your new DB component to a tFlowToIterate component and this to your Main DB component. All you need to do then is use the 'to' and 'from' values (stored in the globalMap) in the WHERE clause of your Main query.

What will happen is that your Main query will be run for every row returned by the query used to return your key (grouping) data. This will mean that the tXMLMap code will be run for each of these key sets. 

[Anonymous]

I'll try your approach and will let you know.

Thanks!

[Anonymous]

Unfortunately, there is no way to group the data I have in a sequential order.

Is there any other way I can do this? 

[Anonymous]

You need to group the data to do this. There is always a way to group data in SQL. Have you tried using a With Clause to construct a data structure with an id field? 

[Anonymous]

I think I'm doing a good progress, I'm getting a null exception, I have the below in my where clause, is it correct to use FirstIndex and SecondIndex in my Where clause?

SELECT ROW_NUMBER() OVER (ORDER BY PRSN_ID) AS ROW_NUM, WORK_EMAIL, PRSN_ID, FIRST_NAME, LAST_NAME
FROM MYREPORTS.MYTABLE
WHERE ACTIVE_EM = 'Y'
AND DATE_TS > TIMESTAMP '2018-12-03 03:11:13.000000'

QUALIFY ROW_NUM >= " + (String)globalMap.get("FirstIndex") + " AND ROW_NUM <=  " + (String)globalMap.get("SecondIndex")

Here is the first query result: 

[Anonymous]

I suspect that your globalMap values are null. There is a SQL error which shows this (in red).

[Anonymous]

Awesome it works now, it was null-ing because I didn't point the From and To column values to the key element in the tFlowToIterate component below on the screenshot

Now excuse my Math poor skills I'm trying to get this working with 1000 batch elements, how is it going to be?

Appreciate your help!

select 
 ID-ID%2 as 'from',
 ID-(ID-1)%2 as 'to'
 from MyTable
 where ID%2=1

[Anonymous · Accepted solution]

You could try with this relatively simple way of achieving this. The query below will need to be tailored to your table, but it should work...

 With multsOf1000 as (
 select 
 ID-999 'from_id',
 ID 'to_id'
 from MyTable
 where ID%1000=0
 ),

 remainder as (
	Select max(multsOf1000.to_id)+1 'from_id',
	max(batch.id) 'to_id'
	From multsOf1000, MyTable
 )

 select *
 from (
 select * 
 from multsOf1000
 
 UNION ALL

  select * 
 from remainder) tmp
 order by tmp.from_id

I can't guarantee that this will be super efficient, but essentially it is returning the multiples of 1000 in the first section. Then it is adding on the remainder record (the records after the last multiple of 1000). You may need to tweak this, but it should give you an idea of how to achieve this sort of thing. I am sure there are much better ways of achieving this, but this was the first that came to mind.

[Anonymous]

In Teradata I tried the below query:

WITH ID(ROW_NUM) AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY PRSN_ID) AS ROW_NUM
    FROM MyTable 
    WHERE ACTIVE_IND = 'Y' 
    GROUP BY PRSN_ID)

SELECT 
   CASE WHEN ROW_NUM = 1 THEN 0 ELSE SecondIndex-999 END AS FirstIndex,   (ROW_NUM+1)/2 * 1000 AS SecondIndex
   FROM ID
   WHERE ROW_NUM MOD 2=1

It's still not working well, but I'll continue looking into it.

Thanks!