Caculated Fields in a Query

[jpatchak]

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:

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:

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]
                       = 'SemiAnnual' THEN 6 ELSE 0 END AS ModalFactor, CASE WHEN dbo.[Final Action].[Spouse Build Rate] > 0 AND 
                      dbo.[Final Action].[Spouse Non-Build Rate] > 0 THEN (dbo.[Final Action].[Spouse Non-Build Rate] - 1) + (dbo.[Final Action].[Spouse Build Rate] - 1) 
                      + 1 ELSE 0 END AS [Spouse Total Rateup], CASE WHEN dbo.[Final Action].[Primary Build Rate] > 0 AND 
                      dbo.[Final Action].[Primary Non-Build Rate] > 0 THEN (dbo.[Final Action].[Primary Non-Build Rate] - 1) + (dbo.[Final Action].[Primary Build Rate] - 1) 
                      + 1 ELSE 0 END AS [Primary Total Rateup], dbo.[PR Only Info].[List Name], dbo.[PR Only Info].[Prospect Number], dbo.[Final Action].Fname, 
                      dbo.[Final Action].LNAme, dbo.[Dental Area Factors].[Area Factor], dbo.[Dental Network Factors].[Network Factor], dbo.[Final Action].[FPMC no Rateup],
                          (SELECT     [Trend Factor]
                            FROM          dbo.[Dental Trend Factors]
                            WHERE      dbo.[Dental Trend Factors].State = CASE WHEN dbo.[Final Action].[Issue State] = 'FL' THEN 'FL' ELSE 'GN' END AND 
                                                   dbo.[Final Action].[Eff Date] BETWEEN dbo.[Dental Trend Factors].[Eff Date] AND dbo.[Dental Trend Factors].[End Date]) AS [Trend Factor], 
                      CASE WHEN dbo.[Final Action].[Dental Product] = 1 THEN 1 ELSE 0 END AS DentalYesNo, dbo.[Final Action].[Dental Product],
                          (SELECT     [Age Gender Factor]
                            FROM          dbo.[Dental Age Gender Rider Factors]
                            WHERE      dbo.[Dental Age Gender Rider Factors].state = CASE WHEN dbo.[Final Action].[Issue State] = 'FL' THEN 'FL' ELSE 'GN' END AND 
                                                   dbo.[Final Action].Age BETWEEN dbo.[Dental Age Gender Rider Factors].[Age From] AND 
                                                   dbo.[Dental Age Gender Rider Factors].[Age to] AND dbo.[Final Action].Gender = dbo.[Dental Age Gender Rider Factors].Gender) 
                      AS [Primary Age Gender Factor],
                          (SELECT     [Rider Factor]
                            FROM          dbo.[Dental Age Gender Rider Factors]
                            WHERE      dbo.[Dental Age Gender Rider Factors].state = CASE WHEN dbo.[Final Action].[Issue State] = 'FL' THEN 'FL' ELSE 'GN' END AND 
                                                   dbo.[Final Action].Age BETWEEN dbo.[Dental Age Gender Rider Factors].[Age From] AND 
                                                   dbo.[Dental Age Gender Rider Factors].[Age to] AND dbo.[Final Action].Gender = dbo.[Dental Age Gender Rider Factors].Gender) 
                      AS [Primary Rider Factor]
FROM         dbo.[Dental Network Factors] INNER JOIN
                      dbo.[Dental Area Factors] ON dbo.[Dental Network Factors].State = dbo.[Dental Area Factors].State INNER JOIN
                      dbo.[Dental ZIP Codes] ON dbo.[Dental Network Factors].State = dbo.[Dental ZIP Codes].State AND 
                      dbo.[Dental Network Factors].[State Region] = dbo.[Dental ZIP Codes].[State Region] AND 
                      dbo.[Dental Area Factors].State = dbo.[Dental ZIP Codes].State AND 
                      dbo.[Dental Area Factors].[State Region] = dbo.[Dental ZIP Codes].[State Region] INNER JOIN
                      dbo.[Final Action] ON dbo.[Dental ZIP Codes].ZIP = dbo.[Final Action].Zip INNER JOIN
                      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.

Thanks,
Josh

[almaz]

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:

select *, Round([DentalYESNO]*(15*[Area Factor]*[Network Factor]*[Trend Factor]*[Primary Age Gender Factor]*1.42857+[Primary Rider Factor]),2) AS [Primary Rate]
from
  ( YOUR_BIG_SELECT_STATEMENT
  ) Source