| A little help with a conditional select error.
|
nelsonm
msg:4445425 | 4:41 pm on Apr 25, 2012 (gmt 0) |
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
msg:4446483 | 11:26 am on Apr 27, 2012 (gmt 0) |
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
|
|
|
