INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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 

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

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.

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

(OP)
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.

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

(OP)

Quote:

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?

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

Well, first of all lengthy scripts are not what you can naturally execute via a connection. So a natural alternative is defininig stored procs. Never did that? Well, it's not that hard, mainly you just give all your code a name and store it in SQL Server: https://msdn.microsoft.com/en-US/library/ms187926....

Bye, Olaf.

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

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.

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

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close