I was wondering if anyone has a solution to a problem I am having with BusinessObjects. First of all, just to give you an idea of our environment, we are using an Oracle 7.3 database on Unix. The users are using BusinessObjects version 4.1.2. The Doc Agent server is on 4.1.3 on Windows NT, with Customer Service Patch #22.
We create many queries in which we specify dates in conditions in queries. For example, Contract Date less than or equal to 01/01/98. When people send queries off to Doc Agent, somewhere along the line the query’s date condition is changed locally. Single quotes are put around the date (i.e., Contract Date less than or equal to ‘01/01/98’). Therefore, when they try to send the query off to Doc Agent again, the query fails, because BusinessObjects is trying to compare a date field with a character field (i.e., ‘01/01/98’).
This problem has happened over a dozen times to us. Would upgrading all the users to 4.1.3 be the answer? I would like to get some feedback before I go ahead and upgrade everyone, since we have about 60 active users at the moment. I would appreciate any help anyone could give me in solving this problem.
Thank you,
Kevin Smith
g GE Capital Railcar Services
Kevin T. Smith
Consultant, BusinessObjects Administrator Phone: 312-853-5340
Fax: 312-853-5046
E-mail: Kevin.Smith2@gecapital.com
When people send queries off to Doc Agent, somewhere along the line the query’s date condition is changed locally. Single quotes are put around the date (i.e., Contract Date less than or equal to ‘01/01/98’).
I’m confused. Where do the quotes come from. The quotes make the date invalid.
Could you please elaborate?
Steve,
I don’t know what to tell you. Somewhere along the line, people’s queries were changed by the system to have single quotes around them, making them invalid.
I was working with a user recently to isolate this problem. He created a query with a date condition with a date value typed in (hardcoded, not a prompt). It was a valid date, as noted by the time stamp extension that appeared after he typed the date in and hit Enter. He ran it locally successfully. He then submitted the query to DocAgent. When he went to the query panel for that same query after that, there were single quotes around the date and the timestamp extension disappeared.
This was not an isolated incident. About a dozen times users (who are on 4.1.2) have called me, asking me why their queries fail. Since I have access to the directory they store their queries, I open up their queries from my machine (which is running on 4.1.3), I notice their hard coded dates in their condition statements have single quotes around them. I remove the single quotes and submit the queries to Doc Agent (version 4.1.3 with CSP #22). The queries run successfully. In addition, when I open the query panel for that query, the single quotes are not around the date field.
I am assuming that the problem has to do with two different version of BusinessObjects being utilized in the same decision support system. I could be wrong, however. That is why I sent the mail message around. I don’t want to just jump to conclusions.
I have opened a case with BusinessObjects. It is #54629. Jessica Cho has been very diligently trying to help me figure out why I am having a problem. However, she has not been able to recreate the problem on her side.
Anyway, I hope that answers some of the questions you might have had about what the problem I have is. Please let me know if I have not explained things adequately. Thanks for your question.
Does this report use a stored procedure or the query panel?
4.1.2 has a problem with stored procedures and parameters.
Tim
FROM RELEASE NOTES README
Vantive case SE22688 Bug Number 22619
If you create a BusinessObjects report using a Sybase 11.0.3 stored procedure that prompts you for a date, you must type " " around the date value.
Fixed. You are no longer required to type " " around the date value. _________________________
Tim Heuer
PacifiCare Health Systems
(714) 825-5702
(800) 759-8888 pin 1251901 - pager tim.heuer@phs.com
Erich is exactly right: in Oracle environments, SQL*Net, the middleware of Oracle likes to do various translations “on the fly” depending on the NLS Language/Territory, etc. settings. It even translates the data-contents of char/varchar/long fields!! depending on the database/client character code settings (US7ASCII is a seven bit coding, the WE8ISO8859P1 setting mostly used in europe, is an 8 bit char code). This also gives you troubles with retrieving the data correctly and moving between different Oracle Clients/Servers. We are exeriencing this in many situations at our customers where thy do default installations of oracle databases, not being aware of this issues.
Walter
Hurst, Erich schrieb:
We create many queries in which we specify dates in conditions in queries. For example, Contract Date less than or equal to 01/01/98. When people send queries off to Doc Agent, somewhere along the line the
query’s date condition is changed locally. Single quotes are put around
the date (i.e., Contract Date less than or equal to ‘01/01/98’). Therefore, when they try to send the query off to Doc Agent again, the
query fails, because BusinessObjects is trying to compare a date field
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ with a character field (i.e., ‘01/01/98’). ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ --------------------------
This is simply not true. In Oracle, if you have a condition CONTRACT_DATE <= ‘01/01/98’, the literal string is implicitly converted to a date type. Now, that implicit conversion will fail if your NLS_DATE_FORMAT (see the ALTER SESSION command) does not match. For example, if your NLS_DATE_FORMAT is ‘yy/mm/dd’, then ‘01/01/98’ is an invalid date and will not implicitly convert.
I can only guess what your problem is, but it sounds like the client PC that submits the query is formatting the date differently than the DAS PC is expecting it. That’s why you only see this problem with queries you send to DAS. I believe you need to look at the OCIV7.SBO file, at the InitDateFormat parameter. I wouldn’t be surprised if the client PC’s InitDateFormat parameter were different from the DAS PC’s.
Something to note, by the way: When you tell Oracle you want to SELECT * FROM TABLE T WHERE CONTRACT_DATE <= ‘01/01/98’, you are really telling Oracle you want to SELECT * FROM TABLE T WHERE CONTRACT_DATE <= ‘01/01/98 12:00:00AM’. So, for example, if you have a record with a contract date of ‘01/01/98 9:30:00AM’, it will NOT retrieve that record.