Hello,
I have a query that works for me in ssms but when I run it in Excel I get the error of "An expression of non-boolean type specified in a context where a condition is expected, near 'Location'
Please help me figure out what is wrong.
I have a query that works for me in ssms but when I run it in Excel I get the error of "An expression of non-boolean type specified in a context where a condition is expected, near 'Location'
Please help me figure out what is wrong.
Code:
Declare
@UserId VARCHAR(20) = NULL,
@PrintId VARCHAR(10) = NULL,
@AgingDate DATETIME = NULL,
@GroupBy VARCHAR(6),
@Location VARCHAR(6) = NULL,
@SPC VARCHAR(6) = NULL,
@SPCChars INT = NULL,
@SPCGroup VARCHAR(6) = NULL,
@Summary BIT = 0
--WITH ENCRYPTION
Declare
@Today datetime,
@SelectDate datetime
-- Set parms.
Set @Today = GetDate()
Set NoCount On
If @AgingDate Is Null Set @AgingDate = Convert(varchar, @Today, 112)
If IsNull(@Location, '') = '' Set @Location = '%'
If IsNull(@SPC, '') = '' Set @SPC = '%'
If IsNull(@SPCChars, 0) = 0 Set @SPCChars = 6
If IsNull(@SPCGroup, '') = '' Set @SPCGroup = '%'
Set @SelectDate = Convert(varchar, DateAdd(day, 1, @AgingDate), 112)
-- Create table variable
Declare @ItemLastTx Table(
Item varchar(80) NOT NULL,
Location varchar(6) NOT NULL,
LastTxDate datetime,
PRIMARY KEY (Item, Location)
)
-- Create temp table
Create Table #ItemOnHand (
Item VARCHAR(80) NOT NULL,
Location VARCHAR(6) NOT NULL,
TxNumber varchar(10),
AverageCost MONEY,
QtyOnHand NUMERIC(19,2)
)
Insert Into #ItemOnHand
Select
i.Item,
il.Location,
Null,
Null,
Null
From tblimItem i
Inner Join tblimItemLoc il On i.Item = il.Item
Where il.Location Like @Location
And i.Type <> 'INTANG'
-- Item had to exist.
And il.DateAdded < @SelectDate
And (@SPCGroup = '%' or i.SPC in (Select SPC from tblimSPCGroupSPC where SPCGroup = @SPCGroup))
And i.SPC Like @SPC
Group By i.Item, il.Location
-- Find the first transaction that occurred after the
-- selection date.
Update oh
Set TxNumber = (
Select Min(TxNumber)
From tblimInvTxHistory tx
Where tx.DateAdded >= @SelectDate And
tx.Item = oh.Item And
tx.Location = oh.Location
)
From #ItemOnHand oh
-- that don't already have a transaction.
Update oh
Set TxNumber = (
Select Max(TxNumber)
From tblimInvTxHistory tx
Where tx.DateAdded < @SelectDate And
tx.Item = oh.Item And
tx.Location = oh.Location
)
From #ItemOnHand oh
Where oh.TxNumber Is Null
Update oh
Set AverageCost =
Case
When tx.TxDate >= @SelectDate Then
tx.AvgCostOld
Else
tx.AvgCostNew
End,
QtyOnHand =
Case
When tx.TxDate >= @SelectDate Then
tx.OnHandLocOld
Else
tx.OnHandLocOld + tx.StockQty
End
From #ItemOnHand oh
Inner Join tblimInvTxHistory tx On oh.TxNumber = tx.TxNumber
Update oh
Set AverageCost = il.AccountingCost,
QtyOnHand = (
Select Sum(ilb.QtyOnHand)
From tblimItemLocBin ilb
Where ilb.Item = il.Item And ilb.Location = il.Location
)
From #ItemOnHand oh
Inner Join tblimItemLoc il On oh.Item = il.Item And oh.Location = il.Location
Where TxNumber Is Null
-- Remove items non-positive items.
Delete #ItemOnHand Where QtyOnHand <= 0
Insert into @ItemLastTx
Select ilt.Item, ilt.SellingLocation, Max(ilt.DateShipped)
From(
Select
invl.Item,
inv.SellingLocation,
inv.DateShipped
From tblarInvoice inv
Inner Join tblarInvoiceLine invl On inv.InvoiceNumber = invl.InvoiceNumber
Inner Join #ItemOnHand oh On invl.Item = oh.Item And
inv.ShipFromLocation = oh.Location
Where inv.DateShipped < @SelectDate And
inv.AdjustedInvoiceNumber Is Null
Union
Select
woi.Item,
inv.SellingLocation,
inv.DateShipped
From tblarInvoice inv
Inner Join tblarInvoiceLineWorkOrder ilwo On ilwo.InvoiceNumber = inv.InvoiceNumber
Inner Join tblwhWorkOrderItem woi On woi.WorkOrderNumber = ilwo.WorkOrderNumber
Inner Join #ItemOnHand oh On woi.Item = oh.Item And
inv.ShipFromLocation = oh.Location
Where inv.DateShipped < @SelectDate And
inv.AdjustedInvoiceNumber Is Null
Union
Select
ptl.Item,
pt.Location,
pt.DateShipped
From tblwhPickTicket pt
Inner Join tblwhPickTicketLine ptl On ptl.PickTicketNumber = pt.PickTicketNumber
Inner Join #ItemOnHand oh On ptl.Item = oh.Item And
pt.Location = oh.Location
Where pt.DateShipped < @SelectDate
and pt.Type = 'INTFAB'
) ilt Group By ilt.Item, ilt.SellingLocation
-- Return the data for the report.
Select oh.Location,
i.Item,
i.[Description] As ItemDesc,
-- If grouping by SPC, they may specify to group by a partial SPC.
Case @GroupBy When 'SPC' Then
Left(i.SPC, @SPCChars)
Else
i.SPC
End As SPC,
spc.[Description] As SPCDesc,
isnull(sg.SPCGroup, '<NONE>') as SPCGroup,
sg.[Description] as SPCGroupDesc,
ISNULL( ilt.LastTxDate ,il.DateAdded) As AgingDate,
oh.AverageCost,
oh.QtyOnHand,
oh.AverageCost * oh.QtyOnHand As ExtendedCost,
-- The next four columns are aging ranges for the extended cost.
-- 0 to 1 year range
Case When DateDiff(dd, ISNULL( ilt.LastTxDate ,il.DateAdded), @AgingDate) <= 365 Then
oh.AverageCost * oh.QtyOnHand
Else
0
End As [0-1],
-- 1 to 2 year range
Case When DateDiff(dd, ISNULL( ilt.LastTxDate ,il.DateAdded), @AgingDate) Between 366 And 730 Then
oh.AverageCost * oh.QtyOnHand
Else
0
End As [1-2],
-- 2 to 3 year range
Case When DateDiff(dd, ISNULL( ilt.LastTxDate ,il.DateAdded), @AgingDate) Between 731 And 1095 Then
oh.AverageCost * oh.QtyOnHand
Else
0
End As [2-3],
-- More than three years
Case When DateDiff(dd, ISNULL( ilt.LastTxDate ,il.DateAdded), @AgingDate) >= 1096 Then
oh.AverageCost * oh.QtyOnHand
Else
0
End As [3_OR_MORE]
From tblimItem i
Inner Join #ItemOnHand oh On i.Item = oh.Item
Inner Join tblimItemLoc il On oh.Item = il.Item And oh.Location = il.Location
Left Outer Join @ItemLastTx ilt On oh.Item = ilt.Item And oh.Location = ilt.Location
Inner Join tblimSPC spc On i.SPC = spc.SPC
left outer join tblimSPCGroupSPC sgs on sgs.SPC = i.SPC
left outer join tblimSPCGroup sg on sg.SPCGroup = sgs.SPCGroup
where isnull(sgs.SPCGroup,'') like @SPCGroup
Drop Table #ItemOnHand