homepage Welcome to WebmasterWorld Guest from 54.166.116.36
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
A little help with a conditional select error.
nelsonm

5+ Year Member



 
Msg#: 4445423 posted 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#: 4445423 posted 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved