Jeremy Campbell
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.
Thanks for the assistance!
IIf([ldLaborType]="P" And [ReworkTran]=0 And [PlanTran]=0 And [ProgTran]=0,[ldLaborHrs],
[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)
Thanks for the assistance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the grade. Good luck with your project. -Jim
ASKER
No problem :)
ASKER