Phoenix; How to create a view with case

Question

I am trying to create a view on a table in Apache Phoenix and add/change the values of one of the base table columns in the view.

Something like this:

CREATE VIEW mobile_product_metrics (new_col varchar) AS
SELECT * ,
(CASE metric_type WHEN 'm' THEN 'mobile'
WHEN 'p' THEN 'phone'
ELSE 'unknown' END ) AS new_col
FROM product_metrics;

I am wondering if this is supported by Phoenix.

Answer 1

Here is the answer by James Taylor of Salesforce/Phoenix posted in the Phoenix user mailing list:

You'd need to create multiple views, one for each metric_type:

CREATE VIEW mobile_product_metrics (new_col1 varchar) AS SELECT * FROM product_metrics WHERE metric_type = 'm';

CREATE VIEW phone_product_metrics (new_col2 varchar) AS SELECT * FROM product_metrics WHERE metric_type = 'p';