By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,212 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,212 IT Pros & Developers. It's quick & easy.

Caculated Fields in a Query

P: 76
Hi Guys,

Sorry if this is a really stupid question. I am trying to upsize my Access database to SQL server. When I used the Access upsizing wizard, some of my queries didn't get upsized so I am building them as views in SQL server. I am having problems doing calculations on fields within the query. For instance, I need to have the following calculation performed on the query below. I want it contained within this query because it is the control source of one of my reports:

Expand|Select|Wrap|Line Numbers
  1. Round([DentalYESNO]*(15*[Area Factor]*[Network Factor]*[Trend Factor]*[Primary Age Gender Factor]*1.42857+[Primary Rider Factor]),2) AS [Primary Rate], 
would be the next field in this query:

Expand|Select|Wrap|Line Numbers
  1. SELECT     CASE WHEN dbo.[Final Action].[Initial Pay Mode] = 'Monthly' THEN 1 WHEN dbo.[Final Action].[Initial Pay Mode] = 'Quarterly' THEN 3 WHEN dbo.[Final Action].[Initial Pay Mode]
  2.                        = 'SemiAnnual' THEN 6 ELSE 0 END AS ModalFactor, CASE WHEN dbo.[Final Action].[Spouse Build Rate] > 0 AND 
  3.                       dbo.[Final Action].[Spouse Non-Build Rate] > 0 THEN (dbo.[Final Action].[Spouse Non-Build Rate] - 1) + (dbo.[Final Action].[Spouse Build Rate] - 1) 
  4.                       + 1 ELSE 0 END AS [Spouse Total Rateup], CASE WHEN dbo.[Final Action].[Primary Build Rate] > 0 AND 
  5.                       dbo.[Final Action].[Primary Non-Build Rate] > 0 THEN (dbo.[Final Action].[Primary Non-Build Rate] - 1) + (dbo.[Final Action].[Primary Build Rate] - 1) 
  6.                       + 1 ELSE 0 END AS [Primary Total Rateup], dbo.[PR Only Info].[List Name], dbo.[PR Only Info].[Prospect Number], dbo.[Final Action].Fname, 
  7.                       dbo.[Final Action].LNAme, dbo.[Dental Area Factors].[Area Factor], dbo.[Dental Network Factors].[Network Factor], dbo.[Final Action].[FPMC no Rateup],
  8.                           (SELECT     [Trend Factor]
  9.                             FROM          dbo.[Dental Trend Factors]
  10.                             WHERE      dbo.[Dental Trend Factors].State = CASE WHEN dbo.[Final Action].[Issue State] = 'FL' THEN 'FL' ELSE 'GN' END AND 
  11.                                                    dbo.[Final Action].[Eff Date] BETWEEN dbo.[Dental Trend Factors].[Eff Date] AND dbo.[Dental Trend Factors].[End Date]) AS [Trend Factor], 
  12.                       CASE WHEN dbo.[Final Action].[Dental Product] = 1 THEN 1 ELSE 0 END AS DentalYesNo, dbo.[Final Action].[Dental Product],
  13.                           (SELECT     [Age Gender Factor]
  14.                             FROM          dbo.[Dental Age Gender Rider Factors]
  15.                             WHERE      dbo.[Dental Age Gender Rider Factors].state = CASE WHEN dbo.[Final Action].[Issue State] = 'FL' THEN 'FL' ELSE 'GN' END AND 
  16.                                                    dbo.[Final Action].Age BETWEEN dbo.[Dental Age Gender Rider Factors].[Age From] AND 
  17.                                                    dbo.[Dental Age Gender Rider Factors].[Age to] AND dbo.[Final Action].Gender = dbo.[Dental Age Gender Rider Factors].Gender) 
  18.                       AS [Primary Age Gender Factor],
  19.                           (SELECT     [Rider Factor]
  20.                             FROM          dbo.[Dental Age Gender Rider Factors]
  21.                             WHERE      dbo.[Dental Age Gender Rider Factors].state = CASE WHEN dbo.[Final Action].[Issue State] = 'FL' THEN 'FL' ELSE 'GN' END AND 
  22.                                                    dbo.[Final Action].Age BETWEEN dbo.[Dental Age Gender Rider Factors].[Age From] AND 
  23.                                                    dbo.[Dental Age Gender Rider Factors].[Age to] AND dbo.[Final Action].Gender = dbo.[Dental Age Gender Rider Factors].Gender) 
  24.                       AS [Primary Rider Factor]
  25. FROM         dbo.[Dental Network Factors] INNER JOIN
  26.                       dbo.[Dental Area Factors] ON dbo.[Dental Network Factors].State = dbo.[Dental Area Factors].State INNER JOIN
  27.                       dbo.[Dental ZIP Codes] ON dbo.[Dental Network Factors].State = dbo.[Dental ZIP Codes].State AND 
  28.                       dbo.[Dental Network Factors].[State Region] = dbo.[Dental ZIP Codes].[State Region] AND 
  29.                       dbo.[Dental Area Factors].State = dbo.[Dental ZIP Codes].State AND 
  30.                       dbo.[Dental Area Factors].[State Region] = dbo.[Dental ZIP Codes].[State Region] INNER JOIN
  31.                       dbo.[Final Action] ON dbo.[Dental ZIP Codes].ZIP = dbo.[Final Action].Zip INNER JOIN
  32.                       dbo.[PR Only Info] ON dbo.[Final Action].[Master ID] = dbo.[PR Only Info].ID
When I try to add the calculated field, I get an error message that says "'Trend Factor' in expression is not part of the query." This is my first experience with SQL Server, so again, I apologize if I'm missing something really obvious.

Dec 11 '06 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 168
Trend Factor is an alias for certain computed column. You cannot use aliases in the statements of the same select query, so you may write it as following:
Expand|Select|Wrap|Line Numbers
  1. select *, Round([DentalYESNO]*(15*[Area Factor]*[Network Factor]*[Trend Factor]*[Primary Age Gender Factor]*1.42857+[Primary Rider Factor]),2) AS [Primary Rate]
  2. from
  4.   ) Source
Dec 11 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.