karephre
asked on
add a case statement to a selet nest statment
hello
I have to working statements that i want to combine together. one is a case statement, the other is is a nest select statement.
Now i already have a left outer join in the select statement. when i go to add the workorder table to a new left outer join, so that i can add the case statment, i get and error.
please help :(
Thanks
I have to working statements that i want to combine together. one is a case statement, the other is is a nest select statement.
Now i already have a left outer join in the select statement. when i go to add the workorder table to a new left outer join, so that i can add the case statment, i get and error.
please help :(
Thanks
case statement
SELECT ORGWON,
CASE CLOSEDFLAG
WHEN 'Y' THEN 'CLOSED'
WHEN 'N' THEN 'OPEN'
ELSE 'NA'
END AS WO_STATUS
FROM WORKORDER
select statement
SELECT T.ORGWON,NVL(B.TOTAL_PARTS,0)AS "TOTAL_PARTS",T.TOTAL_HOURS,(NVL(B.TOTAL_PARTS,0)+T.TOTAL_HOURS) AS "TOTAL_ALL"
FROM
(
SELECT ORGWON, ROUND((TOTAL_HR)*(AVGCONTRATE),2) AS "TOTAL_HOURS"
FROM
(
select ORGWON,ROUND(SUM(reghours),2)AS "TOTAL_HR"
from logmaint.taskperson
where laborcde='01'
and employeetype='K'
GROUP BY ORGWON
)
, LOGMAINT.unitparameter
) T
LEFT OUTER JOIN
(
SELECT ORGWON,SUM(PARTS_TOTAL)AS "TOTAL_PARTS"
FROM (
SELECT TASKPERSON.ORGWON, QTYISSUE, ROUND((QTYISSUE)*(PRICE/MEASQ),2)AS "PARTS_TOTAL"
FROM LOGMAINT.WRKORDPART,LOGMAINT.CATALOG,LOGMAINT.TASKPERSON
WHERE CATALOG.NIINPNO=WRKORDPART.niinpno
AND TASKPERSON.ORGWON=WRKORDPART.ORGWON
AND QTYISSUE > '0'
and PARTINSTALLED='Y'
GROUP BY TASKPERSON.ORGWON,QTYISSUE,PRICE,MEASQ)
GROUP BY ORGWON
) B
ON B.ORGWON= T.ORGWON
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
each on statement must be with its join.
ASKER
now my statement is good to go.
new query below
Thanks
Open in new window