Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Query with function on SYSDATE not work in BI Publisher Reports

Received Response
41
Views
2
Comments
Turek
Turek Rank 3 - Community Apprentice
in Oracle Analytics Cloud and Server

Summary

Query with function on SYSDATE not work in BI Publisher Reports

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?

Answers

  • Brajesh Shukla-95078
    Brajesh Shukla-95078 Rank 7 - Analytics Coach

    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
    Turek Rank 3 - Community Apprentice

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