Dear Adiran, Thanks for your help !!
First I want to say it's not giving the error for this "" languaget...@repos.birchstreet.net", so there is any need to do the change as suggested by you. Second you suggested " exit with cur1; " - *You are right after putting the semi column my code is working fine.* There is also one query I have changed this line "*langCursor cur1%rowtype;" as below:* langCursor RECORD; --cur1%rowtype; Please read my code once again and suggest I did correct these change or not because this is suggested by one of my friend and first I am getting the error for this line. I am the new one for plsql code that's why I am taking the expert advice. Thanks SS On Wed, Jan 20, 2016 at 8:30 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 01/20/2016 06:32 AM, Sachin Srivastava wrote: > >> Dear Thom, >> >> Please find the complete code as below and suggest now. >> > > I would suggest spending some time here: > > http://www.postgresql.org/docs/9.4/interactive/plpgsql.html > > in particular: > > > http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS > > and here: > > http://www.postgresql.org/docs/9.4/interactive/plpgsql-porting.html > > Comments in-line > > >> ---------------------- >> >> -- 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 >> "languaget...@repos.birchstreet.net >> > > Have you tried the above. I know quoting it got you pass the syntax error, > but I am pretty sure it not going to do what it did in Oracle. > > <mailto:languaget...@repos.birchstreet.net>"; >> 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 >> > > From loop link above, this needs to be > > 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 <t...@linux.com >> <mailto:t...@linux.com>> wrote: >> >> On 20 January 2016 at 12:15, Sachin Srivastava >> <ssr.teleat...@gmail.com <mailto:ssr.teleat...@gmail.com>> 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 >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
