cancel
Showing results for 
Search instead for 
Did you mean: 

SAP B1 Query

Former Member
0 Kudos
274

I am creating a slow moving inventory report. When I leave the where clause out of the subselects, it works. But I need the date criteria to filter transactions. Does anyone have an idea why this doesn't work?

SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T0.IsCommited, T0.OnOrder, T0.LastPurPrc FROM OITM T0

Where T0.ItemCode not in (Select T2.ItemCode from DLN1 T2 where T2.DocDate >= [%0])

and T0.ItemCode not in (Select T3.ItemCode from IGN1 T3 where T3.DocDate >=[%0])

and T0.ItemCode not in (Select T4.ItemCode from IGE1 T4 where T4.DocDate >=[%0])

and T0.OnHand<>0 and T0.InvntItem='Y'

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi Martin,

This is the final code I used for this report:

SELECT T0.ItemCode, T0.ItemName, T0.OnHand, (T0.OnHand*T0.AvgPrice)[Value at Cost],T0.IsCommited[Committed], T0.OnOrder[On Order] FROM [dbo].[OITM] T0

Where T0.ItemCode not in (Select T2.ItemCode from [dbo].[DLN1] T2 where T2.DocDate >= [%0])

and T0.ItemCode not in (Select T3.ItemCode from [dbo].[IGN1] T3 where T3.DocDate >=[%0])

and T0.ItemCode not in (Select T4.ItemCode from [dbo].[IGE1] T4 where T4.DocDate >=[%0])

and T0.OnHand<>0 and T0.InvntItem='Y'

Former Member
0 Kudos

I know this is closed. But was wondering what your final code looked like

rgds

Martin

Former Member
0 Kudos

try it as

declare @x datetime
select @x = T0.DocDate from DLN1 T0 where T0.DocDate = '[%0]'

SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T0.IsCommited, T0.OnOrder, T0.LastPurPrc FROM OITM T0 
Where T0.ItemCode not in (Select T2.ItemCode from DLN1 T2 where T2.DocDate >=  @x) 
and T0.ItemCode not in (Select T3.ItemCode from IGN1 T3 where T3.DocDate >=@x) 
and T0.ItemCode not in (Select T4.ItemCode from IGE1 T4 where T4.DocDate >=@x)

and T0.OnHand = 0 and T0.InvntItem='Y'

Former Member
0 Kudos

Thanks all but I figured it out.