How can I use an automatically generated field(column) that is derived from
other columns of the same table as a variable in generating another column
within the same query on SQL Server.
I tried this on MS Access and it worked quite well.
An example of such query is given below:

SELECT debits, credits, balance, (balance-credits-debits) AS
previousbalance, (previousbalance-balance) AS movement
FROM sampletable;

Thanks for anticipated response.

Does this query not work in SQL Server or did I miss the point because it
looks good to me. Where does the variable bit fit in? DTS is interested in
MetaData so

SELECT ..........................(Horse + Dog) as Collection.............
SELECT ..........................(a + b) as Collection.............
SELECT ..........................(1 + 2) as Collection.............
SELECT ..........................(Me + You) as Collection.............

will all work providing the output is compatible.

If you need to dynamically suggest what is in the parentheses then you can
use this

Global Variables and SQL statements in DTS
(http://www.sqldts.com/Default.aspx?205)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"saomot2000" <saomot2000 (AT) discussions (DOT) microsoft.com> wrote