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.

"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

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

"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