Hello
I want to know if it is possible to implement a loop within a transformation?
Hi
It would be great if you can provide a usecase or an example to better understand the requirement.
I want to be able to fetch data from multiple data sets and process it as needed. The number of data sets will vary. Looping code will enable me to "scale" the code dynamically as needed.
The current suggestion is to use UNION ALL for this.
My approach would be to get a list of data sets, then loop code for data from each data set. This will eliminate the need to change the transformation code every time a data set gets added or removed. This will also improve code maintainability.
A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a SELECT
, INSERT
, UPDATE
, or DELETE
statement. CTEs can help improve the readability and organization of complex queries by allowing you to break down your query into simpler, reusable components. They are defined using the WITH
keyword.
Here’s a basic example of a CTE:
WITH Sales_CTE AS (
SELECT
SalesPersonID,
SUM(SalesAmount) AS TotalSales
FROM
Sales
GROUP BY
SalesPersonID
)
SELECT
SalesPersonID,
TotalSales
FROM
Sales_CTE
WHERE
TotalSales > 10000;
In this example, Sales_CTE
is the CTE that calculates the total sales amount for each salesperson. The main query then selects from this CTE and filters the results to include only those salespersons with total sales greater than 10,000.
CTEs can also be recursive, allowing you to perform operations like hierarchical or tree-structured data traversals.
You can also have nested CTE, which could help you to fetch data from multiple datasets
I do not think CTE usage will work for my use case. I want to select a list of data sets, then select from that list of data sets. I would want/need to use the CTE input as database name for next select. I have not found a way to get this to work.
Pseudocode
WITH dataSets_CTE AS(
SELECT
name
FROM
`_cdf`.`datasets`)
SELECT
*
FROM
`dataSets_CTE`.`tableName`
The second select would need to be looped for every row in the CTE Table.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.