Support getting, comparing, grouping by type (i.e., ISTYPE, TYPEOF)

[jduo]

An application user might want to semantically group together variant values for a column that are of the same type.

The SAFECONVERT() function doesn't fully allow for this. For example, you might have a variant V1 for our first parameter, and want SQL_INTEGER values for it.

You can't use WHERE SAFECONVERT(V1, SQL_INTEGER) IS NOT NULL here, since SQL_SMALLINT, SQL_TINYINT, etc values could safely convert to SQL_INTEGERs.

What might make sense would be some dedicated functions for extracting specific types from variant columns.

Also, we should allow for SAFECONVERT() to be used in GROUP BY expressions.

[mikepizzo]

so, something like ISTYPE(V1,SQL_SMALLINT) or, more generally, ISTYPE(V1,My.Custom.Type)?

[mikepizzo]

Updated title to capture ask.

SAFECONVERT (as well as ISTYPE) should be able to be used anywhere an expression is legal (including GROUP BY). Or is the ask really to support TYPEOF(), which would return the (SQL_TYPE? Driver type?) of a value?

Given TYPEOF(), you could do ISTYPE().

Still the question of whether these take/return SQL types or driver types... Maybe need different versions for each?

[mikepizzo]

Proposal: Introduce a TYPEOF canonical function that returns the type name of a column. Consider also adding a canonical function to support returning the SQL_TYPE of a column.

[mikepizzo]

Added two new canonical functions:

3.3.7 TYPEOF

The TYPEOF function, which can be invoked using the function escape clause, returns the type name of an expression.

TYPEOF ( value-exp )

3.3.8 SQLTYPEOF

The SQLTYPEOF function, which can be invoked using the function escape clause, returns the canonical SQLTYPE name of an expression, which can be used in SAFECONVERT.

SQLTYPEOF ( value-exp )