Service Contract (OCTR) Error when running a pivot query

96 Views

I have looked all over and hope someone can help me out.

I have a query that gives a error of 'Service Contract (OCTR)'.

We are on SAP B1 2007 A SP1 PL 6

The query is:

SELECT ArtType,

[1] [jan],

[2] [feb],

[3] [mar],

[4] [apr],

[5] [may],

[6] [jun],

[7] [jul],

[8] [aug],

[9] [sep],

[10] [oct],

[11] [nov],

[12] [dec]

FROM (SELECT DISTINCT Case When T0.ItmsGrpCod = '101' and T1.U_Configuration <> 'Blank' then 'Embroidery'

When (T0.ItmsGrpCod = '100' or T0.ItmsGrpCod = '102' or T0.ItmsGrpCod = '103' or T0.ItmsGrpCod = '104' or T0.ItmsGrpCod = '107') and T1.U_Configuration = 'Blank' then 'Plain'

else NULL end [ArtType],

t2.docnum [stock_value],

MONTH(T2.DocDueDate) [month]

FROM dbo.OITM t0 INNER JOIN dbo.RDR1 t1 ON T0.ItemCode = T1.ITemCode INNER JOIN

dbo.ORDR T2 ON T1.DocEntry = T2.DocEntry

Where T2.JrnlMemo <> 'cancelled' and T2.U_ProductionReady = 'No' and T1.WhsCode = '01' and T1.LineStatus = 'o' and t2.Docduedate >= '[%0]' and t2.docduedate <= '[%1]'

) AS source

PIVOT

(Count([stock_value])

FOR [month] IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] ) ) AS pivoted

I believe the problem lies in the Where statement and allowing the users to input the date range, if I hard code the dates it works great but this is needed to be used for date ranges.

Thanks in advance,

Craig Spehar