Solved

Help Referencing SQL Calculated Field (Alias) in Subqueries

Posted on 2007-12-06
10
2,671 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 47

Expert Comment

by:Vitor Montalvão
ID: 20421923
In that example which are the fields that you want reference?
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 20421999
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:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 20422107
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:dpmoney
ID: 20422552
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
ID: 20424929
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
 

Author Comment

by:dpmoney
ID: 20425137
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 47

Expert Comment

by:Vitor Montalvão
ID: 20426735
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
ID: 20427152
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 47

Expert Comment

by:Vitor Montalvão
ID: 20427589
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
ID: 31413188
Thanks for your assistance.  It took some playing with your feedback but I got it to work.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 66
Help Required 2 30
T-SQL Default value in Select? 5 24
SQL Syntax: How to force case sensitive query? 2 23
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 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