Can I call a Column Expression Alias in another formula in SQL Server Management Studio query like I do In an Access Query?

Jarred Meyer
Jarred Meyer used Ask the Experts™
on
I have this iif statement in an Access query:
IIf([ldLaborType]="P" And [ReworkTran]=0 And [PlanTran]=0 And [ProgTran]=0,[ldLaborHrs],0) AS ProdHrs,

[ReworkTran], [PlanTran] and [ProgTran] are other iif Formulas in the query and although I am able to call them from another iif statement (above) in an Access query I can't seem to figure out if this is possible in a SQL Server Management Studio Query. (I have already created the three formulas; [ReworkTran], [PlanTran] and [ProgTran] in SSMS as CASE statements and they work fine. Here is the working SSMS query without the above mentioned iif statement.
SELECT     dbo.LaborDtl.JobNum, CASE dbo.LaborDtl.ResourceGrpID WHEN 'Eng' THEN 1 ELSE 0 END AS PlanTran, 
                      CASE dbo.LaborDtl.ResourceGrpID WHEN 'PROGWF' THEN 1 WHEN 'PROG3' THEN 1 WHEN 'Prog 5' THEN 1 WHEN 'Quality' THEN 1 WHEN 'PROGCont' THEN 1 ELSE
                       0 END AS ProgTran, CASE WHEN (dbo.LaborDtl.JobNum LIKE 'R%' AND dbo.JobOper.ProdStandard = 0) OR
                      dbo.LaborDtl.Rework = 1 THEN 1 ELSE 0 END AS ReworkTran
FROM         dbo.LaborDtl LEFT OUTER JOIN
                      dbo.JobOper ON dbo.LaborDtl.OprSeq = dbo.JobOper.OprSeq AND dbo.LaborDtl.AssemblySeq = dbo.JobOper.AssemblySeq AND 
                      dbo.LaborDtl.JobNum = dbo.JobOper.JobNum
WHERE     (dbo.LaborDtl.LaborHrs <> 0)

Open in new window


Thanks for the assistance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
The results of one column expression in the SELECT clause are not available to the other columns, so shy of repeating all of the logic for ProdHrs, the fastest way to pull this off is to throw the above statement into a subquery, then select column from that subquery + caluculate ProdHrs.  

Something like..
SELECT
   JobNum, PlanTran, ProgTran, ReworkTran,
   CASE WHEN ldLaborType='P' AND ReworkTran=0 And PlanTran=0 And ProgTran=0 THEN 0 END as ProdHrs
FROM (
   SELECT 
      ldLaborType,     
      dbo.LaborDtl.JobNum, 
      CASE dbo.LaborDtl.ResourceGrpID WHEN 'Eng' THEN 1 ELSE 0 END AS PlanTran,
      CASE dbo.LaborDtl.ResourceGrpID 
         WHEN 'PROGWF' THEN 1 
         WHEN 'PROG3' THEN 1 
         WHEN 'Prog 5' THEN 1
         WHEN 'Quality' THEN 1 
         WHEN 'PROGCont' THEN 1 
         ELSE 0 END AS ProgTran, 
      CASE WHEN (dbo.LaborDtl.JobNum LIKE 'R%' AND dbo.JobOper.ProdStandard = 0) OR dbo.LaborDtl.Rework = 1 THEN 1 ELSE 0 END AS ReworkTran
   FROM  dbo.LaborDtl 
	   LEFT OUTER JOIN dbo.JobOper ON dbo.LaborDtl.OprSeq = dbo.JobOper.OprSeq AND dbo.LaborDtl.AssemblySeq = dbo.JobOper.AssemblySeq AND 
   dbo.LaborDtl.JobNum = dbo.JobOper.JobNum
   WHERE     (dbo.LaborDtl.LaborHrs <> 0)) a

Open in new window

Jarred MeyerProduction Manager

Author

Commented:
Perfect! That did the trick
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade.  Good luck with your project.  -Jim
Jarred MeyerProduction Manager

Author

Commented:
No problem :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial