sql query problem

Question

why this query give me an error:ORA-01790

SELECT TO_CHAR(logical_date,'MM') MONTH
       FROM logical_date WHERE logical_date_type='B'        
UNION 
SELECT 
        TO_CHAR(logical_date,'MM')+1 MONTH
        FROM logical_date WHERE logical_date_type='B'

but when i run them separately,they give the proper output.

Answer 1

I suspect the +1 is causing a data type conversion. Try:

SELECT TO_CHAR(logical_date,'MM')+0 MONTH 
       FROM logical_date WHERE logical_date_type='B'         
UNION  
SELECT  
        TO_CHAR(logical_date,'MM')+1 MONTH 
        FROM logical_date WHERE logical_date_type='B' 

Answer 2

You want a distinct set of months in this table, plus all their following months, right?

select to_char(logical_date, 'MM') MONTH 
from (   
    SELECT logical_date
    FROM logical_date WHERE logical_date_type='B'        
    UNION 
    SELECT ADD_MONTHS(logical_date,1)
    FROM logical_date WHERE logical_date_type='B'
)
/

Alternatively we can use TRUNC(logical_date, 'MM') to retrieve the first day of the month, should we need to retain a DATE datatype.