Query with function on SYSDATE not work in BI Publisher Reports

Question

Summary

Content

Hi,

I have a problem with function for SYSDATE, where this query work on SQL Developer but not work in BI Publisher. I can said more: query works fine in BI Publisher in Data model for "Save like sample data" but not work when I generate report from this data model.

function:

--description: I want to take from year before today and take from three months before that date.

in clause WHERE TO_CHAR(table.UNLOADINGDATE, 'YYYY-MM') >= TO_CHAR((ADD_MONTHS((TO_DATE((SUBSTR(TO_CHAR(SYSDATE, 'YYYY'), 0, 4)-1)) || '-' || TO_CHAR(SYSDATE, 'MM-DD')), -3)), 'YYYY-MM')

Can anyone help?

Brajesh Shukla-95078 · Answer

I don't understand need to break the SYSDATE in the YYYY and MM-DD format because if you say "want to take from year before today and take from three months before that date"

means 15 months before today's date?

Can we directly try add_months(sysdate,-15)

Turek · Answer

Brajesh thanks, of course you are right! I don't know why I tried to do my life harder.