Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
Subquery question |
Mon, Aug 13 2007 9:50 AM | Permanent Link |
Mark Shapiro | In DBISAM 4, I have a long and involved query to which I want to add a subquery in the
following form: SELECT <many clauses> FROM Hotline <many clauses> AND hot_id in (SELECT d_client_id, COUNT(*) FROM MultiChoiceHotlineData WHERE d_agy_id = 4 AND d_MultiChoiceCat_id = 25 GROUP BY d_client_id HAVING COUNT(*) >= 4) Although the statement inside parenthesis works fine by itself, it is illegal as a subquery because it selects two columns. I'm just wondering if there is any way I can accomplish the same thing within a subquery structure. |
Mon, Aug 13 2007 10:33 AM | Permanent Link |
"Robert" | "Mark Shapiro" <infocus@swbell.net> wrote in message news:1C3C1257-FE9B-4689-B6F7-B7FDEF627549@news.elevatesoft.com... > In DBISAM 4, I have a long and involved query to which I want to add a > subquery in the > following form: > > SELECT <many clauses> FROM Hotline > <many clauses> > AND hot_id in > (SELECT d_client_id, COUNT(*) FROM MultiChoiceHotlineData > WHERE d_agy_id = 4 > AND d_MultiChoiceCat_id = 25 > GROUP BY d_client_id HAVING COUNT(*) >= 4) > > Although the statement inside parenthesis works fine by itself, it is > illegal as a > subquery because it selects two columns. I'm just wondering if there is > any way I can > accomplish the same thing within a subquery structure. > Not in DBISAM. I think the only solution is to Select the subquery by iself into a memory table, then JOIN the memory table to your main query on d_client_id = hot_id. If the subquery generates a large result set, you should consider adding an index on d_client_id before you execute the main query. Robert |
Mon, Aug 13 2007 10:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mark
You should be able to do something with a script. Something along the lines of SELECT d_client_id, COUNT(*) into "memory\temp" FROM MultiChoiceHotlineData WHERE d_agy_id = 4 AND d_MultiChoiceCat_id = 25 GROUP BY d_client_id HAVING COUNT(*) >= 4; SELECT <many clauses> FROM Hotline <many clauses> AND hot_id in (select d_client_id from "memory\temp"); and remembering to get rid of the memory table later on Roy Lambert |
Mon, Aug 13 2007 11:00 AM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:B2A5BC56-2031-472C-B85C-CFFE9A62380B@news.elevatesoft.com... > AND hot_id in (select d_client_id from "memory\temp"); > If you don't have a WHERE clause in the subquery, you should use a JOIN. Much simpler and faster. Robert |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |