SQL Snippet to Create Views with LEFT( "foo"."bar", 4000) AS "Name" for Long Strings e.g. Salesforce

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

Articles in this section

Comments