473,320 Members | 2,177 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

CREATE PROC error in MS-Access/Delphi

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?

Thanks
Rich

Nov 13 '05 #1
2 3857
ri*************@sage.com wrote:
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?


-----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-----
Nov 13 '05 #2
Thanks, I found the problem. The unique name of the first proc was
created using the machine name. The machine name had a hyphen in it
which was then interpreted as minus symbol in the second proc.

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Robert Hogan | last post by:
Hello, I am attempting to send emails using T-SQL (in a SQLServerAgent Job) using the stored procedure sp_SendSMTPMail. I created the stored proc using the following script that I got off a post...
14
by: aaron kempf | last post by:
I find that ADP does not support any Stored Procedures that use the 'CREATE PROC spHAPPY' syntax. CREATE PROC syntax is listed in books online. This syntax should be supported Here is a...
5
by: Jason Collins | last post by:
There are a number of stored procedures involved in sql server based session maintenance: - TempUpdateStateItemShort - TempResetTimeout - TempUpdateStateItemLong etc. These stored procs are...
3
by: CK | last post by:
Hi All, Quick question, I have always heard it best practice to check for exist, if so, drop, then create the proc. I just wanted to know why that's a best practice. I am trying to put that theory...
5
by: Philip Nelson | last post by:
I get - db2inst1@dvorak /db2data $ db2 "create database DBTST001 automatic storage yes on /db2data/db2db dbpath on /db2data/db2db" SQL0805N Package "NULLID.SQLE2F0J 0X4141414141654557" was not...
5
by: ljlevend2 | last post by:
Is there any way to create a local server during runtime? For example, if you add an existing Web Site to a Solution from within Visual Studio (by right clicking the solution in the Solution...
4
by: JohnnyDeep | last post by:
I am trying to create a store proc that contain a create index with the cluster option and I receive DB21034E The command was processed as an SQL statement because it was not a valid Command...
11
by: raylopez99 | last post by:
Keep in mind this is my first compiled SQL program Stored Procedure (SP), copied from a book by Frasier Visual C++.NET in Visual Studio 2005 (Chap12). So far, so theory, except for one bug...
2
by: semaj.remle 'at' gmail | last post by:
For files saved in source control, is it better to use code to DROP/ CREATE a procedure like this: ------------------------------------------------------------------ IF OBJECT_ID ('procName') IS...
2
by: =?Utf-8?B?TmV1bmc=?= | last post by:
Dear Sir, I'm not sure I'm in the correct discussion group or not. I would like to create a tool like a small script or program to run at a computer and then returns all networking results....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.