CODEWITHSUNDEEP
sqloracle-database
Vijay
Vijay

Reputation: 67211

sql query problem

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.

Upvotes: 0

Views: 160

Answers (2)

APC
APC

Reputation: 146189

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.

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

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'

Upvotes: 4

Related Questions