How to add Query table for Mysql data source

Hi @Dushyant ,

if you have a query table that outputs a field that can be used in an incremental selection rules like an int or date(time) you can make that query table load incrementally.

There are two prerequisites:

• set a primary key for your Query Table (you cannot load incrementally without one). You can use the Override function to do this
• set up an incremental selection rule that picks up the field

Primary key on SalesOrderDetail Query Table:

Incremental selection rule picks up the query table:

In the DWH you will see an Incremental mapping and the second reload will show an empty Raw table (static data in Adventureworks):

Thanks @rory.smith , But the problem for me is that the query table option inside the “data source setting” is disable. Hence i am unable to add query table. What i mentioned earlier was in query “tool” i am able to case the column which is varchar to date.

Dear @Dushyant ,

I tink you don't need these option because you can put the casts / converts in the Query Table.

you do need to write this in mysql instead of T-SQL.

Hope this helps.

= Daniel

Hi @Dushyant ,

given that that screenshot says “Discovery Hub”, that would be TimeXtender 17.x? Given that that is many years old, I can't really remember the exact possibilities.

I suspect in those cases you cannot set Primary Keys on query table fields though. To get query tables working Perhaps you should then approach the MySQL database through ODBC, OleDB or Anysource - one of will probably work.

You may want to focus on getting closer to a recent release though.

Dear @Dushyant ,

In this version you need to set up the query table first. Cast the varchar to date(time). Synchronize the data source and select the created table to be loaded. In the ODX, set the Primary Key on a field by right clicking on the field and selecting add to primary key.

Then, in the data source table, select the query table - right click - add incremental selection rule. Select the datetime.

This should work, also in your version. I do agreee with Rory that it might be a good thing to upgrade to a more recent version.

= Daniel

Hi @Dushyant 

It would seem like you are using our MySQL provider.

Years ago I wrote a guide about how to convert this to a AnySource ADO.Net version instead.

The benefit being that you can do queries like this in that.

https://legacysupport.timextender.com/hc/en-us/articles/115005949443-How-to-change-a-MySQL-ODBC-type-data-source-to-a-Any-Source-ADO-MySQL-data-source

Hi @Dushyant is the issue resolved? If so please help us by marking a best answer above. Please let us know if you have any follow up questions