Forum Discussion

ChadiLouca's avatar
ChadiLouca
Regular Visitor
2 years ago
Solved

Passing a Date/Time Variable inside a SQL Query in Dataflow Advanced Editor

I want to load data daily from MySQL to Fabric Lakehouse using Dataflow.
My technique is to get the maximum DateCompleted and get the data from MySQL bigger than this value.
I have 2 queries in my dataflow. The first "PGW_Payment_Transactions_LH" calculates the Maximum Date/Time from DateCompleted Column from the table in Lakehouse.


The second query Loads the data from MySQL.

This is the Query I am using:

 

let
    MaxDateCompleted = #"PGW_Payment_Transactions_LH",
    Source = MySQL.Database("Server Name", "Database Name", [Query = "SELECT #(lf)id,#(lf)partition_id,#(lf)date_created,#(lf)date_completed,#(lf)country_code,#(lf)payment_provider_code,#(lf)payment_provider_type,#(lf)integration_service_id,#(lf)revenue_share_id,#(lf)provider_tran_id,#(lf)client_correlation_id,#(lf)payment_acct_num,#(lf)merchant_code,#(lf)merchant_name,#(lf)service_category,#(lf)merchant_correlation_id,#(lf)payment_type,#(lf)product_id,#(lf)product_desc,#(lf)subscription_id,#(lf)provider_subscription_id,#(lf)amount,#(lf)currency_code,#(lf)status,#(lf)provider_response_code,#(lf)verification_type,#(lf)verification_instruction,#(lf)verification_token, 'PROD'  As  SourceDb,#(lf)#(tab) DATE_FORMAT(NOW(), '%Y%m%d%H%m%s') AS ETLRunDate#(lf)FROM payment_transactions#(lf)#(lf)WHERE date_completed  " > & MaxDateCompleted ])
in
  Source

 

It gives me this error:

 

Expression.Error: We cannot apply operator & to types Text and DateTime.DetailsReason = Expression.Error
Operator = &
Left = SELECT 
id,
partition_id,
date_created,
date_completed,
country_code,
payment_provider_code,
payment_provider_type,
integration_service_id,
revenue_share_id,
provider_tran_id,
client_correlation_id,
payment_acct_num,
merchant_code,
merchant_name,
service_category,
merchant_correlation_id,
payment_type,
product_id,
product_desc,
subscription_id,
provider_subscription_id,
amount,
currency_code,
status,
provider_response_code,
verification_type,
verification_instruction,
verification_token, 'PROD'  As  SourceDb,
   DATE_FORMAT(NOW(), '%Y%m%d%H%m%s') AS ETLRunDate
FROM payment_transactions

 

WHERE date_completed >
Right = 5/16/2024 4:01:01 AM

 

Is there a solution for this issue?

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Change

     date_completed  " > & MaxDateCompleted ])

     

    to

     

    date_completed  > "& MaxDateCompleted ])

     

    --Nate