CSV Export with Filter

The following procedure provides the opportunity to filter the resultset before the actual CSV export.

CREATE VIRTUAL PROCEDURE views.csvExport
    (
        IN sourceSchema string NOT NULL OPTIONS (ANNOTATION 'The source schema in datavirtuality.')
        , IN sourceTable string  NOT NULL OPTIONS (ANNOTATION 'The source table in datavirtuality.')
        , IN targetSchema string NOT NULL OPTIONS (ANNOTATION 'A file datasource in datavirtuality. The file will be stored in the directory assigned to the file datasource.')
        , IN targetFile string OPTIONS (ANNOTATION 'The name of the file to store the exported data. If a file with same name exists, it will be overwritten. If omitted, name will be created: sourceSchema_sourceTable.csv.')
        , IN delimiter string OPTIONS (ANNOTATION 'When DELIMITER is not specified, by default comma(,) is used as delimiter.')
        , IN quote string OPTIONS (ANNOTATION 'Double quotes(") is the default quote character. Use QUOTE to specify a different value. All values will be quoted.')
        , IN header boolean OPTIONS (ANNOTATION 'If HEADER is specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group.')
        , IN orderBy string OPTIONS (ANNOTATION 'An ORDER BY-clause may be used to sort the output.')
        , IN encoding string OPTIONS (ANNOTATION 'Encoding for the created file. Default is the systems default encoding.')
        , IN filteringExpression string OPTIONS (ANNOTATION 'Expression which should be used in WHERE clause to filter results in the source view or table.')
    )
    OPTIONS (ANNOTATION 'Exports content of a table into a textfile.')
    AS
    BEGIN
        DECLARE string variables.source_schema = LCASE(REPLACE(sourceSchema, '"', ''));
        DECLARE string variables.source_table = LCASE(REPLACE(sourceTable, '"', ''));
        DECLARE string variables.target_schema = LCASE(REPLACE(targetSchema, '"', ''));
        DECLARE string variables.target_file = targetFile;
        DECLARE boolean variables.header = COALESCE(header,FALSE);
        DECLARE string variables.order_by = LCASE(REPLACE(orderBy, '"', ''));
        DECLARE string variables.delimiter = delimiter;
        DECLARE string variables.quote = quote;
        DECLARE string variables.encoding = encoding;
        DECLARE string variables.columns = '';
        DECLARE string variables.export_command = '';
        
        IF 
            ( 
                ( 
                    SELECT a."Name" 
                    FROM "SYS.Schemas" a 
                    WHERE LCASE(a."Name") = variables.source_schema 
                ) 
                IS NULL 
            ) 
            BEGIN
                ERROR 'Schema ' || variables.source_schema || ' does not exist';
            END
        IF 
            (
                ( 
                    SELECT a."Name" 
                    FROM "SYS.Schemas" a 
                    WHERE LCASE(a."Name") = variables.target_schema 
                ) 
                IS NULL 
            ) 
            BEGIN
                ERROR 'Schema ' || variables.target_schema || ' does not exist';
            END
 
        IF 
            ( 
                ( 
                    SELECT a."Name" 
                    FROM "SYS.Tables" a 
                    WHERE 
                        LCASE(a."Name") = variables.source_table 
                        AND LCASE(a."schemaName") = variables.source_schema 
                ) 
                IS NULL
            ) 
            BEGIN
                ERROR 'Table ' || variables.source_table || ' does not exist';
            END
            
        IF
            ( 
                variables.target_file IS NULL 
                OR variables.target_file = ''
            )
            BEGIN
                variables.target_file = 
                    (
                        variables.source_schema 
                        || '_' 
                        || variables.source_table 
                        || '.csv'
                    )
                ;
            END
       
        LOOP ON 
            (
                SELECT a."Name" 
                FROM "SYS"."Columns" a 
                WHERE 
                    LCASE(a."tableName") = variables.source_table
                    AND LCASE(a."schemaName") = variables.source_schema
                ORDER BY a.position
            ) AS cursor_column_names
            BEGIN            
                variables.columns = 
                    (
                        variables.columns 
                        || 'COALESCE("'
                        || cursor_column_names."Name" 
                        || '",'''') AS "'
                        || cursor_column_names."Name" 
                        || '",'
                    )
                ;                
            END          
        variables.columns = TRIM(TRAILING ',' FROM variables.columns); 
        variables.export_command = 
            'EXEC ' 
            || variables.target_schema
            || '.saveFile( ''' 
            || variables.target_file 
            || ''', ( SELECT TEXTAGG(' 
            || variables.columns
            || CASE WHEN variables.delimiter IS NOT NULL THEN ' DELIMITER ''' 
            || variables.delimiter 
            || '''' ELSE '' END 
            || CASE WHEN variables.quote IS NOT NULL THEN ' QUOTE ''' 
            || variables.quote 
            || '''' ELSE '' END 
            || CASE WHEN variables.header = TRUE THEN ' HEADER ' ELSE '' END  
            || CASE WHEN variables.encoding IS NOT NULL THEN ' ENCODING "' 
            || variables.encoding 
            || '"' ELSE '' END 
            || CASE WHEN variables.order_by IS NOT NULL THEN ' ORDER BY ' 
            || variables.order_by ELSE '' END 
            || ')' 
            || ' FROM "' 
            || variables.source_schema 
            || '"."' 
            || variables.source_table 
            || '"' 
            || CASE WHEN filteringExpression is null or rtrim(filteringExpression) = '' THEN '' ELSE ' WHERE ' || filteringExpression END
            || ') )'
        ;
            
        EXECUTE IMMEDIATE variables.export_command;
        
    END;

After creating the procedure you can run the export with the following call statement.

call "views.csvExport"(
    "sourceSchema" => 'string_sourceSchema',
    "sourceTable" => 'string_sourceTable',
    "targetSchema" => 'string_targetSchema',
    "targetFile" => 'string_targetFile',
    "delimiter" => 'string_delimiter',
    "quote" => 'string_quote',
    "header" => boolean_header,
    "orderBy" => 'string_orderBy',
    "encoding" => 'string_encoding',
    "filteringExpression" => 'string_filteringExpression'
);;

The filtering expression can be used like a where clause.

For example, if you have a column which is called year_c and you want all the data from 2017 it could look like this.

call "views.csvExport"(
    "sourceSchema" => 'string_sourceSchema',
    "sourceTable" => 'string_sourceTable',
    "targetSchema" => 'string_targetSchema',
    "targetFile" => 'test.csv',
    "delimiter" => ';',
    "quote" => '"',
    "header" => true,
    "encoding" => 'UTF-8',
    "filteringExpression" => 'year_c=2017'
);;