This is the error i am getting when calling select * from
cas_reset_qi_changedate('CAS','2003-02-03'
ERROR: syntax error at or near "$1" at character 25
CONTEXT: PL/pgSQL function "cas_reset_qi_changedate" line 15 at for over
select rows
pgsql function :
CREATE or replace FUNCTION qe13.CAS_RESET_QI_CHANGEDATE (VARCHAR,TIMESTAMP)
RETURNS INTEGER AS '
DECLARE
AGR_UNID ALIAS for $1;
M_COMM_CLOSE_DATE ALIAS for $2;
DEFAULT_LOWEST_DATE timestamp default ''1900-01-01 00:00:00.0'';
vQuotaInstanceGID varchar(34) default null;
vQIStartDate timestamp;
vQIEndDate timestamp;
for1 record;
BEGIN
FOR for1 in select qi.tril_gid as vQuotaInstanceGID,qi.startdate as
vQIStartDate,qi.enddate as vQIEndDate from cm_quotainstance as qi, cm_quota
as q, fs_agr as a where a.fs_unid = AGR_UNID and a.fs_model = q.model and
qi.quota = q.tril_gid LOOP
if (vQIStartDate > M_COMM_CLOSE_DATE OR vQIEndDate
<= M_COMM_CLOSE_DATE) then
update cm_quotainstance set changedate =
DEFAULT_LOWEST_DATE where tril_gid = vQuotaInstanceGID;
end if;
END LOOP;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
I know the problem is that we cant use a variable name in a select query.
But then how can I use this variable later in "if" statement (say
vQIStartDate variable)
Thanks and Regards,
Vinita Bansal
_________________________________________________________________
NRIs send 10 photos FREE to India.
http://creative.mediaturf.net/creatives/icicibank/june/kodak/OTP.htm And win
a FREE ticket to India.
