A little help with a conditional select error.

[nelsonm]

Hi all,

I have this select statement in my php script that giving a problems and i can see what the problem is.

The following works fine:

SELECT
wo.WOID,

(SELECT SUM(wi.UnitPrice * wi.QTY) FROM `wo-items-wi` AS wi WHERE wi.WOID = wo.WOID) AS SubTotal,

st.TaxRate

FROM `workorder-wo` AS wo

The following does not:

SELECT
wo.WOID,

CASE
WHEN wo.WSID = 1 THEN
(SELECT SUM(wi.UnitPrice * wi.QTY) FROM `wo-items-wi` AS wi WHERE wi.WOID = wo.WOID) AS SubTotal
WHEN wo.WSID = 2 THEN
(SELECT SUM(si.UnitPrice * si.QTY) FROM `ss-items-si` AS si WHERE si.WOID = wo.WOID) AS SubTotal
END,

st.TaxRate

FROM `workorder-wo` AS wo

mysql says there is a syntax error. It appears that the problem is the alias definition "AS SubTotal". Why does it work in the first example but the same statement does not in the second example when the select is in the case structure?

Any help would be appreciated.

[arms]

Try

SELECT
wo.WOID,

CASE
WHEN wo.WSID = 1 THEN
(SELECT SUM(wi.UnitPrice * wi.QTY) FROM `wo-items-wi` AS wi WHERE wi.WOID = wo.WOID)
WHEN wo.WSID = 2 THEN
(SELECT SUM(si.UnitPrice * si.QTY) FROM `ss-items-si` AS si WHERE si.WOID = wo.WOID)
END AS SubTotal,

st.TaxRate

FROM `workorder-wo` AS wo