BEGIN
DECLARE integer num_col = 0;
DECLARE string use_schema = 'salesforce';
DECLARE string use_table = 'Account';
DECLARE string viewname = 'datavirtuality.SF_Account';
DECLARE clob statement = 'CREATE view "' || viewname || '" ' || unescape( '\n') || 'AS ' || unescape( '\n') || 'SELECT' || unescape( '\n') ;
LOOP ON ( select * from "SYS.Columns" where "schemaName" = use_schema and "TableName" = use_table ) as cur1
BEGIN
IF(num_col > 0 )
statement = statement || ',' || unescape( '\n');
IF ( cur1.DataType = 'string' )
statement = statement || unescape( '\t') ||'LEFT( "' || cur1.TableName || '"."' || cur1.Name ||'", 4000) AS ' || '"' || cur1.Name || '"';
ELSE
statement = statement || unescape( '\t') || '"' || cur1.TableName || '"."' || cur1.Name ||'"';
num_col = num_col + 1;
END
statement = unescape( '\n') || statement || unescape( '\n') || ' FROM "' || use_schema || '.' || use_table || '";';
select statement;
END ;;
Comments
0 comments
Please sign in to leave a comment.