I am having problem in executing the following query in SAP B1. Below are code of the query:
SELECT OITM.ItemCode, OITM.ItemName, OIBT.BatchNum, OIBT.PrdDate, OIBT.Notes, OIBT.Quantity, YEAR(OIBT.PrdDate) as 'Year', MONTH(OIBT.PrdDate) as 'Month' FROM OIBT OIBT INNER JOIN OITM OITM ON OIBT.ItemCode = OITM.ItemCode INNER JOIN OITW OITW ON OIBT.ItemCode = OITW.ItemCode AND OITM.ItemCode = OITW.ItemCode AND OIBT.WhsCode = OITW.WhsCode WHERE (OIBT.Quantity > '0') AND OIBT.WhsCode <> 'EWH01' AND OIBT.WhsCode <>'ST01' AND OIBT.PrdDate <= '[%0]'
and this is the error message that I get:
1). [Microsoft][SQL Native Client][SQL Server]Operand data type nvarchar is invalid for multiply operator. 2). [Microsoft][SQL Native Client][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.
It only happened when I want to use variable in OIBT.PrdDate and it did work if i hardcode it to '20100101'.
Kindly guide me on this. Thanks.
I don't know anything about SAP B1, and that might help. It seems that this environment is tripping you. The syntax [%0] is nothing which exists in SQL Server as such, you may be better off asking in a SAP forum, on how to use that syntax.What I can say from this line, is that you can use Profiler to see what SAP B1 is actually sending to SQL Server./
Are you sure the errors are about the query you posted? Because 1) itdoes not contain a multiply operator and 2) does not reference 'ServiceContracts'. Or are you using views (that you didn't describe in your post)?-- Gert-Jan