• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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.
0
JAMES
Asked:
JAMES
  • 3
  • 3
1 Solution
 
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
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now