using recordset(subquery) as target of FROM clause 
Author Message
 using recordset(subquery) as target of FROM clause

DB Gurus,
  I need to run a query against a recordset, and unfortunately, "filter"
does not suffice...

we have several nested queries in MS Access where the resulting "Select"
statement looks similar to:

Select * from (Select Sum(Field1) as Bal1, Sum(Field2) as Bal2 from (select
* from table))...

I know this looks like a simple case, I've over-simplified the select clause
to show that I'm needing the subquery to be the target of the FROM
clause...,

We're trying desparately to provide a db-independent ADO solution for some
reports we already have in MS Access, and this is where we currently are
stuck...

It seems to me to be a simple-enough need to want to run a select query
against a populated recordset, but I've found no info on the subject...

Please, any help is needed! :)

MTIA
wardo



Wed, 31 Mar 2004 05:33:30 GMT
 using recordset(subquery) as target of FROM clause


Quote:
> DB Gurus,
>   I need to run a query against a recordset, and unfortunately, "filter"
> does not suffice...

> we have several nested queries in MS Access where the resulting "Select"
> statement looks similar to:

> Select * from (Select Sum(Field1) as Bal1, Sum(Field2) as Bal2 from
(select
> * from table))...

> I know this looks like a simple case, I've over-simplified the select
clause
> to show that I'm needing the subquery to be the target of the FROM
> clause...,

> We're trying desparately to provide a db-independent ADO solution for some
> reports we already have in MS Access, and this is where we currently are
> stuck...

> It seems to me to be a simple-enough need to want to run a select query
> against a populated recordset, but I've found no info on the subject...

It's not possible to run a select query against a recordset. You will have
to consider an alternative approach. You could consider select_ing into a
table, and then select_ing from that table, thereby extending the concept of
using subqueries to database platforms &/or providers that do not support
the use of subqueries. The pitfall to such an approach is that you may have
to manually "manage" the temporary (derived) tables - for example, sql
server supports global and local temporary tables, whereas Access does not
offer the same functionality. ('Tho in Access you can use a QueryDef object
in a FROM clause.)

I would have thought that "subqueries" would be supported to some extent by
most db engines -- it must be part of an ANSI standard sql, or something.
Apologies if I am barking up completely the wrong tree here....;

Regds
Robert.

- Show quoted text -

Quote:

> Please, any help is needed! :)

> MTIA
> wardo



Wed, 31 Mar 2004 08:55:14 GMT
 using recordset(subquery) as target of FROM clause
Robert,
  Thanks for the reply!
that's what I was afraid of...  unfortunately I'm not sure a temp table
would work either due to concurrency issues in a multi-user environment...
I'll have to keep looking, though.  thanks again!
wardo


Quote:



> > DB Gurus,
> >   I need to run a query against a recordset, and unfortunately, "filter"
> > does not suffice...

> > we have several nested queries in MS Access where the resulting "Select"
> > statement looks similar to:

> > Select * from (Select Sum(Field1) as Bal1, Sum(Field2) as Bal2 from
> (select
> > * from table))...

> > I know this looks like a simple case, I've over-simplified the select
> clause
> > to show that I'm needing the subquery to be the target of the FROM
> > clause...,

> > We're trying desparately to provide a db-independent ADO solution for
some
> > reports we already have in MS Access, and this is where we currently are
> > stuck...

> > It seems to me to be a simple-enough need to want to run a select query
> > against a populated recordset, but I've found no info on the subject...

> It's not possible to run a select query against a recordset. You will have
> to consider an alternative approach. You could consider select_ing into a
> table, and then select_ing from that table, thereby extending the concept
of
> using subqueries to database platforms &/or providers that do not support
> the use of subqueries. The pitfall to such an approach is that you may
have
> to manually "manage" the temporary (derived) tables - for example, sql
> server supports global and local temporary tables, whereas Access does not
> offer the same functionality. ('Tho in Access you can use a QueryDef
object
> in a FROM clause.)

> I would have thought that "subqueries" would be supported to some extent
by
> most db engines -- it must be part of an ANSI standard sql, or something.
> Apologies if I am barking up completely the wrong tree here....;

> Regds
> Robert.

> > Please, any help is needed! :)

> > MTIA
> > wardo



Wed, 31 Mar 2004 11:08:26 GMT
 using recordset(subquery) as target of FROM clause
On Fri, 12 Oct 2001 17:33:30 -0400, "wardo"

Quote:

>DB Gurus,
>  I need to run a query against a recordset, and unfortunately, "filter"
>does not suffice...

>we have several nested queries in MS Access where the resulting "Select"
>statement looks similar to:

>Select * from (Select Sum(Field1) as Bal1, Sum(Field2) as Bal2 from (select
>* from table))...

>I know this looks like a simple case, I've over-simplified the select clause
>to show that I'm needing the subquery to be the target of the FROM
>clause...,

>We're trying desparately to provide a db-independent ADO solution for some
>reports we already have in MS Access, and this is where we currently are
>stuck...

>It seems to me to be a simple-enough need to want to run a select query
>against a populated recordset, but I've found no info on the subject...

The following is an undocumented feature in A97 (I'm not sure about
A2K - it may be documented in A2K), so use at your own risk:

It is possible to use a subquery in an A97 (and above) query's FROM
clause, provided the following conditions are met:

1. The subquery is surrounded by brackets [] or back ticks (`)

2. The closing bracket or back tick is immediately followed by a
period

    Select * From [Select * From table]. As XX
    Select * From `Select * From table`. As XX

3. The subquery MUST be aliased. This will not work:

    Select * From [Select * From table].

    You must use either this:

    Select * From [Select * From table]. As XX

    or this (the As keyword is optional):

    Select * From [Select * From table]. XX

4. In A97, you can have more than one virtual table, provided they are
not nested. For example, this will not work:

Select * From [Select * From [Select * From table]. As XX]. As YY

However, it is OK to do this:

Select XX.flda, YY.fldm
From [Select flda From tbl1]. As XX
    Inner Join [Select fldm From tbl2]. As YY
    On XX.fldx = YY.fldy

Or this:

Select XX.flda, YY.fldm
From `Select flda From tbl1`. As XX
    Inner Join [Select fldm From tbl2]. As YY
    On XX.fldx = YY.fldy

This limitation (supposedly) does not apply to A2K.

5. It won't work if you have any object names that require brackets,
i.e., field or table names containing spaces or other irregular
characters. The only brackets allowed in this type of query are the
brackets around the subquery. For example, this will not work:

Select * From [Select [this field], [that field]  From [some table]].
XX

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.



Wed, 31 Mar 2004 20:13:06 GMT
 using recordset(subquery) as target of FROM clause
Bob,
   Thanks for the reply!  Unfortunately we're already running our reports in
A2K and need a db-independent ADO solution so that we don't NEED A2K.
That's the kicker... :)  Thanks for the assist, though! :):)
wardo


Quote:
> On Fri, 12 Oct 2001 17:33:30 -0400, "wardo"

> >DB Gurus,
> >  I need to run a query against a recordset, and unfortunately, "filter"
> >does not suffice...

> >we have several nested queries in MS Access where the resulting "Select"
> >statement looks similar to:

> >Select * from (Select Sum(Field1) as Bal1, Sum(Field2) as Bal2 from
(select
> >* from table))...

> >I know this looks like a simple case, I've over-simplified the select
clause
> >to show that I'm needing the subquery to be the target of the FROM
> >clause...,

> >We're trying desparately to provide a db-independent ADO solution for
some
> >reports we already have in MS Access, and this is where we currently are
> >stuck...

> >It seems to me to be a simple-enough need to want to run a select query
> >against a populated recordset, but I've found no info on the subject...

> The following is an undocumented feature in A97 (I'm not sure about
> A2K - it may be documented in A2K), so use at your own risk:

> It is possible to use a subquery in an A97 (and above) query's FROM
> clause, provided the following conditions are met:

> 1. The subquery is surrounded by brackets [] or back ticks (`)

> 2. The closing bracket or back tick is immediately followed by a
> period

>     Select * From [Select * From table]. As XX
>     Select * From `Select * From table`. As XX

> 3. The subquery MUST be aliased. This will not work:

>     Select * From [Select * From table].

>     You must use either this:

>     Select * From [Select * From table]. As XX

>     or this (the As keyword is optional):

>     Select * From [Select * From table]. XX

> 4. In A97, you can have more than one virtual table, provided they are
> not nested. For example, this will not work:

> Select * From [Select * From [Select * From table]. As XX]. As YY

> However, it is OK to do this:

> Select XX.flda, YY.fldm
> From [Select flda From tbl1]. As XX
>     Inner Join [Select fldm From tbl2]. As YY
>     On XX.fldx = YY.fldy

> Or this:

> Select XX.flda, YY.fldm
> From `Select flda From tbl1`. As XX
>     Inner Join [Select fldm From tbl2]. As YY
>     On XX.fldx = YY.fldy

> This limitation (supposedly) does not apply to A2K.

> 5. It won't work if you have any object names that require brackets,
> i.e., field or table names containing spaces or other irregular
> characters. The only brackets allowed in this type of query are the
> brackets around the subquery. For example, this will not work:

> Select * From [Select [this field], [that field]  From [some table]].
> XX

> HTH,
> Bob Barrows
> Please reply to the newsgroup. My reply-to address is my "spam trap" and I

don't check it very often.


Thu, 01 Apr 2004 03:31:32 GMT
 using recordset(subquery) as target of FROM clause
On Sat, 13 Oct 2001 15:31:32 -0400, "wardo"

Quote:

>Bob,
>   Thanks for the reply!  Unfortunately we're already running our reports in
>A2K and need a db-independent ADO solution so that we don't NEED A2K.
>That's the kicker... :)  Thanks for the assist, though! :):)
>wardo

I don't understand your objection. Most SQL languages allow subqueries
in the FROM clause. Your question suggested that this is what you
wanted to do. What's the problem?

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.



Thu, 01 Apr 2004 07:35:19 GMT
 using recordset(subquery) as target of FROM clause
Bob,
   again, thanks for the reply....
Yes, this IS what we are trying to do, but we can't do it in Access 2000
anymore, we need to port the reports to a larger DB, like MSSQL or Oracle.
We are generating the recordsets in VB using ADO(thus the requirement for
db-independence).  We are trying to reproduce the resulting recordsets from
the nested Access 2000 queries.  They work fine when connecting to the A2K
DB, but error out when connecting to the SQL DB.  At present here is what
I've tried in simple nested queries in SQL2K and found did not work:

select * from [select * from table1]. as XX
result=> "Incorrect syntax near the keyword 'as' "

select * from `select * from table1`. as XX
result=> "Incorrect syntax near '`' "
"Incorrect syntax near '`' "

If I'm still missing something, I'd love to know... :)
Thanks!
wardo


Quote:
> On Sat, 13 Oct 2001 15:31:32 -0400, "wardo"

> >Bob,
> >   Thanks for the reply!  Unfortunately we're already running our reports
in
> >A2K and need a db-independent ADO solution so that we don't NEED A2K.
> >That's the kicker... :)  Thanks for the assist, though! :):)
> >wardo

> I don't understand your objection. Most SQL languages allow subqueries
> in the FROM clause. Your question suggested that this is what you
> wanted to do. What's the problem?

> HTH,
> Bob Barrows
> Please reply to the newsgroup. My reply-to address is my "spam trap" and I

don't check it very often.


Thu, 01 Apr 2004 10:02:46 GMT
 using recordset(subquery) as target of FROM clause
wardo,

select * from (select * from table1) X

-oj


Quote:
> Bob,
>    again, thanks for the reply....
> Yes, this IS what we are trying to do, but we can't do it in Access 2000
> anymore, we need to port the reports to a larger DB, like MSSQL or Oracle.
> We are generating the recordsets in VB using ADO(thus the requirement for
> db-independence).  We are trying to reproduce the resulting recordsets
from
> the nested Access 2000 queries.  They work fine when connecting to the A2K
> DB, but error out when connecting to the SQL DB.  At present here is what
> I've tried in simple nested queries in SQL2K and found did not work:

> select * from [select * from table1]. as XX
> result=> "Incorrect syntax near the keyword 'as' "

> select * from `select * from table1`. as XX
> result=> "Incorrect syntax near '`' "
> "Incorrect syntax near '`' "

> If I'm still missing something, I'd love to know... :)
> Thanks!
> wardo



> > On Sat, 13 Oct 2001 15:31:32 -0400, "wardo"

> > >Bob,
> > >   Thanks for the reply!  Unfortunately we're already running our
reports
> in
> > >A2K and need a db-independent ADO solution so that we don't NEED A2K.
> > >That's the kicker... :)  Thanks for the assist, though! :):)
> > >wardo

> > I don't understand your objection. Most SQL languages allow subqueries
> > in the FROM clause. Your question suggested that this is what you
> > wanted to do. What's the problem?

> > HTH,
> > Bob Barrows
> > Please reply to the newsgroup. My reply-to address is my "spam trap" and
I
> don't check it very often.



Thu, 01 Apr 2004 11:23:14 GMT
 using recordset(subquery) as target of FROM clause
On Sat, 13 Oct 2001 22:02:46 -0400, "wardo"

Quote:

>Bob,
>   again, thanks for the reply....
>Yes, this IS what we are trying to do, but we can't do it in Access 2000
>anymore, we need to port the reports to a larger DB, like MSSQL or Oracle.
>We are generating the recordsets in VB using ADO(thus the requirement for
>db-independence).  We are trying to reproduce the resulting recordsets from
>the nested Access 2000 queries.  They work fine when connecting to the A2K
>DB, but error out when connecting to the SQL DB.  At present here is what
>I've tried in simple nested queries in SQL2K and found did not work:

>select * from [select * from table1]. as XX
>result=> "Incorrect syntax near the keyword 'as' "

Sorry. My FAQ does not make it clear: the [] syntax seems to only
apply to A97. In A2K, you're supposed to be able to just use normal
parentheses (just as in SQL Server). I will revise my FAQ response.
I'm at a handicap because I haven't upgraded to A2K, so most of my
answers apply to A97 (which is why I rarely get involved with
A2K-specific questions).

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.



Thu, 01 Apr 2004 21:26:53 GMT
 using recordset(subquery) as target of FROM clause
oj,
   THANK YOU THANK YOU THANK YOU!!!!   Where can I find some more specifics
on how to do this....  nothing I've found mentions this specific syntax!!!
Thanks a MILLION!

Please, one other question:  If I use a column alias in my query Access
allows me to refer to that alias in the same query but SQL does not...
example

This is allowed in Access 2000 but NOT in SQL2K
Select Sum(field1) as Total from table1 where Total > 100

We're using a complicated calculation in the query and then assigning it to
one of several fields on the fly, without being able to reference the alias,
we'd have to recalc the thing 5-6 times for each record in the recordset
(20,000 records)
wardo


Quote:
> wardo,

> select * from (select * from table1) X

> -oj



> > Bob,
> >    again, thanks for the reply....
> > Yes, this IS what we are trying to do, but we can't do it in Access 2000
> > anymore, we need to port the reports to a larger DB, like MSSQL or
Oracle.
> > We are generating the recordsets in VB using ADO(thus the requirement
for
> > db-independence).  We are trying to reproduce the resulting recordsets
> from
> > the nested Access 2000 queries.  They work fine when connecting to the
A2K
> > DB, but error out when connecting to the SQL DB.  At present here is
what
> > I've tried in simple nested queries in SQL2K and found did not work:

> > select * from [select * from table1]. as XX
> > result=> "Incorrect syntax near the keyword 'as' "

> > select * from `select * from table1`. as XX
> > result=> "Incorrect syntax near '`' "
> > "Incorrect syntax near '`' "

> > If I'm still missing something, I'd love to know... :)
> > Thanks!
> > wardo



> > > On Sat, 13 Oct 2001 15:31:32 -0400, "wardo"

> > > >Bob,
> > > >   Thanks for the reply!  Unfortunately we're already running our
> reports
> > in
> > > >A2K and need a db-independent ADO solution so that we don't NEED A2K.
> > > >That's the kicker... :)  Thanks for the assist, though! :):)
> > > >wardo

> > > I don't understand your objection. Most SQL languages allow subqueries
> > > in the FROM clause. Your question suggested that this is what you
> > > wanted to do. What's the problem?

> > > HTH,
> > > Bob Barrows
> > > Please reply to the newsgroup. My reply-to address is my "spam trap"
and
> I
> > don't check it very often.



Fri, 02 Apr 2004 00:45:32 GMT
 using recordset(subquery) as target of FROM clause
wardo,

you can refer to column's alias only in "order by" clause. here is an
example using the order details table in the northwind database. you should
be able to apply to your situation...

select orderid, sum(unitprice) as price
from [northwind]..[order details]
group by orderid
having sum(unitprice)>100

-oj


Quote:
> oj,
>    THANK YOU THANK YOU THANK YOU!!!!   Where can I find some more
specifics
> on how to do this....  nothing I've found mentions this specific syntax!!!
> Thanks a MILLION!

> Please, one other question:  If I use a column alias in my query Access
> allows me to refer to that alias in the same query but SQL does not...
> example

> This is allowed in Access 2000 but NOT in SQL2K
> Select Sum(field1) as Total from table1 where Total > 100

> We're using a complicated calculation in the query and then assigning it
to
> one of several fields on the fly, without being able to reference the
alias,
> we'd have to recalc the thing 5-6 times for each record in the recordset
> (20,000 records)
> wardo



> > wardo,

> > select * from (select * from table1) X

> > -oj



> > > Bob,
> > >    again, thanks for the reply....
> > > Yes, this IS what we are trying to do, but we can't do it in Access
2000
> > > anymore, we need to port the reports to a larger DB, like MSSQL or
> Oracle.
> > > We are generating the recordsets in VB using ADO(thus the requirement
> for
> > > db-independence).  We are trying to reproduce the resulting recordsets
> > from
> > > the nested Access 2000 queries.  They work fine when connecting to the
> A2K
> > > DB, but error out when connecting to the SQL DB.  At present here is
> what
> > > I've tried in simple nested queries in SQL2K and found did not work:

> > > select * from [select * from table1]. as XX
> > > result=> "Incorrect syntax near the keyword 'as' "

> > > select * from `select * from table1`. as XX
> > > result=> "Incorrect syntax near '`' "
> > > "Incorrect syntax near '`' "

> > > If I'm still missing something, I'd love to know... :)
> > > Thanks!
> > > wardo



> > > > On Sat, 13 Oct 2001 15:31:32 -0400, "wardo"

> > > > >Bob,
> > > > >   Thanks for the reply!  Unfortunately we're already running our
> > reports
> > > in
> > > > >A2K and need a db-independent ADO solution so that we don't NEED
A2K.
> > > > >That's the kicker... :)  Thanks for the assist, though! :):)
> > > > >wardo

> > > > I don't understand your objection. Most SQL languages allow
subqueries
> > > > in the FROM clause. Your question suggested that this is what you
> > > > wanted to do. What's the problem?

> > > > HTH,
> > > > Bob Barrows
> > > > Please reply to the newsgroup. My reply-to address is my "spam trap"
> and
> > I
> > > don't check it very often.



Fri, 02 Apr 2004 05:36:14 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. Using Union subquery in select clause

2. UPDATE Query - using Subquery and Top Clause

3. Replication: WHERE clause in Primary:Target

4. Using a Recordset in a WHERE Clause

5. cannot have subqueries in CONNECT BY clause

6. restriction 'subqueries in CONNECT BY clause'

7. Subqueries in From clause

8. Search query w/o dynamic sql (subquery, case, where clause)

9. SET Clause subquery or UPDATE FROM possibly

10. subquery returning no rows in WHERE clause

11. Subqueries Fields in WHERE clause

12. Subqueries and GROUP BY clauses


 
Powered by phpBB® Forum Software