Temp variable in WHERE clause

When we try and use the local variable NetQty (see below) in the WHERE clause we receive the following error in SQL Analyzer.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'NetQty'.

SELECT NetQty = Sum(Qty - QtyClosed),UserID, BookName, Contract, Currency, Exchange, PricingDate FROM books
LEFT OUTER JOIN TicketHeaders ON TicketHeaders.TicketNumber LIKE Books.TicketNumber
WHERE PricingDate > '20041124' AND UserID LIKE 'JAMES' AND BookName LIKE '1' AND NetQty <> 0
GROUP BY UserID, BookName, Contract, Currency, Exchange, PricingDate

We only want to include the row if this Qty - QtyClosed <> 0 - HOW?

Many thanks.

James.
JAMESAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
Use a HAVING Clause as follows

SELECT NetQty = Sum(Qty - QtyClosed),UserID, BookName, Contract, Currency, Exchange, PricingDate FROM books
LEFT OUTER JOIN TicketHeaders ON TicketHeaders.TicketNumber LIKE Books.TicketNumber
WHERE PricingDate > '20041124' AND UserID LIKE 'JAMES' AND BookName LIKE '1'
GROUP BY UserID, BookName, Contract, Currency, Exchange, PricingDate
HAVING Sum(Qty - QtyClosed) <> 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JAMESAuthor Commented:
Fantastic.

Thanks very much.
0
JAMESAuthor Commented:
....I didnt test it here - where it's needed.  How do I include a SUM and HAVING when I dont want to include them in the INSERT

If you want I will post this as a new question!

      INSERT INTO PRICES(UserID, BookName, Contract, Currency, Exchange, PricingDate, Price, ComparisonPrice)
            SELECT UserID, BookName, Contract, Currency, Exchange, PricingDate,0,0, FROM books
            LEFT OUTER JOIN TicketHeaders ON TicketHeaders.TicketNumber LIKE Books.TicketNumber
            WHERE PricingDate > @ValueDate AND UserID LIKE @UserID AND BookName LIKE @BookName
            GROUP BY UserID, BookName, Contract, Currency, Exchange, PricingDate

Thanks.

James.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HilaireCommented:
Glad I could help.
As a rule of thumb,
Any filter / condition on the result of an aggregate / group function (count, max, avg, ...) should be set in the HAVING clause.

eg to check duplicates IDs (more than one occurence)

select ID , count(*) as dups# from  <yourtable> group by ID having count(*) > 1
0
HilaireCommented:
Not sure I understand the question fully

You can set a condition using HAVING even if you don't use the sum/count in the SELECT part of the statement
The statement below is correct

     INSERT INTO PRICES(UserID, BookName, Contract, Currency, Exchange, PricingDate, Price, ComparisonPrice)
          SELECT UserID, BookName, Contract, Currency, Exchange, PricingDate,0,0, FROM books
          LEFT OUTER JOIN TicketHeaders ON TicketHeaders.TicketNumber LIKE Books.TicketNumber
          WHERE PricingDate > @ValueDate AND UserID LIKE @UserID AND BookName LIKE @BookName
          GROUP BY UserID, BookName, Contract, Currency, Exchange, PricingDate
          HAVING Sum(Qty - QtyClosed) <> 0


0
JAMESAuthor Commented:
Thats great again!

It's not you not understanding the question - it's me not understanding your answer ;-)

I do now!

Thanks.

James.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.