Welcome to WebmasterWorld Guest from 23.22.79.235

Forum Moderators: open

Message Too Old, No Replies

A little help with a conditional select error.

   
4:41 pm on Apr 25, 2012 (gmt 0)

5+ Year Member



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.
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