CODEWITHSUNDEEP
sqloracle-databaseexpressionpeoplesoft
Victor Medina
Victor Medina

Reputation: 11

Oracle Peoplesoft Case Expression error with date format

Working in peoplesoft and keep getting the following error related to the expression below:

ORA-00932: inconsistent datatypes: expected NUMBER got DATE expresssion:

CASE WHEN K.DUE_DT - K.PRIOR_DAYS >= TRUNC(SYSDATE) THEN K.DUE_DATE

I did some googling and came up with this but I get the same error

(
    CASE  
        WHEN to_date(K.DUE_DT, 'YYYY-MM-DD') -  to_date(K.PRIOR_DAYS, 'YYYY-MM-DD') 
            >= to_date(TRUNC(SYSDATE), 'YYYY-MM-DD') 
        THEN  to_date(K.DUE_DT,'YYYY-MM-DD') 
    END
)

Upvotes: 1

Views: 601

Answers (2)

Himanshu
Himanshu

Reputation: 3970

Your comparision is wrong difference of dates gives a number of days not date so comparing a number with a date is pointless likewise Oracle gives the error

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

This is what you have:

WHEN to_date(K.DUE_DT, 'YYYY-MM-DD') - to_date(K.PRIOR_DAYS, 'YYYY-MM-DD') >= to_date(TRUNC(SYSDATE), 'YYYY-MM-DD')

Apart from what you've been told about applying TO_DATE function to something that already is date (i.e. SYSDATE in this case, maybe even due_dt and prior_days as well, if those are DATEs), here's how it goes: code you wrote means this:

  • you are subtracting two dates (due_dt - prior_days)
  • result of such an operation is number of days between those two dates
  • you are then asking whether it is >= sysdate (i.e. date)
    • for example, that might be when 4 > 2019-11-22 then ...
  • and that's why Oracle says expected NUMBER got DATE
  • shortly, it doesn't make sense

Can it be fixed? Sure, only if we knew what you want to do. Hopefully, what I wrote will help you do it.

Upvotes: 1

Related Questions