I take your point, but it might not be CF, the drivers may be at fault. To be honest, I've never seen unioned queries formatted in such a way, good to know that it doesn't work though.
Ade -----Original Message----- From: Steve Brownlee [mailto:[EMAIL PROTECTED] Sent: 04 March 2005 19:01 To: CF-Talk Subject: CF7 producing undefined query? This one's driving me bonkers. I found a "solution" but why on earth this is happening is beyond me. <cfquery name="query_paperforspecs" datasource="#Request.DB#"> (SELECT pr.revisionid FROM projects p, projectrevisions pr WHERE pr.revisioncreationtypecode in ('E','D') and pr.revisionstatuscode in ('V','A') and p.projectid = pr.projectid and p.siteid = pr.siteid and p.projectid = #pid# and p.siteid = #request.siteid# GROUP BY pr.revisionid HAVING max(mod(pr.revisionsettings,2)) = 1) UNION (SELECT DISTINCT pr.revisionid FROM projectorder po, projectrevisions pr WHERE mod(pr.revisionsettings,2) = 1 and po.projectid = pr.projectid and po.siteid = pr.siteid and po.orderstatuscode = 'A' and po.projectid = #pid# and po.siteid = #request.siteid#) </cfquery> After running this query in CF7, it doesn't even recognize that I ran a query. Attempts to reference "query_paperforspecs" or any of its properties results in "Variable QUERY_PAPERFORSPECS is undefined" and "Element RECORDCOUNT is undefined in QUERY_PAPERFORSPECS.", respectively. To solve this problem, all I needed to do is remove the parenthesis surrounding the two SELECT statements and it ran fine. Either one works find when I run it directly in the query analyzer. Why would CF care about parenthesis surrounding a SELECT statement? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.6.0 - Release Date: 02/03/2005 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197518 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
