March 7, 2005 at 5:50 am
Hi,
I wonder if anyone has come across a similar problem that I am facing at the moment.
I am importing my Access DB into MS SQL 2000 server DB. I used Upsizing Wizard to get the tables imported. However, all queries must be imported manually one by one.
I have been using calculation aliases in my Access DB, and then using those aliases in another expressions. For some reasons SQL2000 would not allow me to do the same. It would complain that my alias is not a part of the query. Here is my Access query:
SELECT Trades.TradeID, Trades.TradeDate, Trades.SettlementDate, Trades.GSSettlementDate, Trades.BrokerID, Trades.TraderID, Trades.CustomerID, Trades.SideID, Trades.MarketID, Trades.CurrencyID, Trades.Quantity, Trades.TickerID, Trades.RateBroker, Trades.RateClientGross, Trades.RateClientNet, Trades.ExecChargeAdjustment, Trades.Fee*Abs(Trades.Quantity)+Trades.ExecChargeAdjustment AS ExecCharge, Trades.RateBroker*Trades.Quantity+ExecCharge AS NetBroker, Trades.RateClientGross*Abs(Trades.Quantity) AS NetClientGross, Trades.RateClientNet*Abs(Trades.Quantity) AS NetClientNet
FROM Trades;
As you can see, ExecCharge is firstly defined, and then I am trying to use it as a part of another expression. MS Access does not have a problem with this, but SQL2000 seems to have.
I hope someone can help me with this. Thanks in advance.
Martyn
March 7, 2005 at 7:51 am
In SQL Server you have two options.
1. Repeat the expression
2. Use the expressions on a subquery like
Select .... NewAlias + ....
from ( select *, (fld1+fld2 * fld3 ) as NewAlias from tbl) a
HTH
* Noel
March 7, 2005 at 8:14 am
Thanks Noel, option 2 works really well for me. Cheers.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply