Re: Is this illegal in query of queries?
Thanks, everyone. I just re-write the statement without the use of subquery. The statement is actually a lot simpler than with the subquery. (SELECT USERID, HOMEADDR AS ADDRESS FROM QURYUSERS WHERE NOT HOMEADDR IS NULL) UNION (SELECT USERID, BUSADDR AS ADDRESS FROM QURYUSERS WHERE HOMEADDR IS NULL AND NOT BUSADDR IS NULL) Johnny >They don't support subqueries. They're really very limited in terms of >supporting SQL syntax. I don't think they were really ever intended >for anything more than relatively simple operations like, joining 2 >queries from different servers (an oracle erp system + a local sql >server database) or fetching aggregate values from an existing query. >I use them occasionally to resort or refilter a query. I've got one >bit of code that uses an XSL transformation to get the id values for >all the records I want to isolate and return in a particular query, >but the actual QoQ is very simple, it's just "select * from query >where keycolumn in (#XslTransformedListOfIDs#)" > >> I don't see anything about support for subqueries, I would >> be surprised if they were supported. Even if they were, I >> would seriously re-think using a QofQ instead of operating >> against the database. If you read the notes on QofQ, >> you'll see that performance really lags on complex >> operations. > > > >s. isaac dealey 434.293.6201 >new epoch : isn't it time for a change? > >add features without fixtures with >the onTap open source framework > >http://www.fusiontap.com >http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235254 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
Re: Is this illegal in query of queries?
They don't support subqueries. They're really very limited in terms of supporting SQL syntax. I don't think they were really ever intended for anything more than relatively simple operations like, joining 2 queries from different servers (an oracle erp system + a local sql server database) or fetching aggregate values from an existing query. I use them occasionally to resort or refilter a query. I've got one bit of code that uses an XSL transformation to get the id values for all the records I want to isolate and return in a particular query, but the actual QoQ is very simple, it's just "select * from query where keycolumn in (#XslTransformedListOfIDs#)" > I don't see anything about support for subqueries, I would > be surprised if they were supported. Even if they were, I > would seriously re-think using a QofQ instead of operating > against the database. If you read the notes on QofQ, > you'll see that performance really lags on complex > operations. >> Does query of queries allow sub queries? I get this >> error: >> >> Query Of Queries syntax error. >> Encountered "USERID NOT IN ( SELECT. Incorrect >> conditional expression, >> Expected one of [like|null|between|in|comparison] >> condition, >> >> When I tried to run this statement: >> >> (SELECT USERID, HOMEADDR AS ADDRESS FROM QURYUSERS WHERE >> NOT HOMEADDR >> IS NULL) >> UNION >> (SELECT USERID, BUSADDR AS ADDRESS FROM QURYUSERS WHERE >> USERID NOT IN >> ( >> SELECT USERID FROM QRYUSERS WHERE NOT HOMEADDR IS NULL) >> AND NOT BUSADDR IS NULL) >> >> This statement runs perfectly fine in SQL Query Analyzer. >> >> I am using CFMX 7 and MS SQL Server 2000. Is something >> wrong with my >> statement? Is there a better to write this statement? >> > Johnny s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235218 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Is this illegal in query of queries?
Subqueries are not supported in QoQ. On 3/12/06, Robert Munn <[EMAIL PROTECTED]> wrote: > I don't see anything about support for subqueries, I would be surprised if > they were supported. Even if they were, I would seriously re-think using a > QofQ instead of operating against the database. If you read the notes on > QofQ, you'll see that performance really lags on complex operations. -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235126 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Is this illegal in query of queries?
I don't see anything about support for subqueries, I would be surprised if they were supported. Even if they were, I would seriously re-think using a QofQ instead of operating against the database. If you read the notes on QofQ, you'll see that performance really lags on complex operations. > Does query of queries allow sub queries? I get this error: > > Query Of Queries syntax error. > Encountered "USERID NOT IN ( SELECT. Incorrect conditional expression, > Expected one of [like|null|between|in|comparison] condition, > > When I tried to run this statement: > > (SELECT USERID, HOMEADDR AS ADDRESS FROM QURYUSERS WHERE NOT HOMEADDR > IS NULL) > UNION > (SELECT USERID, BUSADDR AS ADDRESS FROM QURYUSERS WHERE USERID NOT IN > ( > SELECT USERID FROM QRYUSERS WHERE NOT HOMEADDR IS NULL) > AND NOT BUSADDR IS NULL) > > This statement runs perfectly fine in SQL Query Analyzer. > > I am using CFMX 7 and MS SQL Server 2000. Is something wrong with my > statement? Is there a better to write this statement? > Johnny ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235125 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Is this illegal in query of queries?
Does query of queries allow sub queries? I get this error: Query Of Queries syntax error. Encountered "USERID NOT IN ( SELECT. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, When I tried to run this statement: (SELECT USERID, HOMEADDR AS ADDRESS FROM QURYUSERS WHERE NOT HOMEADDR IS NULL) UNION (SELECT USERID, BUSADDR AS ADDRESS FROM QURYUSERS WHERE USERID NOT IN ( SELECT USERID FROM QRYUSERS WHERE NOT HOMEADDR IS NULL) AND NOT BUSADDR IS NULL) This statement runs perfectly fine in SQL Query Analyzer. I am using CFMX 7 and MS SQL Server 2000. Is something wrong with my statement? Is there a better to write this statement? Johnny ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235123 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
