January 8, 2020 at 8:18 am
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
January 8, 2020 at 8:41 am
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)
January 8, 2020 at 2:39 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply