 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / October 2003Executing sql statement with SUM and CASE.
I have a sql statement as below: SELECT SUM(CASE WHEN DUNAPPL>=5 THEN 1 ELSE 0 END) as TEST from xyz group by a,b,c When I run this sql statement in TOAD or SQLPLUS I get all the data and it runs fine. But when I try to run the sql thru an VB 6.0 application it gives error. --- right parenthesis missing ( ORA - 00907) ---------------------------------------------------------------------------- ------------------------------------- Set adoConnection = New ADODB.Connection Set adoRecordset = New ADODB.Recordset adoConnection.Open connectionString sqlString = "SELECT SUM(CASE WHEN DUNAPPL>=5 THEN 1 ELSE 0 END) as TEST from xyz group by a,b,c " adoRecordset .Open sqlString, adoConnection, adOpenForwardOnly, adLockReadOnly, adCmdText ---------------------------------------------------------------------------- ------------------------------------- When I remove the SUM function and the parenthesis it runs fine again. What mistaking I am making ? Thank you for help Kiran Math ¤ I have a sql statement as below: ¤ ¤ SELECT SUM(CASE WHEN DUNAPPL>=5 THEN 1 ELSE 0 END) as TEST from xyz group by ¤ a,b,c ¤ ¤ When I run this sql statement in TOAD or SQLPLUS I get all the data and it ¤ runs fine. ¤ ¤ But when I try to run the sql thru an VB 6.0 application it gives error. ¤ --- right parenthesis missing ( ORA - 00907) ¤ ¤ ---------------------------------------------------------------------------- ¤ ------------------------------------- ¤ Set adoConnection = New ADODB.Connection ¤ Set adoRecordset = New ADODB.Recordset ¤ adoConnection.Open connectionString ¤ sqlString = "SELECT SUM(CASE WHEN DUNAPPL>=5 THEN 1 ELSE 0 END) as ¤ TEST from xyz group by a,b,c " ¤ adoRecordset .Open sqlString, adoConnection, adOpenForwardOnly, ¤ adLockReadOnly, adCmdText ¤ ---------------------------------------------------------------------------- ¤ ------------------------------------- ¤ ¤ ¤ When I remove the SUM function and the parenthesis it runs fine again. ¤ ¤ ¤ What mistaking I am making ? I can't reproduce this problem. What data provider are you using? Does it work if you remove the "Group By" clause? Paul ~~~ pclement@ameritech.net Microsoft MVP (Visual Basic) Paul Clement I am using the following: Database : Oracle 9i Provider : OraOLEDB.Oracle When I use : Select Case ..... I do not get any errors But when I go select SUM(Case ... I get error - right parenthsis missing. The query runs perfectly well in TOAD and SQLPLUS. I need Group by as I have got a aggregate function. I changed my query to select SUM(decode ... it works fine again. Thank you for help. Kiran Math . ---------------------------------------------------------------------------- --- Set adoConnection = New ADODB.Connection Set adoRecordset = New ADODB.Recordset adoConnection.Open connectionString sqlString = "SELECT SUM(CASE WHEN DUNAPPL>=5 THEN 1 ELSE 0 END) as TEST from xyz group by a,b,c " adoRecordset .Open sqlString, adoConnection, adOpenForwardOnly, adLockReadOnly, adCmdText ---------------------------------------------------------------------------- -------- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.