MSCEWI1010

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Critical

Description

This error appears when there is not a transformation rule for a specific procedure statement.

Example Code

Input Code:

REPLACE PROCEDURE   USERMANAGER.USERMAN_HANDLEPROFILES
(
         OUT MESSAGE CHAR(200)
)

BEGIN 
DECLARE REQUESTEDUSER CHAR(30);
DECLARE REQUESTEDPROFILE CHAR(30);
DECLARE MSG CHAR(200);

SELECT 'NOTHING PROCESSED' INTO :MESSAGE;

PROFILEHANDLELABEL: BEGIN
FOR V_STMT AS C_VAR CURSOR FOR
SELECT 
USERNAME AS A, 
PROFILENAME AS B 
FROM 
USERADMIN.EDWPROFILESHANDLING
WHERE 
RANKK=1
AND 
(USERNAME, PROFILENAME) NOT IN 
(
SELECT USERNAME, PROFILENAME
FROM 
USERADMIN.USERS_DBC
WHERE 
PROFILENAME IS NOT NULL 
)
  DO 
SET REQUESTEDUSER=V_STMT.A;
SET REQUESTEDPROFILE=V_STMT.B;
CALL DBC.SysExecSQL('MODIFY USER '||:REQUESTEDUSER||' AS SPOOL = 0 TEMPORARY = 0  DEFAULT DATABASE = '||:REQUESTEDUSER||' FALLBACK  NO BEFORE JOURNAL NO AFTER JOURNAL COLLATION = HOST DEFAULT CHARACTER SET LATIN DATEFORM=INTEGERDATE TIME ZONE=NULL PROFILE='||:REQUESTEDPROFILE||';');
SELECT 'PROFILE CHANGED TO:'||TRIM(:REQUESTEDPROFILE)||' FOR USER: '||:REQUESTEDUSER INTO :MSG;
CALL USERMANAGER.USERMAN_LOGHISTORY(NULL, 'PROFILE_HANDLE','PROFILE_ASSIGN',:MSG, 'SUCCESS');
SELECT 'ALL PROFILE CHANGES HANDLED;' INTO :MESSAGE;
END FOR;
END PROFILEHANDLELABEL;
END;

Output Code:

CREATE OR REPLACE PROCEDURE DATAWAREHOUSE.PUBLIC.USERMAN_HANDLEPROFILES ( MESSAGE CHAR(200))
   RETURNS STRING
   LANGUAGE JAVASCRIPT
   EXECUTE AS CALLER
   AS
   $$
 	var REQUESTEDUSER;
	var REQUESTEDPROFILE;
	var MSG;
	var sql_stmt = `
	SELECT 'NOTHING PROCESSED' INTO :MESSAGE`;
	snowflake.createStatement({
	   sqlText : sql_stmt
	}).execute();
	/*
Conversion issue - Severity High - Procedure - No transformation rule found for this FOR LOOP procedure statement
FOR V_STMT AS C_VAR CURSOR FOR
	SELECT
	USERNAME AS A,
	PROFILENAME AS B
	FROM DATAWAREHOUSE.PUBLIC.EDWPROFILESHANDLING
	WHERE
	RANKK=1
	AND
	(USERNAME, PROFILENAME) NOT IN
	(
	SELECT USERNAME, PROFILENAME
	FROM DATAWAREHOUSE.PUBLIC.USERS_DBC
	WHERE
	PROFILENAME IS NOT NULL
	)
	  DO	
	SET REQUESTEDUSER=V_STMT.A;
	SET REQUESTEDPROFILE=V_STMT.B;	
	CALL DBC.SysExecSQL('MODIFY USER '||:REQUESTEDUSER||' AS SPOOL = 0 TEMPORARY = 0  DEFAULT DATABASE = '||:REQUESTEDUSER||' FALLBACK  NO BEFORE JOURNAL NO AFTER JOURNAL COLLATION = HOST DEFAULT CHARACTER SET LATIN DATEFORM=INTEGERDATE TIME ZONE=NULL PROFILE='||:REQUESTEDPROFILE||';');
	SELECT 'PROFILE CHANGED TO:'||TRIM(:REQUESTEDPROFILE)||' FOR USER: '||:REQUESTEDUSER INTO :MSG;	
	CALL USERMANAGER.USERMAN_LOGHISTORY(NULL, 'PROFILE_HANDLE','PROFILE_ASSIGN',:MSG, 'SUCCESS');	
	SELECT 'ALL PROFILE CHANGES HANDLED;' INTO :MESSAGE;
	END FOR*/
	;
                        ;

Recommendations

• Check if the procedure statement is correct.

• If you need more support, you can email us at [email protected]