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*/
;
;
