Solved

Help Referencing SQL Calculated Field (Alias) in Subqueries

Posted on 2007-12-06
10
2,666 Views
Last Modified: 2011-09-20
I am writing a complex SQL query that will calculate a variety of price and tax calculations in one shot.  This may not be the best approach so I'm open to suggestions.  The input for this query is a derived from a Master table and a series of associated lookup tables that are INNER JOINED in the query.  The DB is normalized.  

The major problem I'm having is many of the price and tax calculations are built based on other subqueries which result in calculated fields.  I need to reference these calculated fields as I move deeper and deeper into the query writing, its gets exponentially more complex because SQL 2005 does not let me reference "calculated fields from previous subqueries in another subquery.  

I've attached an example.  Any help would be appreciated as I seem to get stuck on this concept fairly often.  
Select
 

I.ItemID,

I.ItemNum,

I.LeadPPM,

I.VendorPartNum		as 'VendorPartNum1',

I.StdLeadTime		as 'StdLeadTime1',

I.PurchaseCost		as 'StandardCost',

I.PurchaseQty,

C.Category,

V.Vendor			as 'Vendor1',

VF.VendorFactory	as 'VendorFactory1',

CT.Country			as 'Country1',

H.DutyRate			as 'BaseHTSDuty1',

M.Material,		

  

-- Vendor1 HTS Duty Calculation

Case

	When	(V.CountryID=1) or (I.HTSAFLAG=1) then 0

	When	(I.HTSCodeID = null) then ''

	When	((V.CountryID = 9) and (M.MaterialID = 35) and ('XFactoryReplacementCost' > 1.5)) then 0.05

	When	(V.CountryID = 9) then 0

	Else	H.DutyRate

End As		CfmHTSDuty1,
 

--MaxReplacementCostDate

Case 

	When	(SELECT Max(CH.ReplacementCostDate) FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID)=Null then ''

	Else	(SELECT Max(CH.ReplacementCostDate) FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID)

End As		MaxReplacementCostDate,
 

--XFactoryReplacementCost

--Problem 1 - Looks like SQL 2005 let me reference calculated field 'XFactoryReplacementCost' in Vendor 1 HTS Duty Calc above, so I tried referencing MaxReplacementCostDate in subquery below

--It didn't like me referencing it directly so I tried convertig to datetime.  

--It will work if I put in entire calculating query used to derive MaxReplacementCostDate, but I'd rather use calculated field alias.  

Case 

	When	(SELECT CH.ReplacementCost FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate'))=Null then ''

	Else	(SELECT CH.ReplacementCost FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate'))

End As		XFactoryReplacementCost,
 

--Handling Fee

'XFactoryReplacementCost' * .1

As HandlingFee,
 

--COO Duty

-- This is where it gets complicated because this calculation is based on the data derived from 4 calculated fields above

-- They are CfmHTSDuty1, MaxReplacementCostDate, XFactoryReplacementCost, HandlingFee

-- After this field, I have a few more that will use all calculated fields above.  You can see how this is getting to be very complex

-- with SQL 2005 not allowing me to directly reference the calculated fields in the subsequent subqueries
 
 

I.CreatedDate,

I.CreatedBy,

I.ModifiedDate,

I.ModifiedBy
 

from tItemMaster I
 

Left Join tRefCategory			C		on C.CategoryID = I.CategoryID

Left Join tRefVendors			V		on V.VendorID = I.VendorID

Left Join tRefVendorFactory		VF		on VF.VendorFactoryiD = I.VendorFactoryID

Left Join tRefCountry			CT		on CT.CountryID = V.CountryID

Left Join tRefHTSCode			H		on H.HTSCodeID = I.HTSCodeID

Left Join tRefMaterial			M		on M.MaterialID = I.MaterialID
 

where I.ItemID = 2

Open in new window

0
Comment
Question by:dpmoney
10 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
In that example which are the fields that you want reference?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Meanwhile I made a little modification to your select:
Select 

I.ItemID,

I.ItemNum,

I.LeadPPM,

I.VendorPartNum		as 'VendorPartNum1',

I.StdLeadTime		as 'StdLeadTime1',

I.PurchaseCost		as 'StandardCost',

I.PurchaseQty,

C.Category,

V.Vendor		as 'Vendor1',

VF.VendorFactory	as 'VendorFactory1',

CT.Country		as 'Country1',

H.DutyRate		as 'BaseHTSDuty1',

M.Material,		

  

-- Vendor1 HTS Duty Calculation

Case

	When	(V.CountryID=1) or (I.HTSAFLAG=1) then 0

	When	(I.HTSCodeID = null) then ''

	When	((V.CountryID = 9) and (M.MaterialID = 35) and ('XFactoryReplacementCost' > 1.5)) then 0.05

	When	(V.CountryID = 9) then 0

	Else	H.DutyRate

End As		CfmHTSDuty1,

 

--MaxReplacementCostDate

(SELECT ISNULL(Max(CH.ReplacementCostDate), '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID) MaxReplacementCostDate,

 

--XFactoryReplacementCost

--Problem 1 - Looks like SQL 2005 let me reference calculated field 'XFactoryReplacementCost' in Vendor 1 HTS Duty Calc above, so I tried referencing MaxReplacementCostDate in subquery below

--It didn't like me referencing it directly so I tried convertig to datetime.  

--It will work if I put in entire calculating query used to derive MaxReplacementCostDate, but I'd rather use calculated field alias.  

(SELECT ISNULL(Max(CH.ReplacementCost, '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate')) XFactoryReplacementCost, 

--Handling Fee

'XFactoryReplacementCost' * .1

As HandlingFee,

 

--COO Duty

-- This is where it gets complicated because this calculation is based on the data derived from 4 calculated fields above

-- They are CfmHTSDuty1, MaxReplacementCostDate, XFactoryReplacementCost, HandlingFee

-- After this field, I have a few more that will use all calculated fields above.  You can see how this is getting to be very complex

-- with SQL 2005 not allowing me to directly reference the calculated fields in the subsequent subqueries

 

 

I.CreatedDate,

I.CreatedBy,

I.ModifiedDate,

I.ModifiedBy

 

from tItemMaster I

 

Left Join tRefCategory			C		on C.CategoryID = I.CategoryID

Left Join tRefVendors			V		on V.VendorID = I.VendorID

Left Join tRefVendorFactory		VF		on VF.VendorFactoryiD = I.VendorFactoryID

Left Join tRefCountry			CT		on CT.CountryID = V.CountryID

Left Join tRefHTSCode			H		on H.HTSCodeID = I.HTSCodeID

Left Join tRefMaterial			M		on M.MaterialID = I.MaterialID

 

where I.ItemID = 2

Open in new window

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
In general, you cannot reference an alias in the query in which it is "created".  For example, this is not allowed:

SELECT a, a + 1 AS b,  --ok so far
    b +1 AS c  --not allowed, cannot reference alias "b"
FROM tableName
WHERE b > 14  --not allowed, cannot reference alias "b"
--you could of course repeat the entire expresslon, like so:
--WHERE a + 1 > 14


The common way to "get around this" is to use a subquery/derived table:

SELECT a, b, b + 1 AS c
FROM (
    SELECT a, a + 1 AS b
    FROM tableName
    --WHERE a + 1 > 14
) AS derived
WHERE b > 14
0
 

Author Comment

by:dpmoney
Comment Utility
VMontalvao:

In my example, I'm trying to have the XFactoryReplacementCost calculated field reference the MaxReplacementCostDate field calculated in the previous section above.

Then, I have another calculated field called COODuty (formula not shown) which will need to reference the 'MaxReplacementCostDate' calculated field and 'XFactoryReplacementCost' calculated field.  It just keeps getting harder because I have about 3 or 4 more calculated fields which need to reference all fields previously calculated.  I can do this in Access but am not enjoying the fact that T-SQL is not making this easy.  Should I be doing each key calculation as a separate derived table and then reference them that way.  This seems very complex.  Ideal solution would be to write the XFactoryReplacementCost subquery in such a way that it could simply reference the calculated field 'MaxReplacementCostDate' from above...Thanks for input from everyone!  Patiently waiting for more feedback.
0
 
LVL 11

Accepted Solution

by:
yuching earned 400 total points
Comment Utility
I think the query has problem, below doenst take the calculated field value, instead it take that as a string
-->   ... ('XFactoryReplacementCost' > 1.5))  
-->   'XFactoryReplacementCost' * .1    As HandlingFee,
-->  CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate'))=Null


Try Below query
Select * ,
  --- COO Duty, can compute using field CfmHTSDuty1, MaxReplacementCostDate, XFactoryReplacementCost, HandlingFee
From
(
    Select I.ItemID,I.ItemNum, I.LeadPPM, I.VendorPartNum as 'VendorPartNum1',
       I.StdLeadTime as 'StdLeadTime1', I.PurchaseCost as 'StandardCost',
       I.PurchaseQty, C.Category, V.Vendor as 'Vendor1', VF.VendorFactory as 'VendorFactory1',
       CT.Country as 'Country1', H.DutyRate as 'BaseHTSDuty1', M.Material,            
             
        -- Vendor1 HTS Duty Calculation
        Case When (V.CountryID=1) or (I.HTSAFLAG=1) then 0
                 When (I.HTSCodeID = null) then ''
                When ((V.CountryID = 9) and (M.MaterialID = 35) and
                     ( IsNull(ReplacementCost, 0) > 1.5)) then 0.05
                 When (V.CountryID = 9) then 0
                 Else H.DutyRate
        End As       CfmHTSDuty1,
     
        --MaxReplacementCostDate
        Case When MaxReplace.MaxReplacementDate Is Null Then ''
          Else MaxReplacementDate End As MaxReplacementCostDate,
     
        --XFactoryReplacementCost
        Case When ReplacementCost Is Null Then '' Else ReplacementCost
        End As XFactoryReplacementCost,
         
        --Handling Fee
        IsNull(ReplacementCost, 0) * .1 As HandlingFee,
     
      I.CreatedDate,I.CreatedBy,I.ModifiedDate,I.ModifiedBy
     
    from tItemMaster I
     
     -- Get the replacementcode for the max replacementdate
     Left Join (
          Select M.ItemID, M.VendorID, M.MaxReplacementDate,  T.ReplacementCost
          From
          (  
              Select ItemID, VendorID, Max(ReplacementCostDate) As MaxReplacementDate
              From tRefCostHistory
              Group By ItemID, VendorID
          ) M INNER JOIN tRefCostHistory T ON T.ItemID = M.ItemID
             And T.VendorID = M.VendorID And T.ReplacementCodeDate = M.MaxReplacementDate
      ) CH ON CH.ItemID=I.ItemID and CH.VendorID=I.VendorID
     
    Left Join tRefCategory      C on C.CategoryID = I.CategoryID
    Left Join tRefVendors      V on V.VendorID = I.VendorID
    Left Join tRefVendorFactory VF on VF.VendorFactoryiD = I.VendorFactoryID
    Left Join tRefCountry CT on CT.CountryID = V.CountryID
    Left Join tRefHTSCode      H on H.HTSCodeID = I.HTSCodeID
    Left Join tRefMaterial M      on M.MaterialID = I.MaterialID
    where I.ItemID = 2
)  T
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:dpmoney
Comment Utility
Thanks to all for your input.  I'll be back on-site at location with DBs on Mon, 12/10 and will test then.  Any more comments are welcome.  Thanks again!  More details to follow...
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
dpmoney, looks like you'll need to repeat SQL instructions, like this:
Select 

I.ItemID,

I.ItemNum,

I.LeadPPM,

I.VendorPartNum		as 'VendorPartNum1',

I.StdLeadTime		as 'StdLeadTime1',

I.PurchaseCost		as 'StandardCost',

I.PurchaseQty,

C.Category,

V.Vendor		as 'Vendor1',

VF.VendorFactory	as 'VendorFactory1',

CT.Country		as 'Country1',

H.DutyRate		as 'BaseHTSDuty1',

M.Material,		

  

-- Vendor1 HTS Duty Calculation

Case

	When	(V.CountryID=1) or (I.HTSAFLAG=1) then 0

	When	(I.HTSCodeID = null) then ''

	When	((V.CountryID = 9) and (M.MaterialID = 35) and ((SELECT ISNULL(Max(CH.ReplacementCost, '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate')) > 1.5)) then 0.05

	When	(V.CountryID = 9) then 0

	Else	H.DutyRate

End As		CfmHTSDuty1,

 

--MaxReplacementCostDate

(SELECT ISNULL(Max(CH.ReplacementCostDate), '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID) MaxReplacementCostDate,

 

--XFactoryReplacementCost

--Problem 1 - Looks like SQL 2005 let me reference calculated field 'XFactoryReplacementCost' in Vendor 1 HTS Duty Calc above, so I tried referencing MaxReplacementCostDate in subquery below

--It didn't like me referencing it directly so I tried convertig to datetime.  

--It will work if I put in entire calculating query used to derive MaxReplacementCostDate, but I'd rather use calculated field alias.  

(SELECT ISNULL(Max(CH.ReplacementCost, '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate')) XFactoryReplacementCost, 

--Handling Fee

(SELECT ISNULL(Max(CH.ReplacementCost, '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate')) * .1 As HandlingFee,

--COO Duty

-- This is where it gets complicated because this calculation is based on the data derived from 4 calculated fields above

-- They are CfmHTSDuty1, MaxReplacementCostDate, XFactoryReplacementCost, HandlingFee

-- After this field, I have a few more that will use all calculated fields above.  You can see how this is getting to be very complex

-- with SQL 2005 not allowing me to directly reference the calculated fields in the subsequent subqueries  

I.CreatedDate,

I.CreatedBy,

I.ModifiedDate,

I.ModifiedBy

from tItemMaster I 

Left Join tRefCategory		C	on C.CategoryID = I.CategoryID

Left Join tRefVendors		V	on V.VendorID = I.VendorID

Left Join tRefVendorFactory	VF	on VF.VendorFactoryiD = I.VendorFactoryID

Left Join tRefCountry		CT	on CT.CountryID = V.CountryID

Left Join tRefHTSCode		H	on H.HTSCodeID = I.HTSCodeID

Left Join tRefMaterial		M	on M.MaterialID = I.MaterialID

where I.ItemID = 2

Open in new window

0
 

Author Comment

by:dpmoney
Comment Utility
Yuching, your feedback is the closest to what I've been looking for.  However, when I cut and paste your query, I get this error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'From'.
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near 'T'.

Also, when I get ride of the comma after select *, I get this...

Msg 207, Level 16, State 1, Line 43
Invalid column name 'ReplacementCodeDate'.
Msg 4104, Level 16, State 1, Line 43
The multi-part identifier "MaxReplace.MaxReplacementDate" could not be bound.

Thanks to others for your input too.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Sorry, I forgot to close some parentesis in MAX function.
Should be something like this:
Select 

I.ItemID,

I.ItemNum,

I.LeadPPM,

I.VendorPartNum		as 'VendorPartNum1',

I.StdLeadTime		as 'StdLeadTime1',

I.PurchaseCost		as 'StandardCost',

I.PurchaseQty,

C.Category,

V.Vendor		as 'Vendor1',

VF.VendorFactory	as 'VendorFactory1',

CT.Country		as 'Country1',

H.DutyRate		as 'BaseHTSDuty1',

M.Material,		

  

-- Vendor1 HTS Duty Calculation

Case

	When	(V.CountryID=1) or (I.HTSAFLAG=1) then 0

	When	(I.HTSCodeID = null) then ''

	When	((V.CountryID = 9) and (M.MaterialID = 35) and ((SELECT ISNULL(Max(CH.ReplacementCost), '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate')) > 1.5) then 0.05

	When	(V.CountryID = 9) then 0

	Else	H.DutyRate

End As		CfmHTSDuty1,

 

--MaxReplacementCostDate

(SELECT ISNULL(Max(CH.ReplacementCostDate), '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID) MaxReplacementCostDate,

 

--XFactoryReplacementCost

--Problem 1 - Looks like SQL 2005 let me reference calculated field 'XFactoryReplacementCost' in Vendor 1 HTS Duty Calc above, so I tried referencing MaxReplacementCostDate in subquery below

--It didn't like me referencing it directly so I tried convertig to datetime.  

--It will work if I put in entire calculating query used to derive MaxReplacementCostDate, but I'd rather use calculated field alias.  

(SELECT ISNULL(Max(CH.ReplacementCost), '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate')) XFactoryReplacementCost, 

--Handling Fee

(SELECT ISNULL(Max(CH.ReplacementCost), '') FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate')) * .1 As HandlingFee,

--COO Duty

-- This is where it gets complicated because this calculation is based on the data derived from 4 calculated fields above

-- They are CfmHTSDuty1, MaxReplacementCostDate, XFactoryReplacementCost, HandlingFee

-- After this field, I have a few more that will use all calculated fields above.  You can see how this is getting to be very complex

-- with SQL 2005 not allowing me to directly reference the calculated fields in the subsequent subqueries  

I.CreatedDate,

I.CreatedBy,

I.ModifiedDate,

I.ModifiedBy

from tItemMaster I 

Left Join tRefCategory		C	on C.CategoryID = I.CategoryID

Left Join tRefVendors		V	on V.VendorID = I.VendorID

Left Join tRefVendorFactory	VF	on VF.VendorFactoryiD = I.VendorFactoryID

Left Join tRefCountry		CT	on CT.CountryID = V.CountryID

Left Join tRefHTSCode		H	on H.HTSCodeID = I.HTSCodeID

Left Join tRefMaterial		M	on M.MaterialID = I.MaterialID

where I.ItemID = 2

Open in new window

0
 

Author Closing Comment

by:dpmoney
Comment Utility
Thanks for your assistance.  It took some playing with your feedback but I got it to work.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now