Skip to main content
To homepage To homepage
Solved

Use variables on Query tables

J
Contributor

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

Best answer by Thomas Lind

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.

View original
    Did this topic help you find an answer to your question?

    4 replies

    Thomas Lind
    Community Manager
    Forum|alt.badge.img+5
    • Thomas Lind
    • Community Manager
    • 1017 replies
    • August 15, 2023

    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
    Contributor

    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
    Community Manager
    Forum|alt.badge.img+5
    • Thomas Lind
    • Community Manager
    • 1017 replies
    • August 16, 2023

    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
    Community Manager
    Forum|alt.badge.img+5
    • Thomas Lind
    • Community Manager
    • 1017 replies
    • Answer
    • October 18, 2023

    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


    Most helpful members this week

    Terms and ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings