Query without variables is asking for one

[IldiW]

Hi Everyone!
I have a query running fine in SSMS and it has no variables. When I try to run the same in B1, it's asking for the ItemCode to be chosen by the user..?

Any ideas on what I'm doing wrong?
Thanks!

SELECT 
distinct T1.ItemCode
, (SELECT SUM(S1.Quantity) FROM OIGN S0 INNER JOIN IGN1 S1 on S0.DocEntry=S1.DocEntry WHERE S1.ItemCode=T1.ItemCode ) as [Total received]
, (SELECT S0.OnHand FROM OITW S0 WHERE S0.ItemCode=T1.ItemCode and S0.WhsCode='01') as [In Stock]
, (((SELECT SUM(S1.Quantity) FROM OIGN S0 INNER JOIN IGN1 S1 on S0.DocEntry=S1.DocEntry WHERE S1.ItemCode=T1.ItemCode )-
(SELECT S0.OnHand FROM OITW S0 WHERE S0.ItemCode=T1.ItemCode and S0.WhsCode='01'))/
(SELECT SUM(S1.Quantity) FROM OIGN S0 INNER JOIN IGN1 S1 on S0.DocEntry=S1.DocEntry WHERE S1.ItemCode=T1.ItemCode )) as [% Sold]
, ((SELECT SUM(S1.Quantity) FROM OIGN S0 INNER JOIN IGN1 S1 on S0.DocEntry=S1.DocEntry WHERE S1.ItemCode=T1.ItemCode )-
 (SELECT S0.OnHand FROM OITW S0 WHERE S0.ItemCode=T1.ItemCode and S0.WhsCode='01')) as [total Sold]
, (SELECT SUM(S1.LineTotal) FROM ORDR S0 INNER JOIN RDR1 S1 ON S0.DocEntry=S1.DocEntry WHERE S1.ItemCode=T1.ItemCode ) as [Total Revenue]
, (SELECT AVG(S1.Price) FROM ORDR S0 INNER JOIN RDR1 S1 ON S0.DocEntry=S1.DocEntry WHERE S1.ItemCode=T1.ItemCode ) as [Avg Price]
, (SELECT S1.Price FROM OITM S0 INNER JOIN ITM1 S1 ON S0.ItemCode=S1.ItemCode INNER JOIN OPLN S2 on S1.PriceList=S2.ListNum WHERE S1.Pricelist=1 and S1.ItemCode=T1.ItemCode) as [Current RRP]

FROM ORDR T0 INNER JOIN RDR1 T1 on T0.DocEntry=T1.DocEntry

WHERE T0.U_G3Medical='y'

[IldiW]

Ah, thanks Lothar, that worked!

I thought having something in brackets will make it safe but clearly not - I'll remember that!

Thanks again!

[Johan_Hakkesteegt]

It generelly makes it safe, but [% is interpreted by the sql parser as beginning a parameter. You can for example use:

• [Sold %]
• [ % Sold] (please note the space between the bracket and percentage character)

Regards,

Johan