Hello,


I use a stored procedure to concat 2 fields where the second can have
NULL value.
But I have an Invalid request BLR at offset XXXX when I try to use a
Distinct clause.

For example :
select distinct Q2.*,(select FIRSTLASTNAME from CONCAT_FIRSTLASTNAME
(Q2.FIRSTNAME ,Q2.LASTNAME)) R_FIRSTLASTNAME
from CUSTOMER Q2

Give me "Invalid request BLR at offset XXXX " error

With :

CREATE PROCEDURE CONCAT_FIRSTLASTNAME
(
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50)
)
RETURNS
(
FIRSTLASTNAME VARCHAR(101)
)
AS
BEGIN
SELECT '' FROM RDB$DATABASE INTO :FIRSTLASTNAME;
IF ((FIRSTNAME IS NOT NULL) AND (FIRSTNAME != ""))
then FIRSTLASTNAME = :LASTNAME || " " || :FIRSTNAME;
IF ((LASTNAME IS NULL) OR (LASTNAME = ""))
then FIRSTLASTNAME = :FIRSTNAME;
IF ((FIRSTNAME IS NULL) OR (FIRSTNAME = ""))
then FIRSTLASTNAME = :LASTNAME;
SUSPEND;
END
^


DISTINCT clause don't work with stored procedure (It work fine without it) !
Firebird 1.5 work fine but not firebird 1.0X !


How can I do without using Firebird 1.5 ?
Because I need to use a DISTINCT and concat field with NULL values...


--

Fabrice Vendé
fabrice@...
-
SARL I N F O C O B
Développement, formation, matériel, logiciel
http://www.infocob.com