Decimal/Numeric datatype - Mandatory Filter Parameters

Hi,

Case 1: Using CAST function in Analytical View

  1. Actual datatype for columns:
    Column1 DECIMAL(10,0)
    Column2 DECIMAL(10,0)

  2. Used the CAST function in Analytical View.
    CAST(Column1 AS STRING) Column1,
    CAST(Column2 AS STRING) Column2

  3. While refreshing the analytical view from Arcadia UI, then prompting an error as:
    Job Target Failed:(HY000,None)
    AnalysisException: operands of type STRING and SMALLINT are not comparable: Column1=2010.
    Last Task: Error starting refresh of <schema_name>.:(HY000,None)
    AnalysisException: operands of type STRING and SMALLINT are not comparable:Column1=2010.

Case 2: Without using CAST function in Analytical View

  1. Actual datatype for columns:
    Column1 DECIMAL(10,0)
    Column2 DECIMAL(10,0)

  2. While using the columns as mandatory filter parameters, we are using the filter in the dashboard as
    [Column1] in (<Column1.data:’’>)

    Here is the error while validating & saving the above condition:
    Validation Error: (HY000, None) AnalysisException: Incompatible return types ‘STRING’ and ‘SMALLINT’ of exprs ‘Column1’ and ‘2014’.

Could you pelase help us in creating the mandatory filter parameter which is DECIMAL/NUMERIC datatype?

@chaitanya_lanka as you noted you do not need to cast the decimal field to string. That will lead to error.

In Case 2: you need to modify your expression to handle numeric types and when you have “ALL” selected. The way to do this is using a simple CASE statement as follows:

CASE WHEN <<Column1.data:-123>>=-123 THEN (1=1) ELSE [Column1] in (<<Column1.data:-123>>)  END

Mandatory filter parameter: [Column1] in (<<Column1.data:’’>>) condition is working for STRING Datatype.

What could be the Mandatory filter parameter condition for NUMERIC/DECIMAL Datatype?

@chaitanya_lanka I’m not sure what you mean by mandatory filter parameter.

But the expression I provided should work for Numeric/decimals. When no filter are selected you can force the expression to false. As shown here:

CASE WHEN <<Column1.data:-123>>=-123 THEN false ELSE [Column1] in (<<Column1.data:-123>>)  END

I tried the CASE condition and is not working.
(CASE WHEN <Column1.data:-123>=-123 THEN false ELSE [Column1] in (<Column1.data:-123>) END).

Filter Condition: [Column1] in (<<Column1.data:’’>>), In this condition single quotes( ’ ’ ) will be given for STRING datatypes. Same way, could you please provide the condition for Numeric/Decimal datatype?

Sorry a typo earlier it should be like this:

CASE WHEN -123 in (<<Column1.data:-123>>) THEN false ELSE [Column1] in (<<Column1.data:-123>>)  END