I am running a SQL query in UiPath but it doesn't give any data back while when I run the same query on SSMS it shows 345 rows?

Help Studio
, ,
1

/*
DROP TABLE #TEMPBASEProducts
DROP TABLE #TEMPProducts

DROP TABLE #Result
*/

/--------------------1- fetch all the sales order details with the product------------------------/

SELECT sod.salesorderdetailid
,sod.bcrm_ProductLineName OrderReferenceNumber
,sod.dot_BespokeOfferingName OfferingName
,sod.dot_BaseProductGuidID baseProductGUID

INTO #TEMPProducts
FROM salesorderdetail sod (nolock)
INNER JOIN salesorderdetail sodbase (nolock)
ON sodbase.salesorderdetailid = sod.dot_baseproductguidid
INNER JOIN Product p on p.ProductId = sod.ProductId
WHERE p.ProductNumber IN (‘MKTGS1001BIZULTIMATE’)
AND sod.dot_baseproductguidid IS NOT NULL

–select * from #TEMPProducts
/-------------------- fetch all the sales order details with the product------------------------/

/--------------------2 fetch all the base sales order details------------------------/

SELECT sod.salesorderdetailid
,sod.bcrm_ProductLineName
,sod.dot_BespokeOfferingName OfferingName

into #TEMPBASEProducts

FROM salesorderdetail sod (nolock)
WHERE sod.SalesOrderDetailId in (select baseProductGUID from #TEMPProducts)

–select * from #TEMPBASEProducts

/--------------------3 RESULT------------------------/

SELECT
–sod.salesorderdetailid
sod.dot_CustomerName
,sod.SalesOrderIdName
,sod.bcrm_ProductLineName OrderReferenceNumber
,sod.dot_BespokeOfferingName OfferingName
,sod.dot_DevicesReserved DevicesReceived
,[ProductName]=(select name from Product(nolock) where sod.ProductId = Product.ProductId)

into #Result

FROM salesorderdetail sod (nolock)
WHERE
(sod.SalesOrderDetailId in (select salesorderdetailid from #TEMPProducts))
OR
(sod.SalesOrderDetailId in (select salesorderdetailid from #TEMPBASEProducts))
OR
(sod.dot_BaseProductGuidID in (select salesorderdetailid from #TEMPBASEProducts))

order by sod.bcrm_ProductLineName asc

select * from #Result

2

Each part of the query serves a specific purpose, and the overall structure seems sound. However, I would suggest a few observations and potential improvements

Verify that the query itself is syntactically correct. In the SQL code you provided, there are commented lines starting with “–” which can cause issues if not handled correctly, especially in some SQL engines. Make sure these comments do not inadvertently affect your query execution.

Try to execute them one by one and see

Or

Create a procedure to perform this

Hope this helps

Cheers @salmantahir31

3

In SSMS it excutes with those “–” but I am not sure about if it can be an issue in UiPath

4

Try executing them only by one
You will get to know where it is failing
@salmantahir31

1 Like
5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.