Add cross apply in an Azure Data Factory Data Flow

Question

How do I simulate the cross apply in an Azure Data Factory Data flow?

SELECT
   wo.OrderId,
   max(wo.OrderVersionId) as OrderVersionId,
   max(tss.FromDate) as TSSFromDate,
   tss.ToDate as TSSToDate 
FROM dayshift.OrderMain wo CROSS APPLY (
   SELECT
      (
         SELECT
            MIN(wo.OrderEndDate) as OrderLastDate
         FROM
            (SELECT
                  wo.OrderEndDate 
               UNION
               SELECT
                  wo.OrderTerminationDate 
               WHERE
                  wo.OrderTerminationDate IS NOT NULL
            )
            d 
      )
) woEnd 
      INNER JOIN dayshift.TSSchedule tss 
ON wo.OrderTimeCycleId = tss.TimeCycleId
and WoEnd.OrderLastDate >= tss.FromDate
and wo.OrderStartDate <= tss.ToDate;

I tried to create 2 branches for the CTE to get the union between wo.OrderEndDate and wo.OrderTerminationDate but my challenge is how to integate the cross apply into the main data flow;

Here is a sample data;

Answer 1

AFAIK, cross apply might not be possible directly in ADF dataflow.

These are some approaches that you can give a try to achieve your requirement.

1. Inner Join: Cross apply and Inner join will give same results. As you already using Union transformation to do the Union. Use inner join on the results of these. You can add muiltple conditions as well.

2. Copy table from one database to another using copy activity, then execute the above query using copy activity or dataflow as per your requirement.

   As two tables belongs to same database, the above query will give the desired result.