cannot use duplicate table name ct_fact_data

[Former Member]

Hi team,


I'm trying to run

'CALL "MY_FC10_PROD"."FC_CREATE_FACT_DATA"('ACTUAL', '2017.01', 'MYCORP', '%', 'EUR', 0)' and got this issue :

Could not execute 'CALL "MY_FC10_PROD"."FC_CREATE_FACT_DATA"('ACTUAL', '2017.01', 'MYCORP', '%', 'EUR', 0)' in 6.302 seconds .
[129]: transaction rolled back by an internal error: [129] "MY_FC10_PROD"."FC_CREATE_FACT_DATA": line 185 col 3 (at pos 5846): [129] (range 3): transaction rolled back by an internal error: cannot use duplicate table name: CT_FACT_DATA: line 1 col 56 (at pos 55)

Could you help ?
Thanks,

[Marc_Kuipers]

Hi Vivian

The Stored Procedure is throwing this error. Can you check the mentioned line in the code (you can see this SP in Hana Studio)

At what point do you get this error, when running a consolidation in FC ?

Thanks

Marc

[Former Member]

Code lines are near the end when do "EXECUTE IMMEDIATE sQuery;"  : 

	ELSE
		sQuery := 'RENAME TABLE ' || sWorkTable || ' TO ' || :sTBL;
		EXECUTE IMMEDIATE sQuery;
	END IF;



All procedure here : 



CREATE PROCEDURE FC_CREATE_FACT_DATA
(
	IN sCA NVARCHAR(12) 	DEFAULT '%',
	IN sDP NVARCHAR(12) 	DEFAULT '%',
	IN sSC NVARCHAR(12) 	DEFAULT '%',
	IN sVE NVARCHAR(12) 	DEFAULT '%',
	IN sCC NVARCHAR(12) 	DEFAULT '%',
	IN nMode INT 			DEFAULT 0
)
LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS
BEGIN
	DECLARE nCount 		INT := 0;
	DECLARE nIndex1 	INT := 0;
	DECLARE nIndex2 	INT := 0;
	DECLARE nBound1 	INT := 0;
	DECLARE nBound2 	INT := 0;
	DECLARE sWorkTable  NVARCHAR(1024) := '';
	DECLARE sColumn     NVARCHAR(1024) := '';
	DECLARE sColumns    NVARCHAR(1024) := '';
	DECLARE sSQLStr1    NVARCHAR(1024) := '';
	DECLARE sSQLStr2    NVARCHAR(1024) := '';
	DECLARE sQuery      NCLOB := '';
	DECLARE sTBL        NVARCHAR(128) := 'CT_FACT_DATA';
	
	IF nMode = 2 THEN
		SELECT COUNT(table_name) INTO nCount FROM tables WHERE table_name = UPPER(:sTBL) AND schema_name = CURRENT_SCHEMA;
		IF nCount > 0 THEN
			EXECUTE IMMEDIATE 'DROP TABLE ' || :sTBL;
		END IF;
		DELETE FROM ct_fact_data_copy_log;
	END IF;
	 
	tbl_DetDimension = 
		SELECT
			ROW_NUMBER() OVER(ORDER BY ct_order) AS row_id,
			UPPER(phys_name) AS phys_name
		FROM
			ct_datasource_dim
		WHERE
			datasource = -524285 AND
			optional <> 0 AND
			dimension NOT IN (
				-524279,	
				-524278,	
				-524234,	
				-524226)	
			ORDER BY ct_order;


	tbl_Period =
		SELECT DISTINCT
			updper AS id,
			TO_NVARCHAR(1900 + ROUND(updper/262144))||'.'||LPAD(TO_NVARCHAR(TO_INT(MOD(updper, 262144)/8192)), 2, 0) AS name
		FROM
			ct_coref;


	tbl_ConsoRef =
		SELECT
			ROW_NUMBER() OVER(ORDER BY T01.phase, T01.updper) AS row_id,
			T01.phase,
			T01.updper,
			T01.scope,
			T01.variant,
			T01.curncy,
			'CT_CO' || CASE
				WHEN T01.id <= 999 THEN LPAD(TO_NVARCHAR(T01.id), 4,'0')
				ELSE TO_NVARCHAR(T01.id)
				END AS table_name,
			T02.task_date
		FROM
			ct_coref T01
			INNER JOIN ct_conso_def T02 ON
				T01.phase = T02.phase AND
				T01.updper = T02.updper AND
				T01.variant = T02.variante AND
				T01.curncy = T02.curncy
			INNER JOIN ct_conso_def_per T03 ON
				T01.scope = T03.scope_code AND
				T02.main_period = T03.id
			LEFT OUTER JOIN ct_fact_data_copy_log T04 ON
				T01.phase = T04.phase AND
				T01.updper = T04.updper AND
				T01.scope = T04.scope AND
				T01.variant = T04.variant AND
				T01.curncy = T04.curncy
		WHERE
			T01.phase IN (SELECT id FROM ct_phase WHERE name LIKE sCA) AND
			T01.updper IN (SELECT id FROM :tbl_Period WHERE name LIKE sDP) AND
			T01.scope IN (SELECT id FROM ct_scope_code WHERE name LIKE sSC) AND
			T01.variant IN (SELECT id FROM ct_variant WHERE name LIKE sVE) AND
			T01.curncy IN (SELECT id FROM ct_curncy WHERE name LIKE sCC) AND
			(T02.task_date > T04.copy_date OR T04.copy_date IS NULL);


	SELECT COUNT(row_id) INTO nCount FROM :tbl_ConsoRef;
	IF nCount = 0 THEN
		RETURN;
	END IF;


	SELECT COUNT(row_id) INTO nBound1 FROM :tbl_DetDimension;
	FOR nIndex1 IN 1..nBound1 DO
		SELECT sColumns || phys_name || '#' INTO sColumns FROM :tbl_DetDimension WHERE row_id = nIndex1;
	END FOR;


	sWorkTable := 'TFACT_' || SYSUUID;
	sQuery :=
		'CREATE COLUMN TABLE [1] ('||
		'PHASE INT, UPDPER INT, SCOPE INT, VARIANT INT, CONSCUR INT, PERIOD INT, ENTITY INT, ENTORIG INT, ACCNT INT, FLOW INT, NATURE INT, CURNCY INT, '||
		'PARTNER INT, CTSHARE INT, [2] AMOUNT DOUBLE, CONVAMOUNT DOUBLE, CONSAMOUNT DOUBLE) PARTITION BY HASH (UPDPER, SCOPE, VARIANT) PARTITIONS 120';
	sQuery :=
		REPLACE(REPLACE(sQuery,
			'[1]', sWorkTable),
			'[2]', REPLACE(sColumns, '#', ' INT, '));
	EXECUTE IMMEDIATE sQuery;


	SELECT COUNT(row_id) INTO nBound1 FROM :tbl_ConsoRef;
	FOR nIndex1 IN 1..nBound1 DO
		tbl_Column =
			SELECT
				ROW_NUMBER() OVER() AS row_id,
				T01.column_name
			FROM
				table_columns T01
				INNER JOIN :tbl_DetDimension T02 ON
					T01.column_name = T02.phys_name
				INNER JOIN :tbl_ConsoRef T03 ON
					T01.table_name = T03.table_name
			WHERE
				T03.row_id = nIndex1 AND
				T01.schema_name = CURRENT_SCHEMA;


		sQuery :=
			'INSERT INTO [1] ' ||
			'SELECT [2], PERIOD, ENTITY, ENTORIG, ACCNT, FLOW, NATURE, CURNCY, PARTNER, CTSHARE [3], SUM(AMOUNT), SUM(CONVAMOUNT), SUM(CONSAMOUNT) ' ||
			'FROM [4] GROUP BY PERIOD, ENTITY, ENTORIG, ACCNT, FLOW, CURNCY, NATURE, PARTNER, CTSHARE [5]';


		sSQLStr1 := '';
		sSQLStr2 := '';
		SELECT COUNT(row_id) INTO nBound2 FROM :tbl_DetDimension;
		FOR nIndex2 IN 1..nBound2 DO


			SELECT phys_name INTO sColumn FROM :tbl_DetDimension WHERE row_id = nIndex2;
			SELECT COUNT(row_id) INTO nCount FROM :tbl_Column WHERE column_name = sColumn;
			IF nCount = 0 THEN
				sSQLStr1 := sSQLStr1 || ', 0';
			ELSE
				sSQLStr1 := sSQLStr1 || ', ' || sColumn;
				sSQLStr2 := sSQLStr2 || ', ' || sColumn;
			END IF;
		END FOR;


		BEGIN
			DECLARE nPhase, nUpdper, nScope, nVariant, nConscur INT;
			DECLARE sTable NVARCHAR(128);
			
			SELECT phase, updper, scope, variant, curncy, table_name INTO nPhase, nUpdper, nScope, nVariant, nConscur, sTable
				FROM :tbl_ConsoRef
				WHERE row_id = nIndex1;


			sQuery := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(sQuery,
				'[1]', sWorkTable),
				'[2]', TO_NVARCHAR(nPhase)||', '||TO_NVARCHAR(nUpdper)||', '||TO_NVARCHAR(nScope)||', '||TO_NVARCHAR(nVariant)||', '||TO_NVARCHAR(nConscur)),
				'[3]', sSQLStr1),
				'[4]', sTable),
				'[5]', sSQLStr2);
				
			EXECUTE IMMEDIATE sQuery;
		END;
	END FOR;


	SELECT COUNT(table_name) INTO nCount FROM tables WHERE table_name = UPPER(:sTBL) AND schema_name = CURRENT_SCHEMA;


	IF :nCount > 0 THEN
		sQuery := 'DELETE FROM '|| :sTBL ||' WHERE (phase, updper, scope, variant, conscur) IN (SELECT DISTINCT phase, updper, scope, variant, conscur FROM '|| sWorkTable ||')';
		EXECUTE IMMEDIATE sQuery;


		sQuery := 'INSERT INTO '|| :sTBL || ' SELECT * FROM ' || sWorkTable;
		EXECUTE IMMEDIATE sQuery;
		
		sQuery := 'DROP TABLE ' || sWorkTable;
		EXECUTE IMMEDIATE sQuery;
	
	ELSE
		sQuery := 'RENAME TABLE ' || sWorkTable || ' TO ' || :sTBL;
		EXECUTE IMMEDIATE sQuery;
	END IF;


	DELETE FROM ct_fact_data_copy_log
	WHERE
		(phase, updper, scope, variant, curncy) IN
			(SELECT phase, updper, scope, variant, curncy FROM :tbl_ConsoRef);
	INSERT INTO ct_fact_data_copy_log (phase, updper, scope, variant, curncy, copy_date)
	SELECT
		phase,
		updper,
		scope,
		variant,
		curncy,
		CURRENT_UTCTIMESTAMP
	FROM
		:tbl_ConsoRef;


END;

[Former Member]

And no. I didn't launch procedure during running a consolidation in FC.

FC is in an other side. I launch directly on Hana Studio the procedure

[Former Member · Accepted solution]

Hi Marc, hi all

You right, in fact is even if I write CALL "MY_FC10_PROD"."FC_CREATE_FACT_DATA"('ACTUAL', '2017.01', 'MYCORP', '%', 'EUR', 0)

the schema used wasn't MY_FC10_PROD, but SYSTEM. As there was no table in SYSTEM schema, the condition go to the "else" way and crash.

Solution is to set system before launching proc :

SET SCHEMA MY_FC10_PROD;

CALL "MY_FC10_PROD"."FC_CREATE_FACT_DATA"('ACTUAL', '2017.01', 'MYCORP', '%', 'EUR', 0);

Thanks you very much for your help Marc !

[e_malan · Accepted solution]

Hi Vivian

Your'e right.

The "FC_CREATE_FACT_DATA" was designed to be called form an SQL rule within the the SAP FC Schema / DB user.

You can also customize the FC_CREATE_FACT_DATA to use a particular schema, or reloslv ethe correct schema form the launching command, or use a differtent table name for the fact table.

Etienne

[Marc_Kuipers]

Hi Vivian

Strange..

The sTBL is set to 'CT_FACT_DATA'

DECLARE sTBL NVARCHAR(128) := 'CT_FACT_DATA';

Then, this statement is issued

SELECT COUNT(table_name) INTO nCount FROM tables WHERE table_name = UPPER(:sTBL) AND schema_name = CURRENT_SCHEMA;

Because the code executes the "else" clause, it means nCount has a value of 0

But that implies CT_FACT_DATA does not exist (and therefore the rename can be done), but the error shows that CT_FACT_DATA does exists, as you cannot create a duplicate

Can you check in CURRENT_SCHEMA if the table is indeed present?

Marc

[Former Member]

Yes, the CT_FACT_DATA table exists in MY_FC10_PROD schema.

But in the documentation https://help.sap.com/doc/ee41e3f574631014a7da368fb0e91070/10.1.5/en-US/fc_101_admin_en.pdf tell that when we use 0 as last parameter, the procedure should do an update. So quite weird if error message ask for CT_FACT_DATA not exist...