stored procedure returns the same data even if the parameter changes ?!
I have a stored procedure in IDS 9.4 which searches through a table
with about 900.000 rows.
I use that for a report (Crystal Reports)
It used to work fine until a few days ago, now despite the fact that
the parameter changes it keeps returning the same data that returned
the first time. This happens for the Crystal Reports so I get all the
reports being the same but it also happens through WinSQL.
It gives me the first results correct and after that despite changing
the parameters (which in this case is territory of the sales and
dates) it keeps giving me the same data until I close and open again
WinSQL . When I open it again it gives me the right data and then
starts giving me the same data.
I use ODBC 2.9 through a DSN to connect to database.
Here is the stored procedure:
CREATE PROCEDURE get_sales_accounts(cterr CHAR(4), cdate DATE, edate DATE)
RETURNING CHAR(10), CHAR(10), CHAR(40), CHAR(40),
CHAR(20),CHAR(20),CHAR(10),CHAR(10),CHAR(4), CHAR(4),
CHAR(1), CHAR(20),DATE, CHAR(40), CHAR(40), CHAR(30), DATE, INTEGER,DECIMAL(12,2),CHAR(4), DATE, CHAR(4), CHAR(60);
DEFINE v_ship_num CHAR(10);
DEFINE v_cust_num CHAR(10);
DEFINE v_ship_name CHAR(40);
DEFINE v_address1 CHAR(40);
DEFINE v_city CHAR(20);
DEFINE v_province CHAR(20);
DEFINE v_postal_code CHAR(10);
DEFINE v_country CHAR(10);
DEFINE v_terr_code CHAR(4);
DEFINE v_slmn_num CHAR(4);
DEFINE v_stat CHAR(1);
DEFINE v_phone CHAR(20);
DEFINE v_inv_date_last DATE;
DEFINE v_first_name CHAR(40);
DEFINE v_last_name CHAR(40);
DEFINE v_desc_1 CHAR(30);
DEFINE v_date_created DATE;
DEFINE v_inv_shp_l_id INTEGER;
DEFINE v_tot_net_amt DECIMAL(12,2);
DEFINE v_sa_item CHAR(4);
DEFINE v_trans_date DATE;
DEFINE v_sa_cust CHAR(4);
DEFINE v_email_address CHAR(60);
FOREACH cs_inv_line FOR
select a.ship_num,a.cust_num, a.ship_name,a.address1,
a.city,a.province,a.postal_code,a.country,
a.terr_code,a.slmn_num, a.stat,
a.phone,a.inv_date_last,a.first_name,a.last_name,a.desc_1,
a.date_created, b.inv_shp_l_id,b.tot_net_amt,
b.sa_item, b.trans_date, a.sa_cust, a.email_address
into v_ship_num,v_cust_num, v_ship_name,v_address1,
v_city,v_province,v_postal_code,v_country,
v_terr_code,v_slmn_num, v_stat,
v_phone,v_inv_date_last,v_first_name,v_last_name,v_desc_1,
v_date_created, v_inv_shp_l_id,v_tot_net_amt,
v_sa_item, v_trans_date,v_sa_cust, v_email_address
from cust_ship_view a, outer salesrep b
where a.ship_num = b.ship_cust
and a.terr_code like cterr
and b.trans_date >= cdate
and b.trans_date <=edate
and a.date_created <=edate
RETURN v_ship_num,v_cust_num, v_ship_name,v_address1,
v_city,v_province,v_postal_code,v_country,
v_terr_code,v_slmn_num, v_stat,
v_phone,v_inv_date_last,v_first_name,v_last_name,v_desc_1,
v_date_created, v_inv_shp_l_id,v_tot_net_amt,
v_sa_item, v_trans_date, v_sa_cust,v_email_address WITH RESUME;
END FOREACH
END PROCEDURE
I would appreciate any help as this is driving me crazy and I have
about 100 reports that before would run fine automatically. Now I will
have to run them one by one if I do not get this fixed.
Thank you very much
Genti