Select addrid,addrkey,addrline1,addrline2,addrline3,My question is, how do I reference a calculated column in the WHERE clause?
CASE month(getdate()) WHEN 1 THEN addrjan WHEN 2 THEN addrfeb WHEN 3 THEN addrmar WHEN 4 THEN addrapr WHEN 5 THEN addrmay WHEN 6 THEN
addrjun WHEN 7 THEN addrjul WHEN 8 THEN addraug WHEN 9 THEN addrsep WHEN
10 THEN addroct WHEN 11 THEN addrnov WHEN 12 THEN addrdec END AS ThisMonth FROM dbo.address left outer join dbo.jobs on dbo.address.addrlnkjob = dbo.jobs.jobskey WHERE (addrlocatr = 'g') and addrid = '0000035682
select tmpTbl.colx, ... from (select ... <computed column> colx, ... from realTable1, realTable2, ... where ...) tmpTbl where tmpTbl.colx = <some value>Essentially, you are creating a one-time View inside the From clause, and then querying against the view. Suppose you want to generate a list of preferred customers and their average sales amount over the past 30 days when their average sales amount for that period is greater than $1000. (Assume a simple table with customername, salesdatetime, and salesamount - one row per sales transaction.) You could do this with (Oracle syntax):
select tmpTbl.thename, tmpTbl.averagesales from ( select customername as thename, avg(salesamount) as averagesales from customersales where salesdatetime >= (sysdate-30) group by customername ) tmpTbl where tmpTbl.averagesales > 1000;This creates a nested table (a temporary view) containing all the customers and their average sales for the past 30 days - one row per customer. Then the outer query returns just the ones where the average sales amount is greater than $1000.