Comparing to Columns from the same table

Question

Post reply

Comparing to Columns from the same table

aznabeel

SSC Veteran

Points: 203
More actions
January 8, 2020 at 8:18 am

#3713511

I am subtracting two columns (_REG_READING- START_REG_READING) and want to match the output (ActualRead) with another columns (FINAL_REG_QTY) to know if there is any difference. But I am getting the error message “invalid identifier”
Below is the query…
select BSEG_ID,SP_ID,FINAL_REG_QTY,START_REG_READING, END_REG_READING,END_REG_READING- START_REG_READING AS ActualRead
from CI_BSEG_READ
where FINAL_REG_QTY <> ActualRead
=----
If someone can help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

This is down to logical query processing order.

SQL doesn't process the query from top to bottom, instead it breaks it up and starts with the FROM clause.

The WHERE clause is done about 4th in the process and the SELECT about 8th in the process. As such the alias for ActualRead is not known to the query until the SELECT phase which is after the WHERE.

What you would need to do is to put the SELECT phase into a CTE and then issue the where clause against the CTE, something like the below

;WITH CTE AS

(select BSEG_ID,SP_ID,FINAL_REG_QTY,START_REG_READING, END_REG_READING,END_REG_READING- START_REG_READING AS ActualRead

from CI_BSEG_READ)

SELECT * FROM CTE

where FINAL_REG_QTY <> ActualRead

Or you can negate the CTE and do the calculation in the WHERE clause

select BSEG_ID,SP_ID,FINAL_REG_QTY,START_REG_READING, END_REG_READING,END_REG_READING- START_REG_READING AS ActualRead

from CI_BSEG_READ

where FINAL_REG_QTY <> (END_REG_READING- START_REG_READING)

This reply was modified 4 years, 10 months ago by Ant-Green. Reason: Additional solution

ScottPletcher SSC Guru Points: 101096 More actions · Answer 2

I prefer using CROSS APPLY to assign alias names. CAs can also cascade, where one alias can be used to create another alias(es). That's very helpful sometimes.

select BSEG_ID,SP_ID,FINAL_REG_QTY,START_REG_READING, END_REG_READING,

ActualRead

from CI_BSEG_READ

cross apply (

select END_REG_READING - START_REG_READING AS ActualRead

) as aliases1

where FINAL_REG_QTY <> ActualRead

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".