July 7, 2006 at 1:01 pm
Referencing a defined field
I’m in the process of learning SQL Server 2000, having migrated from an IBM as/400 (or iSeries). On the as/400, I could have an SQL statement like this:
Select jan_amt, feb_amt, mar_amt, jan_amt+feb_amt+mar_amt as qtr1_amt,
apr_amt, may_amt, jun_amt, apr_amt + may_amt + jun_amt as qtr2_amt,
qtr1_amt + qtr2_amt as half_year_amt
From SalesFile
Where qtr1_amt > 10000
In other words, new fields named “qtr1_amt” and “qtr2_amt” are defined in the Select statement, and then those fields are referenced later in the same SQL statement. However, it looks like SQL Server 2000 doesn’t support this. Instead, the SQL statement would have to be coded like this:
Select jan_amt, feb_amt, mar_amt, jan_amt+feb_amt+mar_amt as qtr1_amt,
apr_amt, may_amt, jun_amt, apr_amt + may_amt + jun_amt as qtr2_amt,
jan_amt+feb_amt+mar_amt + apr_amt + may_amt + jun_amt as half_year_amt
From SalesFile
Where jan_amt+feb_amt+mar_amt > 10000
Is there a way in SQL Server 2000 to define a new field in the SQL statement, and then reference it later in that SQL statement?
July 7, 2006 at 1:15 pm
select jan_amt, feb_amt, mar_amt, Qtr1_amt,
apr_amt, may_amt, jun_amt, Qt2_amt,
Half_Year_amt
FROM (
Select jan_amt, feb_amt, mar_amt, jan_amt+feb_amt+mar_amt as qtr1_amt,
apr_amt, may_amt, jun_amt, apr_amt + may_amt + jun_amt as qtr2_amt,
jan_amt+feb_amt+mar_amt + apr_amt + may_amt + jun_amt as half_year_amt
From SalesFile) as a
Where a.Qtr1_amt > 10000
July 7, 2006 at 1:31 pm
Thanks!
Could you also have something like this:
select jan_amt, feb_amt, mar_amt, Qtr1_amt,
apr_amt, may_amt, jun_amt, Qt2_amt,
Half_Year_amt
FROM (
Select *, jan_amt+feb_amt+mar_amt as qtr1_amt,
apr_amt + may_amt + jun_amt as qtr2_amt,
jan_amt+feb_amt+mar_amt + apr_amt + may_amt + jun_amt as half_year_amt
From SalesFile) as a
Where a.Qtr1_amt > 10000
In other words, use the * in the inside select so that you don't have to list all of the fields.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply