Query to compare cast in select statement

Question

I have a query where I am pulling in three fields, and converting them to a date. I'm hoping to then run a comparison within the same select statement, but I get an invalid column name on the field I'm attempting to compare. The order by is working correctly though. /scratching_head

SQL Server 2012

SELECT 
UTCSID,
UTLCID,

/* put utonmm, utondd, utonyy together as a date called uton */
CAST(
  CAST(UTONMM as varchar) + '/' + 
  CAST(UTONDD as varchar) + '/' +
  CASE WHEN UTONCV = '1' 
    THEN RIGHT('20' + CONVERT(varchar(4), RIGHT('00' + CONVERT(varchar(4), UTONYY),2)),4)
  ELSE RIGHT('19' + CONVERT(varchar(4), UTONYY),4)
  END 
AS DATETIME) AS UTON, 

/* put utofmm, utofdd, utofyy together as a date called utoff */
CAST(
CASE WHEN UTOFMM > '0' 
THEN
    CAST(UTOFMM as varchar) + '/' + 
    CAST(UTOFDD as varchar) + '/' +
    CASE WHEN UTOFCV = '1' 
        THEN RIGHT('20' + CONVERT(varchar(4), RIGHT('00' + CONVERT(varchar(4), UTOFYY),2)),4)
        ELSE RIGHT('19' + CONVERT(varchar(4), UTOFYY),4)
    END
END
AS DATETIME) AS UTOFF,

UTCBAL,
UTDBAL,
UTUNPS

FROM [HTEDTA].[THOR].[HTEDTA].UT210AP
WHERE UTLCID = '885570' AND UTOFF > GETDATE() ORDER BY UTON DESC

This statement returns:

Invalid column name: 'UTOFF'

I don't think you can compare a result as a column even though it appears to resolve that way. The only way I know of to do this is to make this WHOLE thing a derived table, and query that. (Wrap it in another SELECT statement.) — durbnpoisn, Oct 19 '15 at 17:47

score 0 · Accepted Answer · answered Oct 19 '15 at 17:49

The problem is that the alias utoff isn't known at the time when the where clause is parsed.

One way to get around it is to wrap the query in a common table expression and apply the where clause to that:

WITH CTE AS (
    SELECT UTCSID, UTLCID,    
    /* put utonmm, utondd, utonyy together as a date called uton */
    CAST(
     CAST(UTONMM as varchar) + '/' + 
     CAST(UTONDD as varchar) + '/' +
     CASE WHEN UTONCV = '1' 
       THEN RIGHT('20' + CONVERT(varchar(4), RIGHT('00' + CONVERT(varchar(4), UTONYY),2)),4)
     ELSE RIGHT('19' + CONVERT(varchar(4), UTONYY),4)
     END 
    AS DATETIME) AS UTON, 

    /* put utofmm, utofdd, utofyy together as a date called utoff */
    CAST(
    CASE WHEN UTOFMM > '0' 
    THEN
       CAST(UTOFMM as varchar) + '/' + 
       CAST(UTOFDD as varchar) + '/' +
       CASE WHEN UTOFCV = '1' 
          THEN RIGHT('20' + CONVERT(varchar(4), RIGHT('00' + CONVERT(varchar(4), UTOFYY),2)),4)
          ELSE RIGHT('19' + CONVERT(varchar(4), UTOFYY),4)
       END
    END
    AS DATETIME) AS UTOFF,

    UTCBAL, UTDBAL, UTUNPS    
    FROM [HTEDTA].[THOR].[HTEDTA].UT210AP
)
SELECT * 
FROM CTE
WHERE UTLCID = '885570' AND UTOFF > GETDATE()

That should work great. a CTE is stored in TempDB and then destroyed at the end of the query? — Mike Andrews, Oct 19 '15 at 17:56
@MikeAndrews Yes, it's a temporary result set that vanishes after the query has been executed. — jpw, Oct 19 '15 at 17:57

Query to compare cast in select statement

1 Answers1