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!

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

Evaluating the alias 2

Status
Not open for further replies.
ItHurtsWhenIThink

ItHurtsWhenIThink

Technical User
Sep 1, 2007
60
US
I can't seem to be able to evaluate an alias.

I get the following error:

ERROR: Invalid column name 'HydroDue'.
Error Code: 207

I want to be able to get recordset of all bottle that have past their hydro date. I take the hydro date (last hydro) and then add the interval 'HydroInterval' and add it to the 'HydroDate'. Lastly I want to get a list of those that have a HydrDue date less than the current date. But I get the error. What gives? Thanks...

Select
DateAdd(yyyy, SCBABottles.HydroInterval, Max(SCBAHydro.HydroDate)) As HydroDue,
SCBABottles.SerialNumber,
SCBABottles.Manufacture,
SCBABottles.FDBAID,
SCBABottles.HydroInterval,
From SCBABottles Inner Join
SCBAHydro On SCBABottles.BottleID = SCBAHydro.BottleID
Where HydroDue< '12/10/2013'
Group By
SCBABottles.SerialNumber,
SCBABottles.Manufacture,
SCBABottles.FDBAID,
SCBABottles.HydroInterval
 
Sort by date Sort by votes
you can't use alias like that.

either do
Code: 
Select DateAdd(yyyy, SCBABottles.HydroInterval, Max(SCBAHydro.HydroDate)) As HydroDue
      , SCBABottles.SerialNumber
      , SCBABottles.Manufacture
      , SCBABottles.FDBAID
      , SCBABottles.HydroInterval 
From SCBABottles 
Inner Join SCBAHydro 
On SCBABottles.BottleID = SCBAHydro.BottleID
Group By SCBABottles.SerialNumber
        , SCBABottles.Manufacture
        , SCBABottles.FDBAID
        , SCBABottles.HydroInterval
        
having  DateAdd(yyyy, SCBABottles.HydroInterval, Max(SCBAHydro.HydroDate)) < '12/10/2013'
or
Code: 
select *
from (Select DateAdd(yyyy, SCBABottles.HydroInterval, Max(SCBAHydro.HydroDate)) As HydroDue
           , SCBABottles.SerialNumber
           , SCBABottles.Manufacture
           , SCBABottles.FDBAID
           , SCBABottles.HydroInterval 
     From SCBABottles 
     Inner Join SCBAHydro 
     On SCBABottles.BottleID = SCBAHydro.BottleID
     Group By SCBABottles.SerialNumber
             , SCBABottles.Manufacture
             , SCBABottles.FDBAID
             , SCBABottles.HydroInterval
     ) t
where HydroDue < '12/10/2013'

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
That worked. Now I have an issue with the results. I tried to change the INNER join to Left but got the same results.

I have a table with all the air bottles (SCBABottles). My second table contains all the hydro testing for each bottle. In the query I thought the Max(SCBAHydro.HydroDate) would produce just one record for each bottle with the most current hydro information. But the results produce all records in the SCBAHydro table. All I want is all records from the SCBABottles and only the Max(SCBAHydro.HydroDate) from the SCBAHydro table. Here is the query:

Select DateAdd(yyyy, SCBABottles.HydroInterval, Max(SCBAHydro.HydroDate)) As HydroDue
, SCBABottles.SerialNumber"
, SCBABottles.Manufacture"
, SCBABottles.FDBAID"
, SCBABottles.HydroInterval"
, SCBABottles.DeptID"
, SCBABottles.BottleID"
, SCBABottles.DateofManufacture"
, SCBABottles.BALife"
, SCBABottles.PSI"
, SCBABottles.Model"
, SCBAHydro.HydroDate"
, SCBABottles.NFPAEdition"
, SCBABottles.CubicFeet"
From SCBABottles"
INNER Join SCBAHydro"
On SCBABottles.BottleID = SCBAHydro.BottleID"
Group By SCBABottles.SerialNumber"
, SCBABottles.Manufacture"
, SCBABottles.DateofManufacture"
, SCBABottles.Model"
, SCBABottles.FDBAID"
, SCBABottles.HydroInterval"
, SCBABottles.DeptID"
, SCBABottles.BottleID"
, SCBABottles.BALife"
, SCBABottles.PSI"
, SCBABottles.Enable"
, SCBABottles.Model"
, SCBABottles.DeptID"
, SCBAHydro.HydroDate"
, SCBABottles.NFPAEdition"
, SCBABottles.CubicFeet"
having SCBABottles.Enable='Yes'
and SCBABottles.DeptID=18

Thanks for your help so far.
 
Upvote 0 Downvote
If it's just one piece of data, and you've only one record per item in table "SCBABottles", then how about going with a subquery and losing the need to group?

Code: 
SELECT DateAdd(yyyy, SCBABottles.HydroInterval, 
[COLOR=#CC0000][b](SELECT Max(SCBAHydro.HydroDate) from SCBAHydro where SCBABottles.BottleID = SCBAHydro.BottleID)[/b][/color]
) As HydroDue
 , SCBABottles.SerialNumber
 , SCBABottles.Manufacture
 , SCBABottles.FDBAID
 , SCBABottles.HydroInterval
 , SCBABottles.DeptID
 , SCBABottles.BottleID
 , SCBABottles.DateofManufacture
 , SCBABottles.BALife
 , SCBABottles.PSI
 , SCBABottles.Model
 , SCBAHydro.HydroDate
 , SCBABottles.NFPAEdition
 , SCBABottles.CubicFeet
FROM SCBABottles
WHERE SCBABottles.Enable='Yes'
 and SCBABottles.DeptID=18

soi là, soi carré
 
Upvote 0 Downvote
I get:
ERROR: The multi-part identifier "SCBAHydro.HydroDate" could
not be bound.

?? looks right.

checked the spelling
 
Upvote 0 Downvote
I assume that each BottleId In SCBABottles can have a different HydroInterval. Furthermore, I assume that the Max HydroDate from SCBAHydro can be different for each BottleId as well.

Since this is an assumption, I would prefer for you to confirm or deny before I spend too much time possibly going down the wrong path.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I assume that each BottleId In SCBABottles can have a different HydroInterval. Yes that is correct.


I assume that the Max HydroDate from SCBAHydro can be different for each BottleId as well Yes, that is correct as well

The SCBAHydro table may contain many rows with the same BottleID, since we want to collect each time the bottle was hydro'd.

SCBABottles only unique BottleID


pertinent table schema

SCBAHydro
HID int PK
BottleID int
HydroDate smalldate

SCBABottles
BottleID int PK
DeptID int
HydroInterval numeric

Hope that helps.

Thanks for your time. Very much appreciated.


 
Upvote 0 Downvote
I missed seeing the repetition of Hydrodate in the query, although I suspect George may be offering up something shortly.

Code: 
SELECT DateAdd(yyyy, SCBABottles.HydroInterval, 
(SELECT Max(SCBAHydro.HydroDate) from SCBAHydro where SCBABottles.BottleID = SCBAHydro.BottleID)
) As HydroDue
 , SCBABottles.SerialNumber
 , SCBABottles.Manufacture
 , SCBABottles.FDBAID
 , SCBABottles.HydroInterval
 , SCBABottles.DeptID
 , SCBABottles.BottleID
 , SCBABottles.DateofManufacture
 , SCBABottles.BALife
 , SCBABottles.PSI
 , SCBABottles.Model
 , [COLOR=#CC0000](SELECT Max(SCBAHydro.HydroDate) from SCBAHydro where SCBABottles.BottleID = SCBAHydro.BottleID) as HydroDate[/color]
 , SCBABottles.NFPAEdition
 , SCBABottles.CubicFeet
FROM SCBABottles
WHERE SCBABottles.Enable='Yes'
 and SCBABottles.DeptID=18

soi là, soi carré
 
Upvote 0 Downvote
try this:

Code: 
; With MaxDates As
(
  Select BottleId, 
         Max(SCBAHydro.HydroDate) As MaxDate
  From   SCBAHydro
  Group By BottleId
)
Select DateAdd(yyyy, SCBABottles.HydroInterval, MaxDates.MaxDate)) As HydroDue
       , SCBABottles.SerialNumber
       , SCBABottles.Manufacture
       , SCBABottles.FDBAID
       , SCBABottles.HydroInterval
       , SCBABottles.DeptID
       , SCBABottles.BottleID
       , SCBABottles.DateofManufacture
       , SCBABottles.BALife
       , SCBABottles.PSI
       , SCBABottles.Model
       , MaxDates.MaxDate As HydroDate
       , SCBABottles.NFPAEdition
       , SCBABottles.CubicFeet
From   SCBABottles
       INNER Join MaxDates
         On SCBABottles.BottleID = MaxDates.BottleID
Where  SCBABottles.Enable='Yes'
       and SCBABottles.DeptID=18

Note the with block at the top. This will return the max hydro date for each bottle id. It's only grouping on one column which simplifies things a lot.

Also notice that I changed one of the columns you were returning from SCBAHydro.HydroDate to MaxDates.MaxDate. If a particular BottleId had multiple HydroDates, your original query would have returned a row for each date, which is not what you wanted. By extracting that part of the query out, the rest of the query is a lot simpler to write (and will also perform better).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
drlex query works fine.

gmmastros, your query produced an error for me:

ERROR: Common table expression defined but not used.
Error Code:
422

I want to thank both of you very much for helping me out with this solution. Your time and patience is very much appreciated.

Have a Merry Christmas and a wonderful New Year.
 
Upvote 0 Downvote
I think you missed ; before with. gmmastros solution should work match faster it is one query for max values, but drlex solution running subquery for each row...
 
Upvote 0 Downvote
The problem was mine. I had an extra close parenthesis.

Code: 
; With MaxDates As
(
  Select BottleId, 
         Max(SCBAHydro.HydroDate) As MaxDate
  From   SCBAHydro
  Group By BottleId
)
Select DateAdd(yyyy, SCBABottles.HydroInterval, MaxDates.MaxDate) As HydroDue
       , SCBABottles.SerialNumber
       , SCBABottles.Manufacture
       , SCBABottles.FDBAID
       , SCBABottles.HydroInterval
       , SCBABottles.DeptID
       , SCBABottles.BottleID
       , SCBABottles.DateofManufacture
       , SCBABottles.BALife
       , SCBABottles.PSI
       , SCBABottles.Model
       , MaxDates.MaxDate As HydroDate
       , SCBABottles.NFPAEdition
       , SCBABottles.CubicFeet
From   SCBABottles
       INNER Join MaxDates
         On SCBABottles.BottleID = MaxDates.BottleID
Where  SCBABottles.Enable='Yes'
       and SCBABottles.DeptID=18

The mistake was here:

Select DateAdd(yyyy, SCBABottles.HydroInterval, MaxDates.MaxDate)[!])[/!] As HydroDue

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Got it! thanks for the solution. Both work well and I'll go with the more efficient query.

Thanks again.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
494
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
181
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
289
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
the goal mfrom asking question is t
Replies
0
Views
117
ahmedsa2018
ahmedsa2018
Colin Burton
  • Locked
  • Question
Determine if a file exists on s different server to the SQL Server 1
Replies
2
Views
545
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top