Function error

Started by Sachin Srivastavaover 10 years ago12 messagesgeneral

ssr.teleatlas@gmail.com

over 10 years ago

Dear Concern,

I am creating below function *“create_catexp_ss_master()” *and getting
error as below, I have already created dependent function firstly
successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but still
getting error, please suggest why?

ERROR: syntax error at or near "create_catexp_ss_1"

LINE 38: create_catexp_ss_1;

********** Error **********

ERROR: syntax error at or near "create_catexp_ss_1"

SQL state: 42601

Character: 1104

-- Function: create_catexp_ss_master()

-- DROP FUNCTION create_catexp_ss_master();

CREATE OR REPLACE FUNCTION create_catexp_ss_master()

RETURNS void AS

$BODY$

DECLARE

-- Build snapshot tables for catalog itme exposure.

-- Versions:

-- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13

-- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
and from the Code. 05/23/13

v_count_before bigint;

v_count_after bigint;

v_start_time timestamp;

v_err_msg varchar(1000);

v_set_name varchar(10);

BEGIN

v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;

SELECT LOCALTIMESTAMP INTO v_start_time ;

if v_set_name='A' then

SELECT count(1) INTO v_count_before FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_before FROM
pcat_exp_supp_buyer_ss_b;

end if;

-- Remove old data.

EXECUTE 'truncate table
pcat_exp_supp_buyer_ss_'||v_set_name;

EXECUTE 'truncate table
pcat_exp_supp_cat_buyer_ss_'||v_set_name;

-- Exposure for single supplier without category
filtering

create_catexp_ss_1;

-- Exposure for single supplier with category filtering

create_catexp_ss_2;

if v_set_name='A' then

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_b;

end if;

-- Log

create_ss_log('Catalog Exposure', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, null);

exception -- log error

when others then

v_err_msg := SQLERRM;

create_ss_log('Catalog Exposure - Error', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before,
v_count_after, v_err_msg);

END;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER

COST 100;

ALTER FUNCTION create_catexp_ss_master()

OWNER TO postgres;

Regards,
SS

Pavel Stehule

pavel.stehule@gmail.com

over 10 years ago

In reply to: Sachin Srivastava (#1)

Re: Function error

2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Dear Concern,

I am creating below function *“create_catexp_ss_master()” *and getting
error as below, I have already created dependent function firstly
successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but still
getting error, please suggest why?

ERROR: syntax error at or near "create_catexp_ss_1"

LINE 38: create_catexp_ss_1;

^

********** Error **********

ERROR: syntax error at or near "create_catexp_ss_1"

SQL state: 42601

Character: 1104

-- Function: create_catexp_ss_master()

-- DROP FUNCTION create_catexp_ss_master();

CREATE OR REPLACE FUNCTION create_catexp_ss_master()

RETURNS void AS

$BODY$

DECLARE

-- Build snapshot tables for catalog itme exposure.

-- Versions:

-- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13

-- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
and from the Code. 05/23/13

--

v_count_before bigint;

v_count_after bigint;

v_start_time timestamp;

v_err_msg varchar(1000);

v_set_name varchar(10);

BEGIN

v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;

SELECT LOCALTIMESTAMP INTO v_start_time ;

if v_set_name='A' then

SELECT count(1) INTO v_count_before FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_before FROM
pcat_exp_supp_buyer_ss_b;

end if;

-- Remove old data.

EXECUTE 'truncate table
pcat_exp_supp_buyer_ss_'||v_set_name;

EXECUTE 'truncate table
pcat_exp_supp_cat_buyer_ss_'||v_set_name;

Attention - this is potentially serious security bug

EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
v_set_name);

-- Exposure for single supplier without category filtering

create_catexp_ss_1;

you have to call this function via PERFORM statement

PERFORM create_catexp_ss_1();

-- Exposure for single supplier with category filtering

create_catexp_ss_2;

if v_set_name='A' then

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_b;

end if;

-- Log

create_ss_log('Catalog Exposure', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, null);

exception -- log error

when others then

v_err_msg := SQLERRM;

create_ss_log('Catalog Exposure - Error', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before,
v_count_after, v_err_msg);

END;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER

COST 100;

ALTER FUNCTION create_catexp_ss_master()

OWNER TO postgres;

Regards,
SS

Regards

Pavel Stehule

Charles Clavadetscher

clavadetscher@swisspug.org

over 10 years ago

In reply to: Sachin Srivastava (#1)

Re: Function error

Hello

If I understand you correctly you have two functions create_catexp_ss_1 and create_catexp_ss_2 that you then call from create_catexp_master.
If so then you probably need to change the call to them:

-- Exposure for single supplier without category filtering
create_catexp_ss_1;

-- Exposure for single supplier with category filtering
create_catexp_ss_2;

Should be:

PERFORM create_catexp_ss_1();
PERFORM create_catexp_ss_2();

If necessary with according parameters and assuming that you have no values returned that you need.

Bye
Charles

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sachin Srivastava
Sent: Freitag, 8. Januar 2016 08:24
To: pgsql-general@postgresql.org
Subject: [GENERAL] Function error

Dear Concern,

I am creating below function “create_catexp_ss_master()” and getting error as below, I have already created
dependent function firstly successfully (“create_catexp_ss_1” and “create_catexp_ss_2”) but still getting error,
please suggest why?

ERROR: syntax error at or near "create_catexp_ss_1"

LINE 38: create_catexp_ss_1;

^

********** Error **********

ERROR: syntax error at or near "create_catexp_ss_1"

SQL state: 42601

Character: 1104

-- Function: create_catexp_ss_master()

-- DROP FUNCTION create_catexp_ss_master();

CREATE OR REPLACE FUNCTION create_catexp_ss_master()

RETURNS void AS

$BODY$

DECLARE

-- Build snapshot tables for catalog itme exposure.

-- Versions:

-- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13

-- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen and from the Code. 05/23/13

--

v_count_before bigint;

v_count_after bigint;

v_start_time timestamp;

v_err_msg varchar(1000);

v_set_name varchar(10);

BEGIN

v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;

SELECT LOCALTIMESTAMP INTO v_start_time ;

if v_set_name='A' then

SELECT count(1) INTO v_count_before FROM pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_before FROM pcat_exp_supp_buyer_ss_b;

end if;

-- Remove old data.

EXECUTE 'truncate table pcat_exp_supp_buyer_ss_'||v_set_name;

EXECUTE 'truncate table pcat_exp_supp_cat_buyer_ss_'||v_set_name;

-- Exposure for single supplier without category filtering

create_catexp_ss_1;

-- Exposure for single supplier with category filtering

create_catexp_ss_2;

if v_set_name='A' then

SELECT count(1) INTO v_count_after FROM pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_after FROM pcat_exp_supp_buyer_ss_b;

end if;

-- Log

create_ss_log('Catalog Exposure', v_start_time, 'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, null);

exception -- log error

when others then

v_err_msg := SQLERRM;

create_ss_log('Catalog Exposure - Error', v_start_time, 'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, v_err_msg);

END;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER

COST 100;

ALTER FUNCTION create_catexp_ss_master()

OWNER TO postgres;

Regards,
SS

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sachin Srivastava

ssr.teleatlas@gmail.com

over 10 years ago

In reply to: Pavel Stehule (#2)

Re: Function error

Thanks Pavel !!!

On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Show quoted text

Hi

2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Dear Concern,

I am creating below function *“create_catexp_ss_master()” *and getting
error as below, I have already created dependent function firstly
successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but still
getting error, please suggest why?

ERROR: syntax error at or near "create_catexp_ss_1"

LINE 38: create_catexp_ss_1;

^

********** Error **********

ERROR: syntax error at or near "create_catexp_ss_1"

SQL state: 42601

Character: 1104

-- Function: create_catexp_ss_master()

-- DROP FUNCTION create_catexp_ss_master();

CREATE OR REPLACE FUNCTION create_catexp_ss_master()

RETURNS void AS

$BODY$

DECLARE

-- Build snapshot tables for catalog itme exposure.

-- Versions:

-- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13

-- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
and from the Code. 05/23/13

--

v_count_before bigint;

v_count_after bigint;

v_start_time timestamp;

v_err_msg varchar(1000);

v_set_name varchar(10);

BEGIN

v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;

SELECT LOCALTIMESTAMP INTO v_start_time ;

if v_set_name='A' then

SELECT count(1) INTO v_count_before FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_before FROM
pcat_exp_supp_buyer_ss_b;

end if;

-- Remove old data.

EXECUTE 'truncate table
pcat_exp_supp_buyer_ss_'||v_set_name;

EXECUTE 'truncate table
pcat_exp_supp_cat_buyer_ss_'||v_set_name;

Attention - this is potentially serious security bug

EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
v_set_name);

-- Exposure for single supplier without category
filtering

create_catexp_ss_1;

you have to call this function via PERFORM statement

PERFORM create_catexp_ss_1();

-- Exposure for single supplier with category filtering

create_catexp_ss_2;

if v_set_name='A' then

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_b;

end if;

-- Log

create_ss_log('Catalog Exposure', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, null);

exception -- log error

when others then

v_err_msg := SQLERRM;

create_ss_log('Catalog Exposure - Error', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before,
v_count_after, v_err_msg);

END;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER

COST 100;

ALTER FUNCTION create_catexp_ss_master()

OWNER TO postgres;

Regards,
SS

Regards

Pavel Stehule

Sachin Srivastava

ssr.teleatlas@gmail.com

over 10 years ago

In reply to: Sachin Srivastava (#4)

Re: Function error

Thanks Charles !!!

On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava <ssr.teleatlas@gmail.com>
wrote:

Show quoted text

Thanks Pavel !!!

On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Dear Concern,

I am creating below function *“create_catexp_ss_master()” *and getting
error as below, I have already created dependent function firstly
successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but
still getting error, please suggest why?

ERROR: syntax error at or near "create_catexp_ss_1"

LINE 38: create_catexp_ss_1;

^

********** Error **********

ERROR: syntax error at or near "create_catexp_ss_1"

SQL state: 42601

Character: 1104

-- Function: create_catexp_ss_master()

-- DROP FUNCTION create_catexp_ss_master();

CREATE OR REPLACE FUNCTION create_catexp_ss_master()

RETURNS void AS

$BODY$

DECLARE

-- Build snapshot tables for catalog itme exposure.

-- Versions:

-- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13

-- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
and from the Code. 05/23/13

--

v_count_before bigint;

v_count_after bigint;

v_start_time timestamp;

v_err_msg varchar(1000);

v_set_name varchar(10);

BEGIN

v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;

SELECT LOCALTIMESTAMP INTO v_start_time ;

if v_set_name='A' then

SELECT count(1) INTO v_count_before
FROM pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_before
FROM pcat_exp_supp_buyer_ss_b;

end if;

-- Remove old data.

EXECUTE 'truncate table
pcat_exp_supp_buyer_ss_'||v_set_name;

EXECUTE 'truncate table
pcat_exp_supp_cat_buyer_ss_'||v_set_name;

Attention - this is potentially serious security bug

EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
v_set_name);

-- Exposure for single supplier without category
filtering

create_catexp_ss_1;

you have to call this function via PERFORM statement

PERFORM create_catexp_ss_1();

-- Exposure for single supplier with category filtering

create_catexp_ss_2;

if v_set_name='A' then

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_b;

end if;

-- Log

create_ss_log('Catalog Exposure', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, null);

exception -- log error

when others then

v_err_msg := SQLERRM;

create_ss_log('Catalog Exposure - Error', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before,
v_count_after, v_err_msg);

END;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER

COST 100;

ALTER FUNCTION create_catexp_ss_master()

OWNER TO postgres;

Regards,
SS

Regards

Pavel Stehule

Sachin Srivastava

ssr.teleatlas@gmail.com

over 10 years ago

In reply to: Sachin Srivastava (#5)

Re: Function error

Hi,

Also there is any command to see the invalid and valid function in postgres
database.

Regards,
SS

On Fri, Jan 8, 2016 at 1:18 PM, Sachin Srivastava <ssr.teleatlas@gmail.com>
wrote:

Show quoted text

Thanks Charles !!!

On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava <ssr.teleatlas@gmail.com

wrote:

Thanks Pavel !!!

On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Dear Concern,

I am creating below function *“create_catexp_ss_master()” *and getting
error as below, I have already created dependent function firstly
successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but
still getting error, please suggest why?

ERROR: syntax error at or near "create_catexp_ss_1"

LINE 38: create_catexp_ss_1;

^

********** Error **********

ERROR: syntax error at or near "create_catexp_ss_1"

SQL state: 42601

Character: 1104

-- Function: create_catexp_ss_master()

-- DROP FUNCTION create_catexp_ss_master();

CREATE OR REPLACE FUNCTION create_catexp_ss_master()

RETURNS void AS

$BODY$

DECLARE

-- Build snapshot tables for catalog itme exposure.

-- Versions:

-- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13

-- 2013.02 hxu T11899 - Remove the Single Supplier check box from
Screen and from the Code. 05/23/13

--

v_count_before bigint;

v_count_after bigint;

v_start_time timestamp;

v_err_msg varchar(1000);

v_set_name varchar(10);

BEGIN

v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;

SELECT LOCALTIMESTAMP INTO v_start_time ;

if v_set_name='A' then

SELECT count(1) INTO v_count_before
FROM pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_before
FROM pcat_exp_supp_buyer_ss_b;

end if;

-- Remove old data.

EXECUTE 'truncate table
pcat_exp_supp_buyer_ss_'||v_set_name;

EXECUTE 'truncate table
pcat_exp_supp_cat_buyer_ss_'||v_set_name;

Attention - this is potentially serious security bug

EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
v_set_name);

-- Exposure for single supplier without category
filtering

create_catexp_ss_1;

you have to call this function via PERFORM statement

PERFORM create_catexp_ss_1();

-- Exposure for single supplier with category filtering

create_catexp_ss_2;

if v_set_name='A' then

SELECT count(1) INTO v_count_after
FROM pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_after
FROM pcat_exp_supp_buyer_ss_b;

end if;

-- Log

create_ss_log('Catalog Exposure', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, null);

exception -- log error

when others then

v_err_msg := SQLERRM;

create_ss_log('Catalog Exposure - Error',
v_start_time, 'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before,
v_count_after, v_err_msg);

END;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER

COST 100;

ALTER FUNCTION create_catexp_ss_master()

OWNER TO postgres;

Regards,
SS

Regards

Pavel Stehule

Pavel Stehule

pavel.stehule@gmail.com

over 10 years ago

In reply to: Sachin Srivastava (#6)

Re: Function error

2016-01-08 8:59 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Hi,

Also there is any command to see the invalid and valid function in
postgres database.

No, Postgres is not a Oracle. All functions in database are valid. But it
means some different than in Oracle. That's "all embedded SQL are
syntactically valid". If you need semantic validation, you should to use
plpgsql_check. https://github.com/okbob/plpgsql_check/ .

Regards

Pavel

Show quoted text

Regards,
SS

On Fri, Jan 8, 2016 at 1:18 PM, Sachin Srivastava <ssr.teleatlas@gmail.com

wrote:

Thanks Charles !!!

On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava <
ssr.teleatlas@gmail.com> wrote:

Thanks Pavel !!!

On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Dear Concern,

I am creating below function *“create_catexp_ss_master()” *and
getting error as below, I have already created dependent function firstly
successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but
still getting error, please suggest why?

ERROR: syntax error at or near "create_catexp_ss_1"

LINE 38: create_catexp_ss_1;

^

********** Error **********

ERROR: syntax error at or near "create_catexp_ss_1"

SQL state: 42601

Character: 1104

-- Function: create_catexp_ss_master()

-- DROP FUNCTION create_catexp_ss_master();

CREATE OR REPLACE FUNCTION create_catexp_ss_master()

RETURNS void AS

$BODY$

DECLARE

-- Build snapshot tables for catalog itme exposure.

-- Versions:

-- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13

-- 2013.02 hxu T11899 - Remove the Single Supplier check box from
Screen and from the Code. 05/23/13

--

v_count_before bigint;

v_count_after bigint;

v_start_time timestamp;

v_err_msg varchar(1000);

v_set_name varchar(10);

BEGIN

v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;

SELECT LOCALTIMESTAMP INTO v_start_time ;

if v_set_name='A' then

SELECT count(1) INTO v_count_before
FROM pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_before
FROM pcat_exp_supp_buyer_ss_b;

end if;

-- Remove old data.

EXECUTE 'truncate table
pcat_exp_supp_buyer_ss_'||v_set_name;

EXECUTE 'truncate table
pcat_exp_supp_cat_buyer_ss_'||v_set_name;

Attention - this is potentially serious security bug

EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
v_set_name);

-- Exposure for single supplier without category
filtering

create_catexp_ss_1;

you have to call this function via PERFORM statement

PERFORM create_catexp_ss_1();

-- Exposure for single supplier with category
filtering

create_catexp_ss_2;

if v_set_name='A' then

SELECT count(1) INTO v_count_after
FROM pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_after
FROM pcat_exp_supp_buyer_ss_b;

end if;

-- Log

create_ss_log('Catalog Exposure', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, null);

exception -- log error

when others then

v_err_msg := SQLERRM;

create_ss_log('Catalog Exposure - Error',
v_start_time, 'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before,
v_count_after, v_err_msg);

END;

$BODY$

LANGUAGE plpgsql VOLATILE SECURITY DEFINER

COST 100;

ALTER FUNCTION create_catexp_ss_master()

OWNER TO postgres;

Regards,
SS

Regards

Pavel Stehule

Sachin Srivastava

ssr.teleatlas@gmail.com

over 10 years ago

In reply to: Pavel Stehule (#7)

Re: Function error

Hi,

I am also getting "syntax error for below function (just some lines of
function where I am getting syntax error), please suggest why?
--------------------

-- Image path
AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.company_id(+)=pcat_catalog_item.company_id
AND
pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
AND
pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
AND
pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

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

ERROR: syntax error at or near ")"
LINE 216: AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS

Pavel Stehule

pavel.stehule@gmail.com

over 10 years ago

In reply to: Sachin Srivastava (#8)

Re: Function error

2016-01-08 10:08 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Hi,

I am also getting "syntax error for below function (just some lines of
function where I am getting syntax error), please suggest why?

Using Oracle's outer join syntax, not ANSI SQL syntax

Regards

Pavel

Show quoted text

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

-- Image path
AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.company_id(+)=pcat_catalog_item.company_id
AND
pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
AND
pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
AND
pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

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

ERROR: syntax error at or near ")"
LINE 216: AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS

#10

Sachin Srivastava

ssr.teleatlas@gmail.com

over 10 years ago

In reply to: Pavel Stehule (#9)

Re: Function error

Because I have migrated the database from Oracle to Postgres through ORA2PG.

So how I will change it, please suggest.

On Fri, Jan 8, 2016 at 2:54 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Show quoted text

2016-01-08 10:08 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Hi,

I am also getting "syntax error for below function (just some lines of
function where I am getting syntax error), please suggest why?

Using Oracle's outer join syntax, not ANSI SQL syntax

Regards

Pavel

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

-- Image path
AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.company_id(+)=pcat_catalog_item.company_id
AND
pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
AND
pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
AND
pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

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

ERROR: syntax error at or near ")"
LINE 216: AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS

#11

Pavel Stehule

pavel.stehule@gmail.com

over 10 years ago

In reply to: Sachin Srivastava (#10)

Re: Function error

2016-01-08 10:52 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Because I have migrated the database from Oracle to Postgres through
ORA2PG.

So how I will change it, please suggest.

http://stackoverflow.com/questions/2269156/converting-from-oracle-join-to-postgres-join

Show quoted text

On Fri, Jan 8, 2016 at 2:54 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2016-01-08 10:08 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Hi,

I am also getting "syntax error for below function (just some lines of
function where I am getting syntax error), please suggest why?

Using Oracle's outer join syntax, not ANSI SQL syntax

Regards

Pavel

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

-- Image path
AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.company_id(+)=pcat_catalog_item.company_id
AND
pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
AND
pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
AND
pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

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

ERROR: syntax error at or near ")"
LINE 216: AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS

#12

Sachin Srivastava

ssr.teleatlas@gmail.com

over 10 years ago

In reply to: Pavel Stehule (#11)

Re: Function error

Thanks Pavel for your help !!!

On Fri, Jan 8, 2016 at 3:28 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Show quoted text

2016-01-08 10:52 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Because I have migrated the database from Oracle to Postgres through
ORA2PG.

So how I will change it, please suggest.

http://stackoverflow.com/questions/2269156/converting-from-oracle-join-to-postgres-join

On Fri, Jan 8, 2016 at 2:54 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2016-01-08 10:08 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Hi,

I am also getting "syntax error for below function (just some lines of
function where I am getting syntax error), please suggest why?

Using Oracle's outer join syntax, not ANSI SQL syntax

Regards

Pavel

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

-- Image path
AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
AND cs1.company_id(+)=pcat_catalog_item.company_id
AND
pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
AND
pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
AND
pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

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

ERROR: syntax error at or near ")"
LINE 216: AND
mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS