How to inject non-literal values in "table_changes" CDF function?

[Coffee77]

Hi guys,

I am trying to use table_changes CDF function in an external SQL function run it from an SQL Warehouse serverless cluster in order to retrieve a set of changes from a given table. Here is the documentation for this fuunction:

https://docs.databricks.com/aws/en/sql/language-manual/functions/table_changes 

So, I'm facing the following issue:

- I would like to filter either "version from / version to" or "timestamp from / timestamp to" in a dynamic and automatic way, that is, without being forced to use literals. All samples seem to be focused in working with this function in a kind of "manual" way where I need to retrieve minimum and maximum "versions" or "timestamps" and then, apply those values as "literals", impossible to do it as "variables". If not possible to inject variables, any workaround for external SQL functions to narrow filters from the very beginning instead of doing it after getting results?

- Goal is to retrieve this information to be consumed by an API backend by hitting directly a SQL Warehouse serverless cluster via ODBC so usage of pyspark/sql notebooks in jobs is not a valid workaround for my business case.

Here is another feed with similar problem but not solved so far:

https://stackoverflow.com/questions/76415836/how-do-i-use-latest-version-in-table-changes-function-f... 

Kind Regards!

[saurabh18cs]

Hi @Coffee77 

You cannot directly use SQL variables or subqueries in these arguments—they must be constants at parse time.

[Coffee77]

In the end, as a workaround I had to move/delegate the query to the .NET API backend. From API I can build the query in the exact way I need by previously processing filters, timestamps or versions, ending up with a similar code to the one displayed below:

It would have been much better to make it with a central common Databricks SQL function but not possible so far.

KR