FetchWhere API - possible to create a join table request?

spoudret

In the FetchWhere query, is this possible to create a join table request?

Right now, I'm using (from Postman test query)

{

"CompanyNumber": 1,

"Operator": "******",

"TableName": "wave",

"WhereClause": "co_num='1' and batch= 259809 ",

"BatchSize": 0,

"RestartRowID": ""

}

I would like to create a join on wave.batch = cartonmst.batch

Complementary question:

Any URL on FetchWhere documentation?

Thanks for any help you can provide!

Find more posts tagged with

ION API Gateway

Accepted answers

All comments

Brandon Watson

Hello! I assume you are talking about CSD/SXe, but could be wrong. If so, the FetchWhere does allow for the use of JOIN. If you are using CloudSuite, you can either use DataLake Compass JOIN query, or the API Flow to expose a document that contains data from two separate API Calls. If you are on-premise, you would be limited to using a custom BOD that you filled in from both API calls.

spoudret

Yes I'm working with CSD/SXe, but I felt that FetchWhere might be used in other environment, hence a more generic question.

I need very "fresh" data (like less than a minute old) and was more or less told that the data lake could be a bit behind. It could be wrong tho.

I do not have a significant knowledge of the API flow. I will dig in that direction as a simple join would save me a few days work.

Thanks a lot for your help and pointers.

Stephen

Legacy Contributor

Since there is a TableName key, I assumed that it only accepts one table. Am I wrong? I am using SXe CSD.

antonio-caria

of course.

Legacy Contributor

@spoudret , where you able to get this working? The previous asnwer was not very clear and did not provided examples. If you did, could you share an example.

We have used the IONSERVICES/datalakeapi/v1/compass/jobs API when getting data from multiple tables but the steps to get data out are much more elaborated than the fetchwhere API.

Legacy Contributor

Hey Jim, Data Lake also has the Compass JDBC driver for a synchronous interface if that's any easier. If you need to use APIs, can you outline your use case? We're looking at introducing a synchronous API for small transaction processing, look-ups, and so forth and would live to hear what you're looking to build around.

antonio-caria

don't use API for Join tables.

The process will be always slow

Legacy Contributor

Then what do you use?

I've found jdbc to be slow too. JDBC is ok to make group by query but not to extract all the fields in a table or a qry with thousands of rows, and compass qry only gives you 100 rows.

We have several excel files that query our current ERP (SQL Based) and with CSD we are struggling to find easy ways to reproduce those reports. We have spent several hours building schemas, workflows, workflow schedules, data flow, API calls to download data to csv files so that we can then create SSIS packages to populate a local SQL.

It is just way too complicated. Having fast easy access to the data for ad-hoc reporting is as important as the functionality of the ERP itself.

Hopeffuly I am just wrong and was not advised correctly on the available tools.

tdouglass

You are correct. WE have the same struggles. Many of our reports have had to be developed in Mongoose

spoudret

Not really, but I have more details:

My understanding is that fetchwhere was a temporary solution to a problem and as many temporary things, they tend to become permanent. It was targeting table only level of data access, and as such can not and will probably never accept joins in a friendly way.

Fetchwhere is on the heavy side of resource consumption, and Infor does not like it very much when you overuse it as we were doing. I was a bit surprise at first but more explanation from their side made it clearer.

So now, we duplicate the data we need toward the datalake, where the JDBC / Compass access gives us more liberty, but the data is a not as fresh as it could be in fetchwhere mainly due to the queued nature of the datalake feed. Note that the data there is rarely written more than 30-40 seconds later, so it's still a good place to get data. Some of it is basically copied on our side before we do real data analysis. copy once and use many times locally.

We now use a mix and match from both sources, fetchwhere for up to the second data, and datalake via compass and JDBC for up to the minute data, copied and used in complex queries locally.

The final answer to the question would be:
duplicate what you need to the datalake and "fish" your data there most of the time, and only if really, really needed, use fetchwhere for very targeted data (very few records sent back) from a single table.

As for getting access to the data from Compass, some wrapper functions made it almost as easy as accessing a regular SQL database from a visual studio application. the way to do it reminds me of the mainframe era, where you submitted a job and had to wait for a finish / fail / timeout message before getting your data (or not) in "pages" of 50 lines each. Yes I'm that old :-)

Legacy Contributor

Hi, Mike, I've switched to using the data fabric v2 api's within ssis and find them very reliable compared to FetchWhere, which is extremely likely to time out. This conversation made me wonder whether views we can now create in Compass can also be referenced in the data fabric calls? That might help with the join questions if so.

Kevin Heiman

The Data Fabric calls for Compass are the same whether JDBC, UI or API. All support using Views with some considerations for certain use cases.

However each will perform the Compass query identically. If it works in one, it works in all.

Legacy Contributor

Hey @spoudret ,

A lot of good and relevant observations. I'll add some extra color here to help expand on some of the footnotes you made.

CSD replicates fairly frequently but the data travels through an eventually-consistent architecture so there could be delays in its arrival to Data Lake. Generally, Data Lake shouldn't be considered a source for real-time feeds if you have serious arrival & availability SLAs.

For that reason, though, we've built a new streaming architecture for Data Fabric and are working with the CSD team (and others) so they can provide true, real-time streams of system and user transactions at least on the data production side. Later this year, we're releasing a new product in Data Fabric called Pipelines that can read from those streams and write those records in real-time to cloud-based data systems - think Aurora, MSSQL, Kinesis, S3, and so forth.

As a product, this helps layer in operational use cases on top of the big data capabilities with Data Lake and everything that travels through a stream is also stored in Data Lake for replay abilities, AI/analytics, and batch processing workloads. If you're going to be down in Hollywood, FL for this year's TUG, make sure to tap @khalid-marbou on the shoulder to chat. He'll be presenting quite a few Data Fabric topics

antonio-caria

of course You should use JDBC.

All API use also JDBC inside - They are Java.

Copyright © 2025 Infor. All rights reserved.