server variable

User_97XVQ

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

Robert Angel

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

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

Thomas Dodds

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