richard.nicholl@sage.com wrote:[color=blue]
> Hi,
>
> My application is in Delphi 5 with an Access database. I'm trying to
> populate a TdbGrid pointing to a TTable component.
>
> Using a TQuery I create a stored procedure (with a unique name that has
> been dropped before I create it) in Access DB as follows :
>
> CREATE PROC OSBALS_NS1 AS
> SELECT C.ClientKey, C.WIPBAL AS UNALLOC_WIP, IIF([W].[WIPBAL] IS
> NULL,0,[W].[WIPBAL]) AS UNALLOC_BILLS,
> UNALLOC_WIP - UNALLOC_BILLS AS WIPBAL
> FROM CLIENTS AS C LEFT JOIN (SELECT CLIENTCD, SUM(AMOUNT) AS WIPBAL
> FROM WIP
> WHERE TRNSUBTYPE IN ('U') GROUP BY CLIENTCD) AS W ON C.CLIENTKEY =
> W.CLIENTCD
>
>
> Then I create a second stored procedure in Access DB in where the
> select statment has a left join on the results of the first SP as
> follows:
>
> CREATE PROC CLIENTBALS_NS1 AS
> SELECT C.ClientUId, C.ClientKey, C.ClientKey As ClientCode, C.CoyName,
> CL.WIPBAL, C.FeesBal, A.Address1, C.Entity, C.ClientKey AS Type
> FROM (CLIENTS AS C LEFT JOIN Addresses AS A ON C.AddrKey = A.AddrKey)
> LEFT JOIN OSBALS_NS1 AS CL ON C.ClientKey = CL.ClientKey
> WHERE C.ClientKey <> '0000000000'
> AND UCASE(C.CoyName) LIKE '*'
> ORDER BY C.ClientKey, C.CoyName
>
>
> This works fine on most machines, but on others it returns a SQL error
> message saying there is an error in the FROM clause. All machines are
> running Win XP SP2 and have no other real differences.
>
> Anyone got any ideas why this might not work on certain machines?[/color]
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
What version of Access (JET - the db engine) are you using?
Earlier versions of JET don't recognize the derived tables w/
parentheses. You have to use square brackets. E.g.:
SELECT ...
FROM [SELECT * FROM table]. As A INNER JOIN B ON A.ID = B.ID
Note the period immediately after the right square bracket, that is
required.
In Access 2002 & above (and, I believe, Access 2000) you can set the db
to SQL-92 compliant, and that will allow derived tables w/ parentheses.
Use Tools > Options > Tables/Queries - SQL Server Compatible Syntax (SQL
92). You have to do this for each machine that is running JET/Access.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQiYdFoechKqOuFEgEQLW5gCeLJlw1dIjtsUH39WSQ3Dg/CC62P8AnioM
Mc0kPqOVYU3mqYKkLgpuAhfd
=qjDu
-----END PGP SIGNATURE-----
