At 03:57 PM 19-02-02 +0000, you wrote:

>Either I really don't understand or above claim is not correct - I
>think comparision NULL = NULL is evaluated to FALSE (or UNKNOWN ? -
>in Firebird and Interbase manuals this part is not very clear and in
>one chapter the result of this comparision is stated as FALSE, in
>other chapter as UNKNOWN...but it doesn't matter, because result of
>EXISTS() is the same) so matter of whether the procedure's input
>argument has a null value in that sub-clause HAS bearing on the
>result of the EXISTS().

Pardon me if I can't make sense of the paragraph above...let's start again...

the construct
if (exists(:variable is null))

is not valid SQL.

[snip]

>The same construction I use e.g.
>(:ARDB$DB_KEY IS NULL) OR (CUSTOMER.RDB$DB_KEY <> :ARDB$DB_KEY)
>in OnBeforePost events

A BeforePost event is a Delphi event, not an SQL statement. Just because something works in a Delphi statement does not mean it will work in SQL.

but I can use the

>same stored procedure for "before post check".

Something seems very muddled here. Are you saying that you call a stored procedure in a BeforePost check????

If so, that's a different problem from the immediate one (What caused the DSQL error?) Sorry, but I don't want to spend any more time on that unless I can get my head round what you're attempting to do in your application.

To focus on the error you reported:

Originally you posted that you were getting an 'UNKNOWN TYPE' error from an SQL statement. The clause
WHERE :APARAM IS NULL
is not valid SQL

A WHERE clause can be:

WHERE aColIdentifier IS NULL
WHERE aColIdentifier = :APARAM
WHERE aColIdentifier = aConstant
and even
WHERE aConstant = aConstant (a syllogistic test which can be used in some cases to defeat a bad PLAN by the optimizer)

i.e. the DB engine can work out the data type by examining the attributes it has stored for the column cited in the WHERE clause...

WHERE :APARAM IS NULL isn't valid because the engine has no way to work out the data type of :APARAM.

So that explains the error that you are getting.

I attempted to explain why (possibly) the PSQL parser didn't reject a similar-looking invalid WHERE clause in your stored procedure and went on to say that external criteria were out of place in an EXISTS() predicate, regardless of the parsing ambiguity (PSQL interprets :APARAM as a constant because it is passed into the procedure as an argument and its data type is known, via the argument declaration).

EXISTS() tests for the existence of the SELECTed dataset in the database.

Aside from whether you should be calling this SP at all (your other problem...) a review of the logic of both of your versions would produce a much simpler procedure:

CREATE PROCEDURE SPS_CUSTOMER$ADDRESS_EXISTS
(ACITY_CODE VARCHAR(5),
ACITY_STREET_ID INTEGER,
ASTREET_NUMBER VARCHAR(10),
ABUILDING_REG VARCHAR(10))
RETURNS
(ARESULT SMALLINT)
AS
declare variable LastStreetID integer;
BEGIN
ARESULT = 0;
for
SELECT CITY_STREET_ID FROM CUSTOMER
WHERE CITY_CODE = :ACITY_CODE
AND STREET_NUMBER_UPPER = :ASTREET_NUMBER
AND BUILDING_REG_UPPER = :ABUILDING_REG
into :LastStreetID do
begin
if (ACITY_STREET_ID is not null) then
if (LastStreetID = :ACITY_STREET_ID) then
ARESULT = 1;
end
END

Reason: your version of your SP indicates that you only want to get ARESULT = 1 if the :ACITY_STREET_ID parameter value is not null AND there is a match for :ACITY_STREET_ID involving the other parameters. For any other condition you want to return ARESULT = 0. You are not even interested in the condition where a non-null ACITY_STREET_ID exists in combination with the other three argument-values, that does not match :ACITY_STREET_ID.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at www.ibobjects.com