Referencing a field defined in the Select

  • 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?

  • 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

  • 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