From: | "vinita bansal" <sagivini(at)hotmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | error : syntax error at or near $1 for over select rows |
Date: | 2004-12-27 16:36:21 |
Message-ID: | BAY20-F31E72B46AB4F60EE23DFE1CB990@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Dan Boitnott | 2004-12-27 16:39:48 | Large Objects |
Previous Message | Michael Fuhr | 2004-12-27 14:54:06 | Re: Get current trasanction id |