Re: Syntax error for Function

[Sachin Srivastava <ssr.teleatlas@xxxxxxxxx>]

Dear Thom,

Please find the complete code as below and suggest now.

----------------------

-- Function: gen_budget_for_next_year(bigint, bigint, bigint)

-- DROP FUNCTION gen_budget_for_next_year(bigint, bigint, bigint);

CREATE OR REPLACE FUNCTION gen_budget_for_next_year(
    subid bigint,
    compid bigint,
    formonth bigint)
  RETURNS void AS
$BODY$
DECLARE

--Version:
--2015.01 PM T15422-10- Generate budget period for coming years. 05/01/2015

cur1 CURSOR FOR SELECT distinct(language_id) from "languagetype@xxxxxxxxxxxxxxxxxxxxx";
sFound bigint := 0;
recCount bigint :=0;
period varchar(100);
firstDate varchar(100);
lastDate varchar(100);
curMonth varchar(100);
langCursor RECORD; --cur1%rowtype;

 
BEGIN
  loop
  open cur1;
  IF NOT FOUND THEN EXIT; END IF; -- apply on recCount >= forMonth;
 

        select to_char(TO_DATE(LOCALTIMESTAMP) + recCount*'1 month'::interval,'YYYYMM') into period  ;
        select to_date(period||'01','YYYYMMDD') into firstDate  ;
        select TO_DATE(LOCALTIMESTAMP) + recCount*'1 month'::interval into lastDate  ;

        select to_char(TO_DATE(LOCALTIMESTAMP) + recCount*'1 month'::interval, 'YYYY MM MONTH') into curMonth ;

        recCount :=recCount+1;

  loop
  fetch cur1 into langCursor;
  exit when cur1

            select Count(0) into sFound  from budget_period t where t.subscriber_id =subID
            and t.period_number = period and t.language_id=langCursor.Language_Id;
            if(sFound = 0)then         
                insert into budget_period (subscriber_id, company_id, period_number, period_name,
                period_length_code, first_day, last_day,creation_date, creation_user, update_date, update_user, language_id)
                values(subID, compID, period,  curMonth,  'MONTH',     
                firstDate, lastDate, LOCALTIMESTAMP, 'Admin',LOCALTIMESTAMP, 'Admin', langCursor.Language_Id);
            end if;
  end loop;
  close cur1;
  end loop;
 
commit;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION gen_budget_for_next_year(bigint, bigint, bigint)
  OWNER TO postgres;

--------------------------------

On Wed, Jan 20, 2016 at 6:05 PM, Thom Brown <thom@xxxxxxxxx> wrote:

On 20 January 2016 at 12:15, Sachin Srivastava <ssr.teleatlas@xxxxxxxxx> wrote:
> I am unable to find out the syntax error in below code, please suggest?
>
>
>
> ERROR:  syntax error at or near "select"
> LINE 44:             select Count(0) into sFound  from budget_period ...
>                      ^
> ********** Error **********
> ERROR: syntax error at or near "select"
> SQL state: 42601
> Character: 1190
>
> Code as below:
> -------------------------
>
> select Count(0) into sFound  from budget_period t where t.subscriber_id
> =subID
>             and t.period_number = period and
> t.language_id=langCursor.Language_Id;
>             if(sFound = 0)then
>                 insert into budget_period (subscriber_id, company_id,
> period_number, period_name,
>                 period_length_code, first_day, last_day,creation_date,
> creation_user, update_date, update_user, language_id)
>                 values(subID, compID, period,  curMonth,  'MONTH',
>                 firstDate, lastDate, LOCALTIMESTAMP, 'Admin',LOCALTIMESTAMP,
> 'Admin', langCursor.Language_Id);
>             end if;
>
> ------------------------

Well, it says that the problem occurs on line 44, so what's on the
previous lines it's receiving?  Are you sending an unterminated query
prior to that?

Thom