Re: error : syntax error at or near $1 for over select rows

On Dec 27, 2004, at 11:36 AM, vinita bansal wrote:

> 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;
>

for1 is a record type from which you can access the other columns. So
with something like

FOR for1 in select * from cm_quotainstance where ... loop

then you can access the columns in your subsequent if statements like:

if (for1.startdate > > M_COMM_CLOSE_DATE OR for1.enddate <=
M_COMM_CLOSE_DATE) ...

Look in the plpgsql section of the documentation under "Looping Through
Query Results" -- this is section 36.7.4 in the 8.0 documentation.

Best,

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL