Transformations and Loops

Hi @Dirk Ackerman,

It would be great if you can provide a usecase or an example to better understand the requirement.

@Mithila Jayalath 

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.

​

@Dirk Ackerman you can use CTE or common table expressions

 

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

@Akash Sood 

​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.