Data value casing

You can now define the case of columns in a Model, by changing the case of the columns at table level. By default, ThoughtSpot uses the LOWER function for string comparisons. In cases where this function impacts performance, you may want to use the data value casing feature to change the column’s case.

This feature is not the same as case-sensitive search.

Data value case behavior

The following table describes how ThoughtSpot generates SQL for string comparisons depending on the data value casing setting for a column.

Setting SQL behavior

DEFAULT

ThoughtSpot applies the LOWER() function to the column during string comparison. This is the standard behavior and matches the default case-insensitive search.

MIXED

ThoughtSpot applies the LOWER() function to the column during string comparison. Equivalent to DEFAULT.

ALL_LOWERCASE

ThoughtSpot assumes the stored data is already lowercase. The LOWER() function is not applied to the column during string comparison.

ALL_UPPERCASE

ThoughtSpot assumes the stored data is already uppercase. The LOWER() function is not applied to the column. Instead, ThoughtSpot wraps the search string values in the UPPER() function during comparison.
ThoughtSpot does not sample the data for these columns. If the actual data casing does not match the configured setting, query results may be incorrect. == Examples

The following examples use a COMPANY_NAME column filtered by the values acme industries and nakatomi corporation to illustrate how each casing setting affects the generated SQL.

DEFAULT

With the DEFAULT setting, ThoughtSpot applies LOWER() to the column on both sides of the comparison to perform case-insensitive matching.

SELECT "ta_1"."COMPANY_NAME" "ca_1"
FROM "DUNDERMIFFLIN"."PUBLIC"."DM_CUSTOMER" "ta_1"
WHERE LOWER("ta_1"."COMPANY_NAME") IN (
  LOWER('acme industries'), LOWER('nakatomi corporation')
)
GROUP BY "ca_1"
ORDER BY "ca_1" ASC NULLS LAST

ALL_LOWERCASE

With ALL_LOWERCASE, ThoughtSpot assumes the stored data is already lowercase. The LOWER() function is not applied to the column or the search values during comparison.

SELECT "ta_1"."COMPANY_NAME" "ca_1"
FROM "DUNDERMIFFLIN"."PUBLIC"."DM_CUSTOMER" "ta_1"
WHERE "ta_1"."COMPANY_NAME" IN (
  'acme industries', 'nakatomi corporation'
)
GROUP BY "ca_1"
ORDER BY "ca_1" ASC NULLS LAST
If the stored data is not actually lowercase, query results may be incorrect.

ALL_UPPERCASE

With ALL_UPPERCASE, ThoughtSpot assumes the stored data is already uppercase. The LOWER() function is not applied to the column. Instead, ThoughtSpot wraps the search string values in the UPPER() function so they match the stored casing.

SELECT "ta_1"."COMPANY_NAME" "ca_1"
FROM "DUNDERMIFFLIN"."PUBLIC"."DM_CUSTOMER" "ta_1"
WHERE "ta_1"."COMPANY_NAME" IN (
  UPPER('acme industries'), UPPER('nakatomi corporation')
)
GROUP BY "ca_1"
ORDER BY "ca_1" ASC NULLS LAST
If the stored data is not actually uppercase, query results may be incorrect. === ALL_UPPERCASE behavior

When a column is configured as ALL_UPPERCASE, ThoughtSpot assumes the stored data is already uppercase. During string comparison, instead of applying UPPER() to the column, ThoughtSpot applies UPPER() to the search string values you provide. This means the column value itself is compared as-is against an uppercased version of your filter input.

For example, if you filter on COMPANY_NAME with values acme industries and nakatomi corporation, the generated SQL looks like this:

SELECT "ta_1"."COMPANY_NAME" "ca_1"
FROM "DUNDERMIFFLIN"."PUBLIC"."DM_CUSTOMER" "ta_1"
WHERE "ta_1"."COMPANY_NAME" IN (
  UPPER('acme industries'), UPPER('nakatomi corporation')
)
GROUP BY "ca_1"
ORDER BY "ca_1" ASC NULLS LAST
If you configure a column as ALL_UPPERCASE but the stored data is not actually uppercase, query results may be incorrect or incomplete.
Was this page helpful?
×