IS CASE ... END available for querying DMVs?

Jun 25, 2013 at 11:43 AM
I am trying to use the CASE Statement to explicitly name the Column_encoding type. However, this throws up an error about dialect ambiguity. I have already found out that not all SQL functions are available querying a DMV, but I am not sure that this really is the problem.

This is the query.
SELECT
DIMENSION_NAME, 
COLUMN_ID, 
CASE COLUMN_ENCODING WHEN '1' THEN 'HASH' WHEN '2' THEN 'VALUE' ELSE 'NO_ENC' END AS ENCODING,
DATATYPE,
DICTIONARY_SIZE  

from $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS 

WHERE COLUMN_TYPE = 'BASIC_DATA' AND COLUMN_ID <> 'RowNumber'
Any help would be appreciated
Carsten
Coordinator
Jun 25, 2013 at 1:50 PM
DMV queries are not actually SQL queries. Technically they are executed using the DMX parser and DMX does not support the CASE function so this is definitely your issue. If you are producing a report you might be able to use an expression in your reporting tool to achieve the same effect.