3 Replies Latest reply on Jan 4, 2018 5:08 PM by Thomas Dodds

    server variable

    3026782
    3026782 Jan 4, 2018 4:05 PM

      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

      • 63 Views
      • Tags:
        • 1. Re: server variable
          Robert Angel
          Robert Angel Jan 4, 2018 4:58 PM (in response to 3026782)

          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.

          • 2. Re: server variable
            Robert Angel
            Robert Angel Jan 4, 2018 5:00 PM (in response to 3026782)

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

            • 3. Re: server variable
              Thomas Dodds
              Thomas Dodds Jan 4, 2018 5:08 PM (in response to 3026782)

              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 ...