Is it possible to use a SELECT clause in a FROM clause 
Author Message
 Is it possible to use a SELECT clause in a FROM clause

Hi,

I want to use a SELECT clause in a FROM clausein this way:

SELECT .... FROM (SELECT ...)

It works fine in Microsoft Access (2000) but when a run the query from VB
(DAO 3.5/3.6) it fails and says "Error in FROM clause.".

Does anyone know why it doesn't work ik VB?

Thanks in advance

Edwin Engelen

The SQL-statement that doesn't work in VB:

SELECT
   ID, Omschrijving, Sum(AZVBJ) As TotaalAZVBJ, Sum(OZVBJ) As TotaalOZVBJ,
Sum(AZHBJ) As TotaalAZHBJ, Sum(OZHBJ) As TotaalOZHBJ
FROM
   (
   SELECT DEBSTM.Nummer AS ID, DEBSTM.Naam AS Omschrijving, 0 AS AZVBJ, 0 AS
OZVBJ, Sum(FAKARR.Aantal) AS AZHBJ, Sum(FAKARR.Prijs) AS OZHBJ
   FROM
      ([E:\Administraties\ASCON Automatisering b.v. 2001.mdb].FAKARR INNER
JOIN ([E:\Administraties\ASCON Automatisering b.v. 2001.mdb].FAKARK INNER
JOIN ([E:\Administraties\ASCON Automatisering b.v..mdb].DEBFAC INNER JOIN
[E:\Administraties\ASCON Automatisering b.v. 2001.mdb].DEBSTM ON
DEBFAC.Nummer = DEBSTM.Nummer) ON FAKARK.Factuurnummer =
DEBFAC.Factuurnummer) ON FAKARR.Factuurnummer = FAKARK.Factuurnummer) INNER
JOIN [E:\Administraties\ASCON Automatisering b.v. 2001.mdb].VAMSTM ON
FAKARR.Artikelnummer = VAMSTM.Artikelnummer
   WHERE
      DEBFAC.Boekjaar='2001'
   GROUP BY
      DEBSTM.Nummer, DEBSTM.Naam
   UNION
   SELECT
      DEBSTM.Nummer AS ID, DEBSTM.Naam AS Omschrijving, Sum(FAKARR.Aantal)
AS AZVBJ, Sum(FAKARR.Prijs) AS OZVBJ, 0 AS AZHBJ, 0 AS OZHBJ
   FROM
      ([E:\Administraties\ASCON Automatisering b.v. 2000.mdb].FAKARR INNER
JOIN ([E:\Administraties\ASCON Automatisering b.v. 2000.mdb].FAKARK INNER
JOIN ([E:\Administraties\ASCON Automatisering b.v..mdb].DEBFAC INNER JOIN
[E:\Administraties\ASCON Automatisering b.v. 2000.mdb].DEBSTM ON
DEBFAC.Nummer = DEBSTM.Nummer) ON FAKARK.Factuurnummer =
DEBFAC.Factuurnummer) ON FAKARR.Factuurnummer = FAKARK.Factuurnummer) INNER
JOIN [E:\Administraties\ASCON Automatisering b.v. 2000.mdb].VAMSTM ON
FAKARR.Artikelnummer = VAMSTM.Artikelnummer
   WHERE
      DEBFAC.Boekjaar='2000'
   GROUP BY
      DEBSTM.Nummer, DEBSTM.Naam
   )
GROUP BY
   ID, Omschrijving
ORDER BY
   ID, Omschrijving
;



Wed, 25 Feb 2004 15:34:31 GMT
 Is it possible to use a SELECT clause in a FROM clause

See below.

Quote:

>Hi,

>I want to use a SELECT clause in a FROM clausein this way:

>SELECT .... FROM (SELECT ...)

>It works fine in Microsoft Access (2000) but when a run the query from VB
>(DAO 3.5/3.6) it fails and says "Error in FROM clause.".

>Does anyone know why it doesn't work ik VB?

>Thanks in advance

>Edwin Engelen

>The SQL-statement that doesn't work in VB:

>SELECT
>   ID, Omschrijving, Sum(AZVBJ) As TotaalAZVBJ, Sum(OZVBJ) As TotaalOZVBJ,
>Sum(AZHBJ) As TotaalAZHBJ, Sum(OZHBJ) As TotaalOZHBJ
>FROM
>   (
>   SELECT DEBSTM.Nummer AS ID, DEBSTM.Naam AS Omschrijving, 0 AS AZVBJ, 0 AS
>OZVBJ, Sum(FAKARR.Aantal) AS AZHBJ, Sum(FAKARR.Prijs) AS OZHBJ
>   FROM
>      ([E:\Administraties\ASCON Automatisering b.v. 2001.mdb].FAKARR INNER
>JOIN ([E:\Administraties\ASCON Automatisering b.v. 2001.mdb].FAKARK INNER
>JOIN ([E:\Administraties\ASCON Automatisering b.v..mdb].DEBFAC INNER JOIN
>[E:\Administraties\ASCON Automatisering b.v. 2001.mdb].DEBSTM ON
>DEBFAC.Nummer = DEBSTM.Nummer) ON FAKARK.Factuurnummer =
>DEBFAC.Factuurnummer) ON FAKARR.Factuurnummer = FAKARK.Factuurnummer) INNER
>JOIN [E:\Administraties\ASCON Automatisering b.v. 2001.mdb].VAMSTM ON
>FAKARR.Artikelnummer = VAMSTM.Artikelnummer
>   WHERE
>      DEBFAC.Boekjaar='2001'
>   GROUP BY
>      DEBSTM.Nummer, DEBSTM.Naam
>   UNION
>   SELECT
>      DEBSTM.Nummer AS ID, DEBSTM.Naam AS Omschrijving, Sum(FAKARR.Aantal)
>AS AZVBJ, Sum(FAKARR.Prijs) AS OZVBJ, 0 AS AZHBJ, 0 AS OZHBJ
>   FROM
>      ([E:\Administraties\ASCON Automatisering b.v. 2000.mdb].FAKARR INNER
>JOIN ([E:\Administraties\ASCON Automatisering b.v. 2000.mdb].FAKARK INNER
>JOIN ([E:\Administraties\ASCON Automatisering b.v..mdb].DEBFAC INNER JOIN
>[E:\Administraties\ASCON Automatisering b.v. 2000.mdb].DEBSTM ON
>DEBFAC.Nummer = DEBSTM.Nummer) ON FAKARK.Factuurnummer =
>DEBFAC.Factuurnummer) ON FAKARR.Factuurnummer = FAKARK.Factuurnummer) INNER
>JOIN [E:\Administraties\ASCON Automatisering b.v. 2000.mdb].VAMSTM ON
>FAKARR.Artikelnummer = VAMSTM.Artikelnummer
>   WHERE
>      DEBFAC.Boekjaar='2000'
>   GROUP BY
>      DEBSTM.Nummer, DEBSTM.Naam
>   )
>GROUP BY
>   ID, Omschrijving
>ORDER BY
>   ID, Omschrijving
>;

I don't use A2K, so I may be wrong, but ...
I think your problem is being caused by using the DAO 3.5/3.6 library.
I generally avoid "compatability" libraries, preferring to fix my code
so it works with the new library.

Try switching to the 3.6 library. If that does not help, see below:

The following is an undocumented feature, 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

I have successfully tested using DAO in A97 to open recordsets on
queries that follow the above rules.

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, 25 Feb 2004 21:36:50 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. results from SELECT clause used in an IN clause

2. I don't know the difference of using index clause and just constraint clause

3. Using case stement in a where clause with an in clause

4. Writing a SELECT with three possible WHERE CLAUSE (Need some help)

5. Using a variable in Select clause

6. Using expressions AS a select clause

7. Using expressions in a SELECT Clause

8. Using DELETE and a SELECT with the TOP Clause

9. Using Stored Procedure in an IN clause in a SELECT statement

10. Using Union subquery in select clause

11. UDF using a select clause

12. Using a variable in the select from clause


 
Powered by phpBB® Forum Software