I have a table like this:

I have to create a materialized view in SQL with a new varchar column containing the column B value with a symbol added when the value is positive. I want a result like:

I tried the following but this adds a symbol before all values:

CREATE MATERIALIZED VIEW schema.view
as
    select column a, column b, CONCAT(' ', column b)  AS column c 
    from schema.table;

CodePudding user response：

You can use a CASE statement to add the positive sign only if there isn't already a negative sign. Something like:

CREATE MATERIALIZED VIEW schema.view
as
    select columna, columnb, CASE WHEN columnb NOT LIKE '-%' THEN CONCAT(' ', columnb) ELSE columnb END columnc 
    from schema.table;

However, I would suggest revisiting whether this is actually necessary for your actual scenario. This seems like something trivial to calculate on the fly. Typically I would expect a materialized view to be used when the calculations are complex and there are performance gains that justify the duplication and wasted space. Maybe you could use a regular view or a generated column instead.