Invalid Column Name Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Invalid Column Name Error

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2008-10-28 : 06:49:23

Hi there,

When I run the following SQL statement I get the error "Invalid column name 'Bradford'".


select

   EMPLOYEE.EMPLOY_REF AS Employ_ref,

   ((Count(distinct Absence.ABSENCE_REF)) *  (Count(distinct Absence.ABSENCE_REF)) *  (SUM(ABSDET.DAYPORTION)))  AS Bradford,

   (RTRIM(EMPLOYEE.FORENAME)+' '+ EMPLOYEE.SURNAME) AS Employee_Name, 

   SV_LA_CURRENT_POST.LOCATION AS Location,

   SV_LA_CURRENT_POST.DEPT AS Dept

from

     EMPLOYEE

        INNER JOIN dbo.ABSENCE AS Absence

         ON EMPLOYEE.EMPLOY_REF = Absence.EMPLOY_REF

         join ABSDET on Absence.ABSENCE_REF = ABSDET.ABSENCE_REF

         join SV_LA_CURRENT_POST on EMPLOYEE.EMPLOY_REF = SV_LA_CURRENT_POST.EMPLOY_REF

where

  (EMPLOYEE.LEAVER = 0) 

  and absence.ABS_REASON in ('SICK', 'SICKNESS')

  and absence.UNTILDATE >= DateAdd(ww,-52, CONVERT(datetime, ROUND(CONVERT(float, GETDATE()), 0, 1)))

  and ABSDET.ABS_DATE >=DateAdd(ww,-52, CONVERT(datetime, ROUND(CONVERT(float, GETDATE()), 0, 1)))

  and SV_LA_CURRENT_POST.LOCATION = 'LANCASTER'

  and SV_LA_CURRENT_POST.DEPT = 'BSG'

  and Bradford >= 100

group by  

  EMPLOYEE.EMPLOY_REF, EMPLOYEE.SURNAME, EMPLOYEE.FORENAME,  SV_LA_CURRENT_POST.LOCATION, SV_LA_CURRENT_POST.DEPT

order by

EMPLOYEE.SURNAME

Basically I want to filter the output of the query to include only rows where the 'Bradford' number is more than or equel to 100, but I can't seem to get the syntax right because Bradford is a calculated number rather than an actual field in the database.

Any ideas?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 06:56:10

you cant use aliases directly in where. you need to modify query like below

select

   EMPLOYEE.EMPLOY_REF AS Employ_ref,

   ((Count(distinct Absence.ABSENCE_REF)) *  (Count(distinct Absence.ABSENCE_REF)) *  (SUM(ABSDET.DAYPORTION)))  AS Bradford,

   (RTRIM(EMPLOYEE.FORENAME)+' '+ EMPLOYEE.SURNAME) AS Employee_Name, 

   SV_LA_CURRENT_POST.LOCATION AS Location,

   SV_LA_CURRENT_POST.DEPT AS Dept

from

     EMPLOYEE

        INNER JOIN dbo.ABSENCE AS Absence

         ON EMPLOYEE.EMPLOY_REF = Absence.EMPLOY_REF

         join ABSDET on Absence.ABSENCE_REF = ABSDET.ABSENCE_REF

         join SV_LA_CURRENT_POST on EMPLOYEE.EMPLOY_REF = SV_LA_CURRENT_POST.EMPLOY_REF

where

  (EMPLOYEE.LEAVER = 0) 

  and absence.ABS_REASON in ('SICK', 'SICKNESS')

  and absence.UNTILDATE >= DateAdd(ww,-52, CONVERT(datetime, ROUND(CONVERT(float, GETDATE()), 0, 1)))

  and ABSDET.ABS_DATE >=DateAdd(ww,-52, CONVERT(datetime, ROUND(CONVERT(float, GETDATE()), 0, 1)))

  and SV_LA_CURRENT_POST.LOCATION = 'LANCASTER'

  and SV_LA_CURRENT_POST.DEPT = 'BSG'

  and Bradford >= 100

group by  

  EMPLOYEE.EMPLOY_REF, EMPLOYEE.SURNAME, EMPLOYEE.FORENAME,  SV_LA_CURRENT_POST.LOCATION, SV_LA_CURRENT_POST.DEPT
HAVING ((Count(distinct Absence.ABSENCE_REF)) *  (Count(distinct Absence.ABSENCE_REF)) *  (SUM(ABSDET.DAYPORTION)))  >=100
order by

EMPLOYEE.SURNAME

Gyto
Posting Yak Master

144 Posts

Posted - 2008-10-28 : 06:58:28

Aaaaah....I did try that but I think I had 'Count' outside the brackets....wondered why it wasn't working!

Thanks very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 07:02:40

quote:
Originally posted by Gyto

Aaaaah....I did try that but I think I had 'Count' outside the brackets....wondered why it wasn't working!

Thanks very much!

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources