Link to home
Start Free TrialLog in
Avatar of Jeremy Campbell
Jeremy CampbellFlag for United States of America

asked on

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

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!
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jeremy Campbell

ASKER

Perfect! That did the trick
Thanks for the grade.  Good luck with your project.  -Jim
No problem :)