ODBC Input Build Tags

Binds the parameter value to the variable in the SQL query.

If the driver does not support describing parameters, Spectre ODBC reader hands off the parameters as strings (similar to Integrator).

TIP: If you know the SQL where clause is expecting a numeric, use Spectre's parse_integer() function to change each string data-type parameter value into an integer value . For example, change bind "$(INTNUMB)" to bind `parse_integer(param("INITNUMB"))`.

• gregorian month—Gregorian year-month
• gregorian quarter—Gregorian year-quarter
• iso8601 week—ISO-8601 year-week
• gregorian+<N> month—Offset Gregorian year-month for fiscal calendars. Specifying "gregorian+6 month" means "add six months to the real year-mo to get the fiscal year-mo", which is like saying "The fiscal year starts in July".
• <custom calendar file>—For more tailored fiscal calendars
• See Spectre Calendars.

As of Spectre 7.2, column-override is deprecated. Use column instead.

Specifies the columns for the cBase and overrides the indicated attributes. For example, override the default numeric type to string; specify that the number has 2 decimal places.

NOTE: Use of column-override causes the Spectre build engine to request the given field from the database using a specific type. If the database or its ODBC driver cannot perform that conversion, then an error is generated. Use of column-override does not cause the Spectre build engine to perform any conversions itself.

TIP: The column-override attribute is not intended to be used as a way to do data type casting. For example, in general, column-override does not work with date data in a DB. Either create an SQL query that converts the field, or, once read by the Build, replace the value using an explicit date() function.

Specifies, using one tag, a string with DSN, user, and password.

NOTE: Different ODBC drivers might use other connect string formats.

Defines the encoding used in the input and output files. Encoding types supported include: cp-1252, iso-8859-1, utf-16be, utf-16le, utf-8, and gb18030.

NOTE

• Best practice is to specify the encoding of the input data.
• For text inputs, if no encoding is specified, Spectre analyzes the first few megabytes of the file to attempt to determine the encoding.
• For databases, if no encoding is specified, Spectre queries the driver for unicode data; if encoding="utf8" is specified, Spectre accepts the data as-is from the driver; if encoding is specified as something else, Spectre transcodes the bytes from the driver.
• Spectre Build's informational output indicates which encoding is in use.
• Non-Unicode DI clients are Unicode-aware.

• When using the Easysoft ODBC-to-JDBC Bridge, specify encoding="utf-8" to work around a driver bug.

Directs the build to use a specific number of rows from each input. If multiple inputs are used, the first 100 rows are read from each, ensuring a complete sampling of data values.

TIP: Using a "LIMIT" statement or "TOP X" in your SQL query is the optimal way to limit the number of rows coming from the database.

Specifies the query into the database. The SQL query string takes multiple lines, so triple quotes are needed to open and close the string.

See also query-path. Use query or query-path, but not both.

Sorts the data when processing. When specifying columns, use sort-by to order them based on another column's value.

NOTE

• An alternate sort can only be applied to a string column, and the sort-by column must be a string or an integer column
• Dive and cPlan files have a different sort tag.
• Spectre uses "numeric collation" when sorting strings. When a subset of a string looks like a number, then the numeric value is considered during the sort. For example, Spectre sorts "a2" before "a10". See also sort-natural.
• Null strings sort to the end.
• If the alternate sort has duplicates, the fall back is the main column.

• If a row has the value null or the value unknown in the column with an alternate sort, then the sort-by column must also have the same value.

• If the sort-by column has type string AND has the value null or the value unknown, then the column to be sorted must have the same value.
• Spectre issues a warning if you attempt to alternate sort null as non-null, or unknown as not-unknown.

Defines a time threshold, in seconds, for a cache entry to turn stale. Use with odbc-input.

The example here will re-run the lookup at most once every minute so that people looking at the result see data at most one minute old.

The stale-after attribute can also be used with tunnel-input.

Sets the column's summary type explicitly. Any summary function that takes a single argument (except for count and percentile) are allowed. See Summary Functions.

Most common are sum, info, any, first, and last. The default for numeric data is sum; use kahan_sum if concerned about precision; use const for a column with only one value. The rest of the functions (for example, min, average, median) are better done as a separate calc (that is, not the default one).

Specifies the type of data in the column. Valid values are boolean, date, datetime, double, fixed100, integer, period, and string. See Spectre Data Types.

If the type is period, a calendar tag is required.

NOTE: Fields of type bit from SQL Server default to type string in Spectre. You can specify the type as boolean for better performance.