To homepage To homepage
Solved

Use variables on Query tables

  • 15 August 2023
  • 4 replies
  • 114 views
J
Rank

Hi,

I have a project that is using the cdata connector as a datasource, and because of issues with paging and nested call we have created query tables to get the correct results (using queryslicer in the rsd-files).

Because of performance issues the tables have to be setup for incremental loading, but I cannot get this working because I need the incremental value in the call.

Is it somehow possible to use a variable or a field from another table in the Quey tables to get the incremental value?

When using a fixed date the logic is working fine (as shown below) but that's not workable:

    SELECT * 
    FROM [REST].[EventRegistration_Detail]  
    WHERE [file_name] IN 
        (SELECT [Name] 
         FROM [REST].[EventRegistration_List]
         WHERE [Properties.Last-Modified] > '2023-01-01')
  

(using TX 20.10.30.64 with Business Units)

 

Any good suggestions on this?

 

Kind regards,

Jacqueline

icon

Best answer by Thomas Lind 18 October 2023, 11:08

View original

4 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @jacqueline.hofmeijer 

I got this guide in the old support https://legacysupport.timextender.com/hc/en-us/articles/360052383191-Creating-and-using-RSD-files-for-CData-providers#use-parameters

I use a custom data selection rule. You can do this with a dynamic variable as the term.

I have done this as a way to do incremental load. I would pull out the latest max date from the _I version of the table and then use that as a filter in the data selection rule.

Here is the variable.

Here is how I apply it.

 

J
Rank

Hi Thomas,

 

Thanks for the reply.

I had already tried this method, and thought it was working, but it has no positive effect on the performance and I think it has to do with the additional Query table.

I need this one to be able to use pagination on highest level, and with the result get the next level for only the latest records.

Tried something like this on the query table but this gives an error about unknown table.

SELECT *, cast(left([Timestamp], 26) as datetime) as Timestamp2

FROM [REST].[EmailDelivered_Detail]

WHERE [file_name] IN 

    (SELECT [Name] 

     FROM [REST].[EmailDelivered_List]

     WHERE [Properties.Last-Modified] > (select Timestamp2 from [ODX_CE].[dbo].[Blob Storage_REST_EventRegistration_i]))

Thomas Lind
Rank
Userlevel 6
Badge +5

In the example I shared I am not using a query table, I have a input field in my RSD file. It will run only with the date added. I would maybe structure this differently.

Let me see if I can come up with a way to do it.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @jacqueline.hofmeijer 

Sorry for the delay.

The reason you get the error is because it is not another RSD file you connect to. The table Storage_Rest_EventRegistration can’t be known by the other tables.

So you will need to have the EmailDelivered_List table only give the newest row by default, so you don’t need to add an input to get the max date.

Maybe this can be done by structuring it like this.

SELECT *, cast(left([Timestamp], 26) as datetime) as Timestamp2

FROM [REST].[EmailDelivered_Detail]

WHERE [file_name] IN 

    (SELECT TOP 1 [Name] 

     FROM [REST].[EmailDelivered_List]

     ORDER BY [Properties.Last-Modified] DESC)

I don’t know if it will be faster, but the RSD files should behave like SQL once they are called like this.

Reply


Terms and ConditionsCookie settings