## forClause ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR forClause_start((START)) forClause_start --> forClause_0_0[FOR]:::quoted forClause_0_0 --> forClause_0_1[forCsvClause] forClause_0_0 --> forClause_0_2[forExcelClause] forClause_0_0 --> forClause_0_3[forJsonClause] forClause_0_0 --> forClause_0_4[forXmlClause] forClause_0_1 --> forClause_end((END)) forClause_0_2 --> forClause_end((END)) forClause_0_3 --> forClause_end((END)) forClause_0_4 --> forClause_end((END)) ``` ## Purpose The 'for' clause replaces the results from a query by a single row or a few rows, single column embedding the full results in the respective format. ## forCsvClause ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD forCsvClause_start((START)) forCsvClause_start --> forCsvClause_0_0[CSV]:::quoted forCsvClause_0_0 --> forCsvClause_0_1[","]:::quoted forCsvClause_0_1 --> forCsvClause_0_2[INCLUDE]:::quoted forCsvClause_0_2 --> forCsvClause_0_3[TECHNICAL]:::quoted forCsvClause_0_3 --> forCsvClause_0_4[EXCLUDE]:::quoted forCsvClause_0_4 --> forCsvClause_0_5[HEADERS]:::quoted forCsvClause_0_5 --> forCsvClause_0_6[","]:::quoted forCsvClause_0_6 --> forCsvClause_0_7[ROW]:::quoted forCsvClause_0_7 --> forCsvClause_0_8[DELIMITER]:::quoted forCsvClause_0_8 --> forCsvClause_0_9[stringConstant] forCsvClause_0_9 --> forCsvClause_0_10[","]:::quoted forCsvClause_0_10 --> forCsvClause_0_11[COLUMN]:::quoted forCsvClause_0_11 --> forCsvClause_0_12[DELIMITER]:::quoted forCsvClause_0_12 --> forCsvClause_0_13[stringConstant] forCsvClause_0_13 --> forCsvClause_0_14[","]:::quoted forCsvClause_0_14 --> forCsvClause_0_15[QUOTING]:::quoted forCsvClause_0_15 --> forCsvClause_0_16[CHARACTER]:::quoted forCsvClause_0_16 --> forCsvClause_0_17[stringConstant] forCsvClause_0_17 --> forCsvClause_0_18[","]:::quoted forCsvClause_0_18 --> forCsvClause_0_19[ESCAPED]:::quoted forCsvClause_0_19 --> forCsvClause_0_20[QUOTING]:::quoted forCsvClause_0_20 --> forCsvClause_0_21[CHARACTER]:::quoted forCsvClause_0_21 --> forCsvClause_0_22[stringConstant] forCsvClause_0_22 --> forCsvClause_0_23[","]:::quoted forCsvClause_0_23 --> forCsvClause_0_24[OUTPUT]:::quoted forCsvClause_0_24 --> forCsvClause_0_25[PER]:::quoted forCsvClause_0_25 --> forCsvClause_0_26[numericConstant] forCsvClause_0_26 --> forCsvClause_0_27[ROW]:::quoted forCsvClause_0_27 --> forCsvClause_0_28[ROWS]:::quoted forCsvClause_0_28 --> forCsvClause_0_29[REPEAT]:::quoted forCsvClause_0_29 --> forCsvClause_0_30[HEADERS]:::quoted forCsvClause_0_30 --> forCsvClause_end((END)) ``` ## Purpose The 'for csv' clause replaces the results from a query by few rows and asingle column named 'CSV' embedding the full results in an CSV format. Depending on the data volume, multiple rows can be returned. By default, each output row contains at most 1.000 rows from the query. The actual number of rows from the query can be set using 'OUTPUT PER ... ROWS'. By default, the first output row starts with a technical header of the respective column names. The headers can be replaced by labels using 'INCLUDE HEADERS'. The headers can be fully disabled by adding 'EXCLUDE HEADERS'. The output of headers in each output row can be enabled using 'REPEAT HEADERS' with 'OUTPUT PER ... ROWS'. In that case, 'OUTPUT PER ... ROWS' must always be specified even with the default of 1.000 query rows per output row. The CSV-output can use other texts than CR/LF for row delimiter, ',' for column delimiter, '"' for quoting character and '\"' for escaped quoting character. These can be set using, respectively, 'ROW DELIMITER', 'COLUMN DELIMITER', 'QUOTING CHARACTER' and 'ESCAPED QUOTING CHARACTER'. ## forExcelClause ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD forExcelClause_start((START)) forExcelClause_start --> forExcelClause_0_0[EXCEL]:::quoted forExcelClause_0_0 --> forExcelClause_0_1[","]:::quoted forExcelClause_0_1 --> forExcelClause_0_2[INCLUDE]:::quoted forExcelClause_0_2 --> forExcelClause_0_3[TECHNICAL]:::quoted forExcelClause_0_3 --> forExcelClause_0_4[EXCLUDE]:::quoted forExcelClause_0_4 --> forExcelClause_0_5[HEADERS]:::quoted forExcelClause_0_5 --> forExcelClause_end((END)) ``` ## Purpose The 'for excel' clause replaces the results from a query by a single row, single column named 'Excel' embedding the full results in an Excel xlsx-file format. The Excel xlsx-file has an Excel worksheet with a name (by default 'resultset1') with the rows. By default, the first Excel worksheet starts with a header with all column names. No header is added when 'EXCLUDE HEADERS' is specified. Labels can be used as headers by specifying 'INCLUDE HEADERS'. ## forJsonClause ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD forJsonClause_start((START)) forJsonClause_start --> forJsonClause_0_0[JSON]:::quoted forJsonClause_0_0 --> forJsonClause_0_1[AUTO]:::quoted forJsonClause_0_1 --> forJsonClause_0_2[PATH]:::quoted forJsonClause_0_2 --> forJsonClause_0_3[","]:::quoted forJsonClause_0_3 --> forJsonClause_0_4[ROOT]:::quoted forJsonClause_0_4 --> forJsonClause_0_5["("] forJsonClause_0_5 --> forJsonClause_0_6[stringConstant] forJsonClause_0_6 --> forJsonClause_0_7[")"] forJsonClause_0_7 --> forJsonClause_0_8[","]:::quoted forJsonClause_0_8 --> forJsonClause_0_9[INCLUDE_NULL_VALUES] forJsonClause_0_9 --> forJsonClause_0_10[","]:::quoted forJsonClause_0_10 --> forJsonClause_0_11[WITHOUT_ARRAY_WRAPPER] forJsonClause_0_11 --> forJsonClause_0_12[","]:::quoted forJsonClause_0_12 --> forJsonClause_0_13[OUTPUT]:::quoted forJsonClause_0_13 --> forJsonClause_0_14[PER]:::quoted forJsonClause_0_14 --> forJsonClause_0_15[numericConstant] forJsonClause_0_15 --> forJsonClause_0_16[ROW]:::quoted forJsonClause_0_16 --> forJsonClause_0_17[ROWS]:::quoted forJsonClause_0_17 --> forJsonClause_end((END)) ``` ## Purpose The 'for json' clause replaces the results from a query by a single column named 'JSON' embedding the full results in a JSON format. Depending on the data volume, multiple rows can be returned. By default, each output row contains at most 1.000 rows from the query. The actual number of rows from the query can be set using 'OUTPUT PER ... ROWS'. One of the naming configurations is 'AUTO'; the rows return one JSON object, with column names and their values as, respectively, JSON property names and values. A period ('.') in a column name will not influence the generated JSON differently from any other character. Casing of the column names will be reflected in the JSON property names. Casing is upper-case by default in Invantive UniversalSQL. Escape column names using square brackets to accurately specify casing of JSON property names. As an alternative, the naming configuration can be switched to 'PATH'. With 'PATH', the JSON property names are identical to 'AUTO' except for a period ('.') in a column name. Each period in a column name introduces a new nesting level in the generated JSON. A wrapper can be specified using 'ROOT'. The default root JSON property name is 'root'. A deviating name can be specified as a text constant between parentheses. Properties are excluded for column null values. JSON properties are generated even for null values when 'INCLUDE_NULL_VALUES' is present. The JSON objects are combined into a JSON array unless 'WITHOUT_ARRAY_WRAPPER' is present. With 'WITHOUT_ARRAY_WRAPPER' present, the output becomes NDJSON, where each individual output row represent a single source row. ## forXmlClause ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD forXmlClause_start((START)) forXmlClause_start --> forXmlClause_0_0[XML]:::quoted forXmlClause_0_0 --> forXmlClause_0_1[RAW]:::quoted forXmlClause_0_1 --> forXmlClause_0_2["("]:::quoted forXmlClause_0_2 --> forXmlClause_0_3[stringConstant] forXmlClause_0_3 --> forXmlClause_0_4[")"]:::quoted forXmlClause_0_4 --> forXmlClause_0_5[AUTO]:::quoted forXmlClause_0_5 --> forXmlClause_0_6[forXmlClauseCommonDirectives] forXmlClause_0_6 --> forXmlClause_0_7[","]:::quoted forXmlClause_0_7 --> forXmlClause_0_8[XMLDATA]:::quoted forXmlClause_0_8 --> forXmlClause_0_9[XMLSCHEMA]:::quoted forXmlClause_0_9 --> forXmlClause_0_10[stringConstant] forXmlClause_0_10 --> forXmlClause_0_11[PATH]:::quoted forXmlClause_0_11 --> forXmlClause_0_12["("]:::quoted forXmlClause_0_12 --> forXmlClause_0_13[stringConstant] forXmlClause_0_13 --> forXmlClause_0_14[")"]:::quoted forXmlClause_0_14 --> forXmlClause_0_15[forXmlClauseCommonDirectives] forXmlClause_0_15 --> forXmlClause_0_16[","]:::quoted forXmlClause_0_16 --> forXmlClause_0_17[ELEMENTS]:::quoted forXmlClause_0_17 --> forXmlClause_0_18[XSINIL]:::quoted forXmlClause_0_18 --> forXmlClause_0_19[ABSENT]:::quoted forXmlClause_0_19 --> forXmlClause_0_20[EXPLICIT]:::quoted forXmlClause_0_20 --> forXmlClause_0_21[forXmlClauseCommonDirectives] forXmlClause_0_21 --> forXmlClause_end((END)) ``` ## Purpose The 'for xml' clause replaces the results from a query by a single row, single column named 'XML' embedding the full results in an XML format. ## forXmlClauseCommonDirectives ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD forXmlClauseCommonDirectives_start((START)) forXmlClauseCommonDirectives_start --> forXmlClauseCommonDirectives_0_0[","]:::quoted forXmlClauseCommonDirectives_0_0 --> forXmlClauseCommonDirectives_0_1[BINARY]:::quoted forXmlClauseCommonDirectives_0_1 --> forXmlClauseCommonDirectives_0_2[BASE_64]:::quoted forXmlClauseCommonDirectives_0_2 --> forXmlClauseCommonDirectives_0_3[","]:::quoted forXmlClauseCommonDirectives_0_3 --> forXmlClauseCommonDirectives_0_4[TYPE]:::quoted forXmlClauseCommonDirectives_0_4 --> forXmlClauseCommonDirectives_0_5[","]:::quoted forXmlClauseCommonDirectives_0_5 --> forXmlClauseCommonDirectives_0_6[ROOT]:::quoted forXmlClauseCommonDirectives_0_6 --> forXmlClauseCommonDirectives_0_7["("]:::quoted forXmlClauseCommonDirectives_0_7 --> forXmlClauseCommonDirectives_0_8[stringConstant] forXmlClauseCommonDirectives_0_8 --> forXmlClauseCommonDirectives_0_9[")"]:::quoted forXmlClauseCommonDirectives_0_9 --> forXmlClauseCommonDirectives_end((END)) ```