Condition in creating view - liquibase xml

Question

I want to make some view but with condition in my fourth column.

CONDITION: If epr.j_id is not null then fill lj.id BUT WHEN epr.j_id is null then fill 'NONE'

I've found some condition tag there but I have no idea how I can use it or maybe there is some another way...

    <changeSet id="103" author="m">
    <createView replaceIfExists="true"
                schemaName="public"
                viewName="report">
        SELECT DISTINCT l.id,
        l.name,
        l.gis_ext_id AS gis_ext_id_line,
        condition (epr.j_id is not null ),
        

Answer 1

From a previous StackOverflow question: How to add condition to changeSet

If you want to update the columns datatype depending on certain conditions, you can use <preConditions> with <sqlCheck>, and then use <modifyDataType>.

So supposing that there's already a table with name TASK and it contains a column named STATUS, you can modify it's datatype as such:

<changeSet id="1" author="xxx">
    <comment>Update Asset table</comment>
    <preConditions onFail="MARK_RAN">
        <and>
            <columnExists tableName="TASK" columnName="STATUS"/>
            <sqlCheck expectedResult="0"> SELECT COUNT(*) FROM TASK WHERE STATUS = "pending";</sqlCheck>
        </and>
    </preConditions>
    <modifyDataType tableName="TASK" columnName="STATUS" newDataType="VARCHAR(10)"/>
</changeSet>

Or perhaps you'd like another onFail-behaviour. Check out this link

• HALT - Immediately halt execution of entire change log [default]
• CONTINUE - Skip over change set. Execution of change set will be attempted again on the next update. Continue with change log.
• MARK_RAN - Skip over change set, but mark it as ran. Continue with change log
• WARN - Output warning and continue executing change set as normal.

Additional Documentation:

• Liquibase: Create View
• Mohit Goyal: Check database state and conditionally apply changes in the Liquibase