Welcome to WebmasterWorld Guest from 54.162.139.217

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)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 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.
11:26 am on Apr 27, 2012 (gmt 0)

New User

5+ Year Member

joined:Feb 22, 2011
posts:22
votes: 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
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members