Invalid Column Name

[OAKEJ]

I'm new to SQL Server 2005 and I've got a SQL query that I keep getting a Invalid Column Name,

SELECT newreg AS region, crsite AS location,
CASE WHEN crsite='COLU2' THEN.03
WHEN crsite='COLUOH' THEN.2
WHEN crsite='GARYIN' THEN.24
WHEN crsite='HARRMO' THEN.23
WHEN crsite='HOUSTX' THEN.08
WHEN crsite='INVGMN' THEN.10
WHEN crsite='IRVITX' THEN.10
WHEN crsite='LARETX' THEN.09
WHEN crsite='MANTIL' THEN.22
ELSE NULL END
AS goalredux
FROM OAKEJ_NEWSHPLST1

WHERE (goalredux) IS NOT NULL --Invalid column name 'goalredux'.

How Can I resolve this, Any Suggestions would be greatly appreciated. Thanks

 

[gmmastros]

goalredux is a column alias. You cannot use a column alias in a where clause unless you make a derived table.

Usually, the best way to accommodate this is to put the case/when in the where clause.

SELECT   newreg AS region, crsite AS location,                         
           CASE WHEN crsite='COLU2' THEN.03
        WHEN crsite='COLUOH' THEN.2
                WHEN crsite='GARYIN' THEN.24
        WHEN crsite='HARRMO' THEN.23
        WHEN crsite='HOUSTX' THEN.08
        WHEN crsite='INVGMN' THEN.10
        WHEN crsite='IRVITX' THEN.10
        WHEN crsite='LARETX' THEN.09
                WHEN crsite='MANTIL' THEN.22
        ELSE NULL END
        AS goalredux      
FROM  OAKEJ_NEWSHPLST1    

WHERE    (CASE WHEN crsite='COLU2' THEN.03
        WHEN crsite='COLUOH' THEN.2
                WHEN crsite='GARYIN' THEN.24
        WHEN crsite='HARRMO' THEN.23
        WHEN crsite='HOUSTX' THEN.08
        WHEN crsite='INVGMN' THEN.10
        WHEN crsite='IRVITX' THEN.10
        WHEN crsite='LARETX' THEN.09
                WHEN crsite='MANTIL' THEN.22
        ELSE NULL END) IS NOT NULL

In your case, it can be simplified to....

[tt][blue]WHERE crsite IN ('COLU2','COLUOH','GARYIN','HARRMO',
'HOUSTX','INVGMN','IRVITX','LARETX',
'MANTIL')
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

Just for people's sanity could you please represent your numbers correctly?

WHEN crsite='HARRMO' THEN [COLOR=black yellow]0.[/color]23

and if you like, you can simplify your CASE statement to make it easier to read (although it will be processed identically by the query engine):

CASE crsite
   WHEN 'COLU2'  THEN 0.03
   WHEN 'COLUOH' THEN 0.20
   WHEN 'GARYIN' THEN 0.24
   WHEN 'HARRMO' THEN 0.23
   WHEN 'HOUSTX' THEN 0.08
   WHEN 'INVGMN' THEN 0.10
   WHEN 'IRVITX' THEN 0.10
   WHEN 'LARETX' THEN 0.09
   WHEN 'MANTIL' THEN 0.22
   ELSE NULL
END

See how much easier to read that is?

 

[r937]

Usually, the best way to accommodate this is to put the case/when in the where clause.

i disagree ;-)


the best way is to leave the CASE in the SELECT clause, "push" the SELECT clause down one level into a subquery, i.e. derived table, and then go ahead and use the alias in the WHERE clause of the outer query

so change this --

SELECT newreg AS region
     , crsite AS location
     , CASE WHEN crsite='COLU2'  THEN 0.03
            WHEN crsite='COLUOH' THEN 0.2
            WHEN crsite='GARYIN' THEN 0.24
            WHEN crsite='HARRMO' THEN 0.23 
            WHEN crsite='HOUSTX' THEN 0.08 
            WHEN crsite='INVGMN' THEN 0.10 
            WHEN crsite='IRVITX' THEN 0.10
            WHEN crsite='LARETX' THEN 0.09 
            WHEN crsite='MANTIL' THEN 0.22
            ELSE NULL END   AS goalredux      
  FROM OAKEJ_NEWSHPLST1    
 WHERE goalredux IS NOT NULL  -- invalid

to this --

[red]SELECT region
     , location
     , goalredux
  FROM ([/red]
       SELECT newreg AS region
            , crsite AS location
            , CASE WHEN crsite='COLU2'  THEN 0.03
                   WHEN crsite='COLUOH' THEN 0.2
                   WHEN crsite='GARYIN' THEN 0.24
                   WHEN crsite='HARRMO' THEN 0.23 
                   WHEN crsite='HOUSTX' THEN 0.08 
                   WHEN crsite='INVGMN' THEN 0.10 
                   WHEN crsite='IRVITX' THEN 0.10
                   WHEN crsite='LARETX' THEN 0.09 
                   WHEN crsite='MANTIL' THEN 0.22
                   ELSE NULL END   AS goalredux      
         FROM OAKEJ_NEWSHPLST1    
       [red]) as derivedtable[/red] 
 WHERE goalredux IS NOT NULL  -- valid!! ;o)

r937.com | rudy.ca

 

[SQLDenis]

i disagree also to all of the above

why not create a lookup table and join on that instead of nast CASE statements

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

I was about to say that, Denis

I fully agree it's the best long-term solution.

 

[SQLSister]

Yep Denis, that's the best solution. That way when they want to add another value, all they need to do is add it once to the table and all code that uses those values will automatically reflect it rather than having to go search out all the procs or inline code that might be using the same case statment.

"NOTHING is more important in a database than integrity." ESquared