ODBC Input Build Tags

Assigns a calendar and period type to the column. Valid calendars are:

• standard—Gregorian calendar; period type options are year, year-half, year-quarter, year-tertile, and year-month.
• offset <month>—Fiscal year similar to the standard calendar except starting on a different month; period type options are the same as standard calendars.
• iso 8601—ISO week-numbering system; period type options are year and year-week.
• custom—Custom calendar created from a text file with a contiguous range of dates; required columns are date and year, optional columns are half, quarter, tertile, month, and week.

See General Build Tags for a different use of calendar as a Build tag.

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.

Formats the "date" or "period" data for the cBase. To store dates like July 4, 2013, use format="MMMM D, YYYY".

Use to format currency.

• ODBC data source name (DSN) and query are required.
• ODBC user, password, column-override types, calendar, and format are optional.
• Zero or more column-overrides, properties, and custom-properties are allowed.
• A text or variable length field with an unspecified maximum length is capped at 32,767 characters.
• When run, key stages are reported: opening the DSN, running the query, reading the structure of the result, reading the data of the result, and constructing the output table.
• The query can be inline or stored in an external file.

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.