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

server variable

Received Response
1
Views
3
Comments
User_97XVQ
User_97XVQ Rank 3 - Community Apprentice
edited Aug 13, 2024 5:52PM in Oracle Analytics Cloud and Server

Hi,

I am using OBIEE 11.1.1.7.16, I am working on a direct database request that references a date presentation variable. I would like to default the presentation variable to a server variable(prior month start date & prior month end date)

and orderdate BETWEEN TO_NUMBER(TO_CHAR(TO_DATE( '@{fromdate}{VALUEOF(NQ_SESSION.PRIOR_MONTH_START_DT)}', 'YYYY-MM-DD HH24:MI:SS'), 'YYYYDDD'))-1900000 AND TO_NUMBER(TO_CHAR(TO_DATE('@{todate}{VALUEOF(NQ_SESSION.PRIOR_MONTH_END_DT)}}', 'YYYY-MM-DD HH24:MI:SS'), 'YYYYDDD'))-1900000

the query is not returning any data but if I replace the server variable with hardcoded dates 2017-12-01 to 2017-12-31 there is output returned. I checked the query log and there is no server variable date being passed. I can view the server variable dates in the rpd so not an issue with the variable

Is the above syntax incorrect?

Thanks

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Hi,

    you might want to double check that the connection pool in question has not been blacklisted, I have seen this a number of times where server variables cease to work due to temporary (or permanent) connectivity issues.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    You might also want to try it as a session variable.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    You are probably having data type issues between dates and timestamps ... but really if you have to do to_number on a to_char on a to_date then subtract 1900000 from it then you've got other issues ...  USE of information DRIVES physical data model design.  You've got a use for the information, but haven't stored it in such away that it is readily usable.  Best = store dates in date dimension and have facts joined on real date keys; Better = have the variable value already converted to the YYYYDDD number format it seems you have your orderdate in ...