RE: Case statement inside cached query
Matt, sorry I wasn't clear. I got a query of queries syntax error like this: Query Of Queries syntax error. Encountered sum ( case. Incorrect Select List, Nathan -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: Wednesday, November 07, 2012 4:27 PM To: cf-talk Subject: Re: Case statement inside cached query What does wouldn't work mean? Get results you didn't expect? If yes, what did you get and what did you expect? Get an exception? If yes, where's the stack trace? On Wed, Nov 7, 2012 at 5:22 PM, Nathan Chen nathan.c...@cu.edu wrote: All: Do cached queries allow SQL case statement at all or is there a way to get around it? I am trying to put this inside a cached query such as : Sum( case when A = 'B' then C else 0 end) as D ( C is numeric field). It wouldn't work. I am on CF 8 and Oracle 11g. Thanks. Nathan Chen ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353089 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Case statement inside cached query
Database-specific functions do not work in a Query of Queries. On Thu, Nov 8, 2012 at 10:07 AM, Nathan Chen nathan.c...@cu.edu wrote: Matt, sorry I wasn't clear. I got a query of queries syntax error like this: Query Of Queries syntax error. Encountered sum ( case. Incorrect Select List, Nathan -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: Wednesday, November 07, 2012 4:27 PM To: cf-talk Subject: Re: Case statement inside cached query What does wouldn't work mean? Get results you didn't expect? If yes, what did you get and what did you expect? Get an exception? If yes, where's the stack trace? On Wed, Nov 7, 2012 at 5:22 PM, Nathan Chen nathan.c...@cu.edu wrote: All: Do cached queries allow SQL case statement at all or is there a way to get around it? I am trying to put this inside a cached query such as : Sum( case when A = 'B' then C else 0 end) as D ( C is numeric field). It wouldn't work. I am on CF 8 and Oracle 11g. Thanks. Nathan Chen ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353090 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Case statement inside cached query
I don't think the CASE function is supported in Query-of-Query statements. -Carl V. On 11/8/2012 8:07 AM, Nathan Chen wrote: Matt, sorry I wasn't clear. I got a query of queries syntax error like this: Query Of Queries syntax error. Encountered sum ( case. Incorrect Select List, Nathan -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: Wednesday, November 07, 2012 4:27 PM To: cf-talk Subject: Re: Case statement inside cached query What does wouldn't work mean? Get results you didn't expect? If yes, what did you get and what did you expect? Get an exception? If yes, where's the stack trace? On Wed, Nov 7, 2012 at 5:22 PM, Nathan Chen nathan.c...@cu.edu wrote: All: Do cached queries allow SQL case statement at all or is there a way to get around it? I am trying to put this inside a cached query such as : Sum( case when A = 'B' then C else 0 end) as D ( C is numeric field). It wouldn't work. I am on CF 8 and Oracle 11g. Thanks. Nathan Chen ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353091 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Case statement inside cached query
you can only use very basic syntax in QofQ as it is not really SQL or running against the database server. It is just CFML extracting a subset of data from an existing query. If you want to do complex searches then you will need to do it by looping over the query and comparing each row. Or simply run your query directly against the database instead of using QofQ On Thu, Nov 8, 2012 at 4:07 PM, Nathan Chen nathan.c...@cu.edu wrote: Matt, sorry I wasn't clear. I got a query of queries syntax error like this: Query Of Queries syntax error. Encountered sum ( case. Incorrect Select List, Nathan -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: Wednesday, November 07, 2012 4:27 PM To: cf-talk Subject: Re: Case statement inside cached query What does wouldn't work mean? Get results you didn't expect? If yes, what did you get and what did you expect? Get an exception? If yes, where's the stack trace? On Wed, Nov 7, 2012 at 5:22 PM, Nathan Chen nathan.c...@cu.edu wrote: All: Do cached queries allow SQL case statement at all or is there a way to get around it? I am trying to put this inside a cached query such as : Sum( case when A = 'B' then C else 0 end) as D ( C is numeric field). It wouldn't work. I am on CF 8 and Oracle 11g. Thanks. Nathan Chen ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353092 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Case statement inside cached query
All: Do cached queries allow SQL case statement at all or is there a way to get around it? I am trying to put this inside a cached query such as : Sum( case when A = 'B' then C else 0 end) as D ( C is numeric field). It wouldn't work. I am on CF 8 and Oracle 11g. Thanks. Nathan Chen ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353084 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Case statement inside cached query
What does wouldn't work mean? Get results you didn't expect? If yes, what did you get and what did you expect? Get an exception? If yes, where's the stack trace? On Wed, Nov 7, 2012 at 5:22 PM, Nathan Chen nathan.c...@cu.edu wrote: All: Do cached queries allow SQL case statement at all or is there a way to get around it? I am trying to put this inside a cached query such as : Sum( case when A = 'B' then C else 0 end) as D ( C is numeric field). It wouldn't work. I am on CF 8 and Oracle 11g. Thanks. Nathan Chen ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353085 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
