1

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

enter image description here

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?

Improve this question

2 Answers 2

Reset to default
1

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

enter image description here

But in ADF as Synapse source:

enter image description here

enter image description here

Workaround....

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

enter image description here

Use Select to get data from function.

enter image description here

enter image description here

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The common table expression (CTE) in SQL is not supported in the mapping data flow Query mode, because the prerequisite of using this mode is that queries can be used in the SQL query FROM clause but CTEs cannot do this. To use CTEs, you need to create a stored procedure using the following query:

CREATE PROC CTESP @query nvarchar(max)
AS
BEGIN
EXECUTE sp_executesql @query;
END

Then use the Stored procedure mode in the source transformation of the mapping data flow and set the @query like example with CTE as (select 'test' as a) select * from CTE. Then you can use CTEs as expected.

Source: https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server?tabs=data-factory#source-transformation

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.