可以使用case表达式：

select t.*, (case when liqn_days > 0 and liq_months = 0 and liqn_yearsand = 0
                  then 'Monthly'
                  when liq_months = 3 and liqn_days = 0 and liqn_yearsand = 0
                  then 'quarterly'
                  when liq_months =  6 and liqn_days = 0 and liqn_yearsand = 0
                  then 'half yearly'
                  when liq_months =  0 and liqn_days = 0 and liqn_yearsand > 0
                  then ' yearly' 
                  else 'other'
             end)
from table t;

你也可以使用联盟所有

WITH da(Other_columns,liqn_years, liq_months, liqn_days) AS ( 
SELECT 'Other_colums', 0,0,1 FROM dual
UNION ALL SELECT 'Other_colums',12,0,0 FROM dual
UNION ALL SELECT 'Other_colums',0,3,0 FROM dual
)
SELECT Other_columns, 'Yearly' AS output  FROM da WHERE liqn_years > 0
UNION ALL
SELECT Other_columns, 'Monthly' AS output  FROM da WHERE liqn_years > 0
UNION ALL
SELECT Other_columns, 'quaterly' AS output  FROM da WHERE liqn_years > 0

结果

OTHER_COLUMNS|OUTPUT  |
-------------|--------|
Other_colums |Yearly  |
Other_colums |Monthly |
Other_colums |quaterly|