correlated subquery cannot have TOP

[former_member635855]

Hi Team,

I'v been working in a query that show Stock to an specific date:

SELECT T0."ItemCode" as "CODIGO SAP",  T2."ItemName" as "DESCRIPCION",
(SELECT (SUM(Y."InQty")-SUM(Y."OutQty")) FROM "OINM" Y WHERE Y."ItemCode" = T2."ItemCode" AND Y."Warehouse" = T1."WhsCode" AND Y."DocDate" BETWEEN '20010101' AND '[%1]') as "STOCK", 
T0."IsCommited" as "COMPROMETIDO",
((SELECT (SUM(Y."InQty")-SUM(Y."OutQty")) FROM "OINM" Y WHERE Y."ItemCode" = T2."ItemCode" AND Y."Warehouse" = T1."WhsCode" AND Y."DocDate" BETWEEN '20010101' AND '[%1]')) - T0."IsCommited" as "DISPONIBLE", T1."WhsName" as "PROYECTO", 
(SELECT (SUM(Y."TransValue")) FROM "OINM" Y WHERE Y."ItemCode" = T2."ItemCode" AND Y."Warehouse" = T1."WhsCode" AND Y."DocDate" BETWEEN '20010101' AND '[%1]') as "Valor Acumulado",
(SELECT TOP 1 B."Rate" FROM "ORTT" B INNER JOIN OINM A ON A."DocDate" = B."RateDate" WHERE B."Currency" = 'USD' AND A."DocDate" BETWEEN '20010101' AND '[%1]') "T/C",
((SELECT (SUM(Y."TransValue")) FROM "OINM" Y WHERE Y."ItemCode" = T2."ItemCode" AND Y."Warehouse" = T1."WhsCode" AND Y."DocDate" BETWEEN '20010101' AND '[%1]')) / ((SELECT TOP 1 B."Rate" FROM "ORTT" B INNER JOIN "OINM" A ON A."DocDate" = B."RateDate" WHERE B."Currency" = 'USD' AND A."DocDate" BETWEEN '20010101' AND '[%1]')) as "Valor Acumulado $us",
(select top 1 A."Price" From "AIT1" A where T2."ItemCode" = A."ItemCode" and A."PriceList" = 1) as 
"Precio $us", T2."U_Superficie"

FROM "OITW" T0

INNER JOIN "OWHS" T1 ON T0."WhsCode" = T1."WhsCode"
INNER JOIN "OITM" T2 ON T0."ItemCode" = T2."ItemCode"
INNER JOIN "OMRC" T3 ON T2."FirmCode"=T3."FirmCode"

WHERE (SELECT (SUM(Y."InQty")-SUM(Y."OutQty")) FROM "OINM" Y WHERE Y."ItemCode" = T2."ItemCode" AND Y."Warehouse" = T1."WhsCode" AND Y."DocDate" BETWEEN '20010101' AND '[%1]' )!= '0'

ORDER BY T0."WhsCode", T0."ItemCode"

However y get the following error message: correlated subquery cannot have TOP or ORDER BY

Believe it's probably for this part: (select top 1 A."Price" From "AIT1" A where T2."ItemCode" = A."ItemCode" and A."PriceList" = 1) as "Precio $us"

Need your help for this work please, try to use Limit 1 but does not work.

Regards.