P
Peter J. Veger
I am trying to upsize a mdb-application to an adp-application (20 tables,
170 queries, 20 forms, 80 reports, 26 class modules, 1 non-class module,
ADO)
Only a small part of the queries were translated ---- primary reason: usage
of VBA-functions from the non-class module.
I am now busy to translate manually the queries to T-SQL and that
translation goes very well but for one problem.
The following in-line procedure (simplified) is not accepted ----- gives an
ADO error:
===============
CREATE PROCEDURE Statistics(@RefDatum smalldatetime)
RETURNS table
AS RETURN (
SELECT Persons.PID AS PID,
Persons.Email IS NULL AS HasEmail,
EXISTS( SELECT * FROM PersonsBoats WHERE Persons.PID = PersonsBoats.PID) AS
HasBoat
FROM Persons
WHERE Year(Persons.SomeDate)=Year(@RefDatum)
================
The bit-valued expressions
expr IS NULL
and
EXISTS(SELECT ......)
are accepted in Access-ADO-mdb but not in Access-ADO-adp.
Also brackets around these expression don't have any effect.
From the Books-Online:
1) The "unary postfix" operators IS [NOT] NULL do not occur among the
Operators but the text of "IS [NOT] NULL" makes one belief that they build
expressions, e.g. their use in a WHERE clause:
...WHERE ..... OR advance IS NULL....
2) EXISTS(subquery) is mentioned in "Operators", but subquery is in general
not an expression --- "Expressions" allows only scalar_subquery!
What is wrong?
Is there a possibility to circumvent these vague restrictions?
170 queries, 20 forms, 80 reports, 26 class modules, 1 non-class module,
ADO)
Only a small part of the queries were translated ---- primary reason: usage
of VBA-functions from the non-class module.
I am now busy to translate manually the queries to T-SQL and that
translation goes very well but for one problem.
The following in-line procedure (simplified) is not accepted ----- gives an
ADO error:
===============
CREATE PROCEDURE Statistics(@RefDatum smalldatetime)
RETURNS table
AS RETURN (
SELECT Persons.PID AS PID,
Persons.Email IS NULL AS HasEmail,
EXISTS( SELECT * FROM PersonsBoats WHERE Persons.PID = PersonsBoats.PID) AS
HasBoat
FROM Persons
WHERE Year(Persons.SomeDate)=Year(@RefDatum)
================
The bit-valued expressions
expr IS NULL
and
EXISTS(SELECT ......)
are accepted in Access-ADO-mdb but not in Access-ADO-adp.
Also brackets around these expression don't have any effect.
From the Books-Online:
1) The "unary postfix" operators IS [NOT] NULL do not occur among the
Operators but the text of "IS [NOT] NULL" makes one belief that they build
expressions, e.g. their use in a WHERE clause:
...WHERE ..... OR advance IS NULL....
2) EXISTS(subquery) is mentioned in "Operators", but subquery is in general
not an expression --- "Expressions" allows only scalar_subquery!
What is wrong?
Is there a possibility to circumvent these vague restrictions?