Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query fails with error of "An expression of non-boolean type specified in a context

Status
Not open for further replies.

dwg23

Technical User
Oct 21, 2002
151
US
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.
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
 
What are you doing in Excel? Excel doesn't run T-SQL.Are you making a SQL Server connection and then trying to run this full script? You may create a stored procedure with a table return value to be able to EXEC this stored proc from VBA.

Bye, Olaf.
 
We have an Excel spread sheet setup to run query's that I write the connection is already made. This allows users to run query's against the database with no risk they will mess things up.
But yes, we make the SQL Connection and then run the script. always before I would create my query in ssms and once it was working I would paste the code to the Excel sheet and all was good.
 
You may create a stored procedure with a table return value to be able to EXEC this stored proc from VBA.

How would I do this?
 
Have you tried running the script with out the comments? I'm not sure how you execute the query but if for some reason it is sent without carriage returns, it is executed as a long one line query. The comments would therefore comment out a lot of the code.
 
RyanEK,
Removing the comments is what got me to the point I am at. although you are correct in that before I removed the comments it would error right away.
I tried just calling the S/P in Excel by just using the S/P name. It asked for a value for one of the var's once I supplied that it ran fine!

Thanks!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top