Why Synapse is not working with my query?

I’m trying to use a query in Synapse Analytics from Azure, and when I use it I got the next error:

at Source ‘AgenciesInventoryQueryFromSynapsestg’: shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: Parse error at line: 1, column: 47: Incorrect syntax near ‘WITH’.

That’s weird for me cause I tested the query in Microsoft SQL Server Management before to add it to Synapse, and it works perfectly there, the query is the next one:

WITH cte AS (Select abi_stg.mex_log_reverlog_agencies_inventory.centro, abi_stg.mex_log_reverlog_destinations_catalog.agencia,
abi_stg.mex_log_reverlog_agencies_inventory.material, abi_stg.mex_log_reverlog_agencies_inventory.almacen,
abi_stg.mex_log_reverlog_agencies_inventory.texto_breve_material, abi_stg.mex_log_reverlog_agencies_inventory.unidad_medida_base,
abi_stg.mex_log_reverlog_agencies_inventory.libre_utilizacion, abi_stg.mex_log_reverlog_agencies_inventory.control_calidad,
abi_stg.mex_log_reverlog_agencies_inventory.stock_no_disponible, abi_stg.mex_log_reverlog_agencies_inventory.bloqueado, abi_stg.mex_log_reverlog_agencies_inventory.devoluciones,
abi_stg.mex_log_reverlog_agencies_inventory.stock_en_transito, abi_stg.mex_log_reverlog_agencies_inventory.trasladando,
abi_stg.mex_log_reverlog_agencies_inventory.stock_bloqueado_em_valorado,
abi_stg.mex_log_reverlog_destinations_catalog.drv as zona,
abi_stg.mex_log_reverlog_destinations_catalog.subagencia, abi_stg.mex_log_reverlog_destinations_catalog.origen_jda, 
abi_stg.mex_log_reverlog_materials_catalog.id,
abi_stg.mex_log_reverlog_materials_catalog.marca,
abi_stg.mex_log_reverlog_materials_catalog.cupo, abi_stg.mex_log_reverlog_materials_catalog.tipo_envase, abi_stg.mex_log_reverlog_agencies_average_sales_catalog.ventas_promedio,
RANK() OVER (partition by abi_stg.mex_log_reverlog_agencies_inventory.centro order by abi_stg.mex_log_reverlog_agencies_average_sales_catalog.uen desc) as order_c
From abi_stg.mex_log_reverlog_agencies_inventory 
Left Join abi_stg.mex_log_reverlog_destinations_catalog
On abi_stg.mex_log_reverlog_agencies_inventory.centro = abi_stg.mex_log_reverlog_destinations_catalog.centro
Left Join abi_stg.mex_log_reverlog_materials_catalog
On abi_stg.mex_log_reverlog_agencies_inventory.material = abi_stg.mex_log_reverlog_materials_catalog.material
Left Join abi_stg.mex_log_reverlog_agencies_average_sales_catalog
On abi_stg.mex_log_reverlog_destinations_catalog.origen_jda = abi_stg.mex_log_reverlog_agencies_average_sales_catalog.zona)
select centro,agencia,material,almacen,texto_breve_material,unidad_medida_base,libre_utilizacion,control_calidad,stock_no_disponible,bloqueado,
devoluciones,stock_en_transito,trasladando,stock_bloqueado_em_valorado,zona,subagencia,origen_jda,id,marca,cupo,tipo_envase,ventas_promedio
from cte where order_c =1

Any idea what is going wrong here? or any idea for a work around?

Answer

Currently this is not supported so is using Order By clauses. As a workaround you can use user-defined table functions, such as select * from udfGetData(), where a UDF in SQL would return a table. This query will produce a source table that you can use in your data flow.

Example: the below works fine in SSMS

But in ADF as Synapse source:

Workaround….

Create a user defined function as necessary, for example as below:

Use Select to get data from function.