Skip to Content

cannot use duplicate table name ct_fact_data

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,

Add comment
10|10000 characters needed characters exceeded

  • 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;
    
  • 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

  • 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

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 22 at 08:49 AM

    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 !

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Jan 16 at 05:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded