At 10:18 AM 19-02-02 +0000, you wrote:

>Sorry to trouble all of you, but this IB_query I can't prepare:
>
>SELECT CUSTOMER.ID
>FROM CUSTOMER
>WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)

> AND (((:ACITY_STREET_ID IS NULL) <-- this syntax is wrong.

WHERE :ACITY_STREET_D IS NULL is invalid, since the identifier should be pointing to a column in a table. It looks as if it shouldn't be there, anyway, since the next sub-clause *does* point to the column (brackets not corrected):

>AND (CUSTOMER.CITY_STREET_ID IS
>NULL)) OR (CUSTOMER.CITY_STREET_ID = :ACITY_STREET_ID))
> AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER) AND
>(CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)
>
>Error message:
>ISC ERROR CODE:335544569
>Dynamic SQL Error
>SQL Error code = -804
>Data type unknown
>
>Critical part of SQL is "(:ACITY_STREET_ID IS NULL)" (without this
>part I can prepare the IB_Query...)
>
>The curiosity is that in database is declared stored 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
>BEGIN
>...
>IF (EXISTS(SELECT * FROM CUSTOMER
>WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)
> AND (((:ACITY_STREET_ID IS NULL) AND (CUSTOMER.CITY_STREET_ID IS
>NULL)) OR (CUSTOMER.CITY_STREET_ID = :ACITY_STREET_ID))
> AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER) AND
>(CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)))
>THEN
> ARESULT = 1;
>ELSE
> ARESULT = 0;
>...
>END
>
>with the same WHERE part of SQL which works fine...

"works fine" in the sense that it doesn't throw an error? In PSQL the parser probably allowed it somehow because you had ACITY_STREET_ID defined as an input. As such, it could be treated as a variable within your procedure. IMO, its illegal use in the WHERE clause ought to have prevented the procedure from compiling. If it DID compile, then I think we are seeing a parser error in the database.

Regardless of what the PSQL parser allowed, the matter of whether the procedure's input argument has a null value in that sub-clause has NO bearing on the result of the EXISTS() predicate and it has no business being there.

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