March 2, 2009 at 2:49 pm
Hi,
We are just converting an Access 2002 database to SQL Server 2005. The access database has over 100 queries and a number of them use a calculation result from one column in another i.e. a+b=c c/a=e etc.
Having now read a number of various items on the web, I am begining to think this is not possible using SQL Server, this would mean a complete re-write of a very large website.
Please let me know if there is a way around this an some example code if possible.
Thanks.
March 2, 2009 at 3:26 pm
I can assure you that SQL Server can handle this type of calculation. Please post an example so we can see what you are up against.
Kind regards,
Lee
March 2, 2009 at 3:46 pm
Hi Lee,
Example as requested (does not contain whole query but gives the idea):
SELECT VHCWorkTable.id, VHCSalesTable.IDfromWorkTable, VHCSalesTable.AccountNumber, VHCWorkTable.AccountNumber, VHCWorkTable.RetailLabRate, VHCWorkTable.VATRate, VHCWorkTable.Other1Time, VHCWorkTable.Other2Time, VHCWorkTable.Other3Time, VHCWorkTable.Other4Time, VHCWorkTable.Other5Time, VHCWorkTable.Other6Time, VHCWorkTable.Other7Time, VHCWorkTable.Other8Time, VHCWorkTable.Other9Time, VHCWorkTable.Other10Time, ([Other1Time]*[VHCWorkTable.RetailLabRate]) AS Other1TotalLab, ([Other1TotalLab]*[VHCWorkTable.VATRate]/100)+([Other1TotalLab]) AS Other1TotalLabincVAT, ([Other2Time]*[VHCWorkTable.RetailLabRate]) AS Other2TotalLab, ([Other2TotalLab]*[VHCWorkTable.VATRate]/100)+([Other2TotalLab]) AS Other2TotalLabincVAT, ([Other3Time]*[VHCWorkTable.RetailLabRate]) AS Other3TotalLab, ([Other3TotalLab]*[VHCWorkTable.VATRate]/100)+([Other3TotalLab]) AS Other3TotalLabincVAT, ([Other4Time]*[VHCWorkTable.RetailLabRate]) AS Other4TotalLab, ([Other4TotalLab]*[VHCWorkTable.VATRate]/100)+([Other4TotalLab]) AS Other4TotalLabincVAT, ([Other5Time]*[VHCWorkTable.RetailLabRate]) AS Other5TotalLab, ([Other5TotalLab]*[VHCWorkTable.VATRate]/100)+([Other5TotalLab]) AS Other5TotalLabincVAT, ([Other6Time]*[VHCWorkTable.RetailLabRate]) AS Other6TotalLab, ([Other6TotalLab]*[VHCWorkTable.VATRate]/100)+([Other6TotalLab]) AS Other6TotalLabincVAT
FROM VHCWorkTable INNER JOIN VHCSalesTable ON VHCWorkTable.id = VHCSalesTable.IDfromWorkTable;
Thanks,
Colin
March 3, 2009 at 2:38 am
Colin,
Rather than repeatedly use the column alias 'Other1TotalLab' in the next column, just repeat the calculation. (Maybe a performance guru will shout at me here!) Also if you add 1 to the vat calculation the multiplication becomes something like * 1.175 (assume 17.5 vat ) so you don't need to add Other1TotalLab back in again.
eg
select
([Other1Time]*[VHCWorkTable.RetailLabRate]) AS Other1TotalLab,
([Other1Time]*[VHCWorkTable.RetailLabRate]*((VHCWorkTable.VATRate/100) + 1)) AS Other1TotalLabincVAT
etc etc
Not tested this but I think it is along the right lines.
Tony
March 3, 2009 at 9:22 am
This is correct. You CANNOT create an alias in one column and use it in the next. Limey has the right idea. you will need to replace it with the complete calcuation.
Cheers,
Brian
March 3, 2009 at 9:33 am
Absolutely correct - you will have a few things to rewrite, and this is one of them. Macros, DLOOKUP, etc. are other things that you might run into...there won't be a 100% direct replacement for some things such as these, but you wouldn't think that everything ports without some work. I have converted several and the query you mention shouldn't be a deal killer. You'll be much happier on SQL Server once you get past some of the conversion pains.
Best of luck!
March 3, 2009 at 10:01 am
What you CAN do is use what is called a CTE (Common Table Expression), which allows you to create reusable expressions.
So - you'd have to build a CTE that holds the items that are reused by others, and the outer query then reuses those items.
Something like:
;With InnerCTE as
(
select
([Other1Time]*[VHCWorkTable.RetailLabRate]) AS Other1TotalLab,
etc...)
SELECT
[Other1TotalLab]*((VHCWorkTable.VATRate/100) + 1) AS Other1TotalLabincVAT
--other fields here
from InnerCTE
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 5, 2009 at 2:56 am
Thanks All,
I guess I have some writing to do!
Regards,
Colin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply