Hi:

Thanks.

--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> OK. The first thing I would do is figure out what table and
> field RDB$INDEX_18 references. Something like:
>
> select i.rdb$relation_name, ii.rdb$field_name
> from rdb$indices i join rdb$index_fields ii
> on (i.rdb$index_name = ii.rdb$index_name);
>
> Then I would use that information to query the same tables
> looking for the duplicates.

Got it past the create to the execute/select

If I call:
execute procedure GETTENANTDATALIST('01/01/1998', '01/01/1998')
it returns one record.

If I call
select * from GETTENANTDATALIST('01/01/1998', '01/01/1998')
then I get an error (error at line 1) and loose the connection.

Any suggestion sappreciated.

Best regards

Create Procedure Code:

SET TERM ## ;
CREATE PROCEDURE GETTENANTDATALIST(IN_DATEENTRY DATE, IN_DATEUP DATE)
RETURNS (ssn varchar(12),
birth DATE,
firstname VARCHAR(50),
mi VARCHAR(2),
lastname VARCHAR(50),
address VARCHAR(50),
subadd VARCHAR(10),
street VARCHAR(50),
apt VARCHAR(10),
city VARCHAR(50),
st VARCHAR(3),
zip VARCHAR(10),
ssncot VARCHAR(12),
birthcot DATE,
firstnamecot VARCHAR(50),
micot VARCHAR(2),
lastnamecot VARCHAR(50),
datein DATE,
dateout DATE,
court VARCHAR(4),
courtaction VARCHAR(20),
dispo VARCHAR(15),
source VARCHAR(12),
pay VARCHAR(10),
clean VARCHAR(10),
problems VARCHAR(15),
comments VARCHAR (100),
dateentry DATE,
dateup DATE,
id INTEGER,
mgrid INTEGER)
AS
BEGIN
FOR SELECT
t1.SSN_TEN,
t1.BIRTH_TEN,
PROPERCASESTRING(t1.NAME1_TEN,''),
PROPERCASESTRING(t1.MI_TEN,''),
PROPERCASESTRING(t1.NAME2_TEN,''),
t1.ADRESS_TEN,
t1.SUBADD_TEN,
PROPERCASESTRING(t1.STNAME_TEN,''),
PROPERCASESTRING(t1.APT_TEN,''),
PROPERCASESTRING(t1.CITY_TEN,''),
t1.STATE_TEN,
t1.ZIP_TEN,
t1.SSN_COT,
t1.BIRTH_COT,
PROPERCASESTRING(t1.NAME1_COT,''),
PROPERCASESTRING(t1.MI_COT,''),
PROPERCASESTRING(t1.NAME2_COT,''),
t1.DATE_IN,
t1.DATE_OUT,
PROPERCASESTRING(t1.COURT_INIT,''),
t1.COURT_ACT,
PROPERCASESTRING(t1.COURT_DISP,''),
PROPERCASESTRING(t1.CORT_SORCE,''),
PROPERCASESTRING(t1.PAY_RENT,''),
PROPERCASESTRING(t1.CLEAN,''),
PROPERCASESTRING(t1.PRBLMS,''),
t1.COMMENTS,
t1.DATE_ENTRY,
t1.DATE_UP,
t1.ID,
t1.MGRID
FROM TENANTS t1
WHERE t1.DATE_ENTRY > :IN_DATEENTRY or t1.DATE_UP > :IN_DATEUP
ORDER BY t1.NAME2_TEN ASC
INTO
:ssn,:birth,:firstname,:mi,:lastname,:address,:subadd,:street,:apt,:city,:st,:zip,:ssncot,:birthcot,:firstnamecot,:micot,:lastnamecot,:datein,:dateout,:court,:courtaction,:dispo,:source,:pay,:clean,:problems,:comments,:dateentry,:dateup,:id,:mgrid
DO
SUSPEND;
END ##
SET TERM ; ##