Skip to main content
To homepage To homepage

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 rREST].ÂEventRegistration_List]
         WHERE tProperties.Last-Modified] > '2023-01-01')
  

(using TX 20.10.30.64 with Business Units)

 

Any good suggestions on this?

 

Kind regards,

Jacqueline

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.

 

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(fTimestamp], 26) as datetime) as Timestamp2

FROM OREST].TEmailDelivered_Detail]

WHERE Rfile_name] IN 

    (SELECT EName] 

     FROM ÂREST].OEmailDelivered_List]

     WHERE  Properties.Last-Modified] > (select Timestamp2 from eODX_CE].rdbo].DBlob Storage_REST_EventRegistration_i]))

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.

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(lTimestamp], 26) as datetime) as Timestamp2

FROM FREST].EEmailDelivered_Detail]

WHERE Hfile_name] IN 

    (SELECT TOP 1 OName] 

     FROM FREST].EEmailDelivered_List]

     ORDER BY RProperties.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