Обсуждение: Syntax error for Function
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 **********
LINE 44: select Count(0) into sFound from budget_period ...
^
********** Error **********
ERROR: syntax error at or near "select"
SQL state: 42601
Character: 1190
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;
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;
------------------------
On 20 January 2016 at 12:15, Sachin Srivastava <ssr.teleatlas@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
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
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
--2015.01 PM T15422-10- Generate budget period for coming years. 05/01/2015
cur1 CURSOR FOR SELECT distinct(language_id) from "languagetype@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;
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_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
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;
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;
$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@linux.com> wrote:
Well, it says that the problem occurs on line 44, so what's on theOn 20 January 2016 at 12:15, Sachin Srivastava <ssr.teleatlas@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;
>
> ------------------------
previous lines it's receiving? Are you sending an unterminated query
prior to that?
Thom
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 > "languagetype@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:languagetype@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 <thom@linux.com > <mailto:thom@linux.com>> wrote: > > On 20 January 2016 at 12:15, Sachin Srivastava > <ssr.teleatlas@gmail.com <mailto:ssr.teleatlas@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.klaver@aklaver.com
Dear Adiran,
Thanks for your help !!
First I want to say it's not giving the error for this ""languagetype@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.klaver@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
"languagetype@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:languagetype@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;<mailto:thom@linux.com>> wrote: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@linux.com
On 20 January 2016 at 12:15, Sachin Srivastava<ssr.teleatlas@gmail.com <mailto:ssr.teleatlas@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.klaver@aklaver.com
On 01/20/2016 08:00 AM, Sachin Srivastava wrote: > Dear Adiran, > > Thanks for your help !! > > First I want to say it's not giving the error for > this ""languagetype@repos.birchstreet.net > <mailto:languagetype@repos.birchstreet.net>", so there is any need to do > the change as suggested by you. You are not seeing a syntax error, but I am pretty sure you will see a run error as from what I gather languagetype@repos.. is an Oracle construct: http://stackoverflow.com/questions/296263/what-is-the-meaning-of-symbol-in-oracle-sql Pretty sure Postgres is going to fail on: cur1 CURSOR FOR SELECT distinct(language_id) from "languagetype@repos.birchstreet.net " when it tries to execute the query. > > 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; No ; at end of comment: http://www.postgresql.org/docs/9.4/static/plpgsql-structure.html > > 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. That is why I suggested taking a look at the plpgsql section of the manual. A quick run through will answer most of your questions. > > > Thanks > SS > > On Wed, Jan 20, 2016 at 8:30 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@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 > "languagetype@repos.birchstreet.net > <mailto:languagetype@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:languagetype@repos.birchstreet.net > <mailto:languagetype@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 <thom@linux.com > <mailto:thom@linux.com> > <mailto:thom@linux.com <mailto:thom@linux.com>>> wrote: > > On 20 January 2016 at 12:15, Sachin Srivastava > <ssr.teleatlas@gmail.com <mailto:ssr.teleatlas@gmail.com> > <mailto:ssr.teleatlas@gmail.com > <mailto:ssr.teleatlas@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com