Skip to Content
0
Former Member
Nov 24, 2009 at 12:35 PM

Query Parameters Problem

28 Views

Please excuse the formatting as I am not sure why the forum is bunching everything together and not putting my query in code format.

Hi All,

We are using SAP B1 2007 A SP01 PL05

I am having a problem with getting a query to run in SAP with parameter selections. The query below runs fine as is however once I add date parameters which look like this

AND T2.[DocDate] >= '[%0]' AND T2.[DocDate] <= '[%1]'

I start getting errors. The first error I got was RIGHT.CardCode could not be bound once I removed the parameter and added them again I got an error Conversion failed when converting datetime from character string. I then removed the parameters again and added them once more and got another error An expression of non-boolean type specified in a context where a condition is expected. I saved the query and logged out and logged back in and ran it again and now am getting the error RIGHT.CardCode could not be bound again.

Could someone please have a look at this and try and help me get this running as I want to add more parameters where you can select the Customer Group and the Item Group as well.

SELECT     T2.DocDate, T2.DocNum, T2.CardCode, T2.CardName, T5.GroupName, T1.ItemCode, T1.Dscription AS 'Description', T7.ItmsGrpNam, T1.Quantity, T0.Price AS 'Unit Price (List)', T0.Price * T1.Quantity AS 'Gross Total', T1.LineTotal / T1.Quantity AS 'Unit Price (Doc)', T1.LineTotal AS 'Nett Total'
FROM         ITM1 AS T0 INNER JOIN
                      INV1 AS T1 ON T0.ItemCode = T1.ItemCode INNER JOIN
                      OINV AS T2 ON T1.DocEntry = T2.DocEntry INNER JOIN
                      OPLN AS T3 ON T0.PriceList = T3.ListNum INNER JOIN
                      OCRD AS T4 ON T2.CardCode = T4.CardCode INNER JOIN
                      OCRG AS T5 ON T4.GroupCode = T5.GroupCode INNER JOIN
                      OITM AS T6 ON T1.ItemCode = T6.ItemCode INNER JOIN
                      OITB AS T7 ON T6.ItmsGrpCod = T7.ItmsGrpCod
WHERE     T0.PriceList = 1
UNION ALL
SELECT     T2.DocDate, T2.DocNum, T2.CardCode, T2.CardName, T5.GroupName, T1.ItemCode, T1.Dscription AS 'Description', T7.ItmsGrpNam, - (1 * T1.Quantity) AS Quantity, - (1 * T0.Price) AS 'Unit Price (List)', - (1 * (T0.Price * T1.Quantity)) AS 'Gross Total', - (1 * (T1.LineTotal / T1.Quantity)) AS 'Unit Price (Doc)', - (1 * T1.LineTotal) AS 'Nett Total'
FROM         ITM1 AS T0 INNER JOIN
                      RIN1 AS T1 ON T0.ItemCode = T1.ItemCode INNER JOIN
                      ORIN AS T2 ON T1.DocEntry = T2.DocEntry INNER JOIN
                      OPLN AS T3 ON T0.PriceList = T3.ListNum INNER JOIN
                      OCRD AS T4 ON T2.CardCode = T4.CardCode INNER JOIN
                      OCRG AS T5 ON T4.GroupCode = T5.GroupCode INNER JOIN
                      OITM AS T6 ON T1.ItemCode = T6.ItemCode INNER JOIN
                      OITB AS T7 ON T6.ItmsGrpCod = T7.ItmsGrpCod
WHERE     T0.PriceList = 1

Any help will be greatly appreciated.