SQL Report Error - [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name

Question

We use a housing software based on ColdFusion and uses SQL to create database functions in our reports. I am working on a custom report to try to subtract one column from another: more specifically subtract COUNT_OF_INSPECTION_TYPE from OCCUPANCY. The OCCUPANCY column is based on the following code:

CONVERT(INT
        , CASE 
               WHEN tblHalls.HALLNAME = 'Hall1' THEN 198 
               WHEN tblHalls.HALLNAME = 'Hall2' THEN 430  
               WHEN tblHalls.HALLNAME = 'Hall3' THEN 333 
         END
      )

When I try a new function OCCUPANCY - COUNT_OF_INSPECTION_TYPE, I get an error:

Unable to invoke CFC - Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'OCCUPANCY'.

I'm not sure if I am explaining this right. I'd appreciate any help you can offer.

What kind of function exactly? Which application is throwing the error? Without knowing more, this sounds like a SQL or reporting issue. However, it is hard to advise without more details. — Leigh
This is the error I receive ERROR MESSAGE: Unable to invoke CFC - Error Executing Database Query. ERROR DETAIL: [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'OCCUPANCY'. — DANNYJ1
(Edit) Ohh... you cannot use an alias in a calculation at the same level (ie before it is defined). The choices are to either a) repeat the whole case statement in the calculation OR b) use another option like a CTE, derived query, the `apply operator etcetera. See sqlmag.com/blog/tip-apply-and-reuse-column-aliases — Leigh
Can you give me an example of what you're talking about? I am struggling to understand. My knowledge of SQL is VERY BASIC. — DANNYJ1
SQL processes things out of the order that you code it in. Basic flow is: FROM >> WHERE >> GROUP BY >> HAVING >> SELECT >> ORDER BY >> TOP. You can't access a variable in a higher process. — Shawn

Leigh Leigh · Accepted Answer · 2017-05-09T01:35:34

You cannot create an alias and use it in another calculation, at the same level, because the alias is not defined yet. Either repeat the whole CASE ... END statement in the calculation (less desirable) OR use another option, such as a CTE, derived table, APPLY operator, etcetera.

Wrapping your existing SELECT in a CTE is probably one of the simplest options:

;WITH cte
AS
(
    -- Your current SELECT statement goes here
    SELECT 
        CONVERT(INT
                , CASE 
                       WHEN tblHalls.HALLNAME = 'Hall1' THEN 198 
                       WHEN tblHalls.HALLNAME = 'Hall2' THEN 430  
                       WHEN tblHalls.HALLNAME = 'Hall3' THEN 333 
                 END
        ) 
        AS OCCUPANCY
        , tblHalls.COUNT_OF_INSPECTION_TYPE
        , ... (Other Columns)
    FROM  tblHalls
)
-- Now use the alias in a calculation
SELECT cte.*
        , cte.OCCUPANCY - cte.COUNT_OF_INSPECTION_TYPE AS SomeAliasForThisCol
FROM   cte

Side note, since the CASE statement does not define an ELSE condition, the result of the calculation will be NULL if none of HallName's match. If that is not desirable, consider setting a default.

SQL Report Error - [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name

1 Answers