cancel
Showing results for 
Search instead for 
Did you mean: 

Service Contract (OCTR) Error when running a pivot query

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Use :

 [%0] 

for your date variables.

Cheers

Answers (3)

Answers (3)

Former Member
0 Kudos

I was able to figure this out.

The table in which I needed to select as the user imput needed to be the left most table, so by simply changing the tables everything worked perfectly.

Here is the exact query, hope it helps someone else..

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 T2.ItmsGrpCod = '101' and T1.U_Configuration <> 'Blank' then 'Embroidery'

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

else NULL end [ArtType],

t0.docnum [stock_value],

MONTH(t0.docduedate) [month]

FROM dbo.ORDR t0 INNER JOIN dbo.RDR1 t1 ON T0.DocEntry = T1.DocEntry INNER JOIN

dbo.OITM T2 ON T1.ItemCode = T2.ItemCode

Where T0.DOcDueDate >= '[%0]' and T0.DOcDueDate <= '[%1]' and T0.JrnlMemo <> 'cancelled' and T0.U_ProductionReady = 'No' and T1.WhsCode = '01' and T1.LineStatus = 'o'

) AS source

PIVOT

(Count([stock_value])

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

Former Member
0 Kudos

Gordon,

I have looked and tried to declare the dates and still get the exact same error..

Can anyone help me?

Former Member
0 Kudos

Craig Spehar,

Have you tried by parameters before SELECT? If you are not clear. Check the forum first. There are many examples you may find.

Thanks,

Gordon