Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

BrioQuery v6.6.4 convert existing reports from DB2 to MS SQL Server

Posted on 2004-10-27
2
Medium Priority
?
578 Views
Last Modified: 2007-12-19
I am on a project where we are converting BrioQuery v6.6.4 existing reports from DB2 to MS SQL Server.

The reports are pre-existing and working.

HERE IS A LIST OF OBSERVATIONS:

1.
I open report1, and run it as is, it runs fine.  Yet if I open the 'view' -> 'custom SQL',
and run it it fails on 'Not Use11', here is the code:

select srst1staff.empusrid, vstb01be.subusrid
FROM From.Vstb01Be, From.Srst1Staff, From.Cont1Conti WHERE (Cont1Conti.Companyid=Vstb01Be.Companyid AND

Cont1Conti.Conid=Srst1Staff.Conid AND Srst1Staff.Conid=Vstb01Be.Supid)  AND (Vstb01Be.Subdate>='Not Use11' AND

Vstb01Be.Batstcd='O' AND Srst1Staff.Empusrid='AWILEY  ' AND Vstb01Be.Statuscd='A')

The error occurs on the 'Not Use11'.



2.
If I connect to db2 then hit the 'reset' button it updates the custom sql from the GUI:

select srst1staff.empusrid, vstb01be.subusrid
FROM From.Vstb01Be, From.Srst1Staff, From.Cont1Conti WHERE (Cont1Conti.Companyid=Vstb01Be.Companyid AND

Cont1Conti.Conid=Srst1Staff.Conid AND Srst1Staff.Conid=Vstb01Be.Supid)  AND (Vstb01Be.Subdate>='2002-06-11' AND

Vstb01Be.Batstcd='O' AND Srst1Staff.Empusrid='AWILEY  ' AND Vstb01Be.Statuscd='A')

Note: the 'Not Use11' has been replaced by '2002-06-11'



3.
Now I open a connection to MS SQL Server and run the query I get an error saying
"SQLSTATE 22008, Error Msg: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime

from character string."


4.
So now I open the custom SQL and hit the reset button, the code changes to:

select srst1staff.empusrid, vstb01be.subusrid
FROM From.Vstb01Be, From.Srst1Staff, From.Cont1Conti WHERE (Cont1Conti.Companyid=Vstb01Be.Companyid AND

Cont1Conti.Conid=Srst1Staff.Conid AND Srst1Staff.Conid=Vstb01Be.Supid)  
AND (Vstb01Be.Subdate>={d '2002-06-11 00:00:00.000'} AND Vstb01Be.Batstcd='O' AND Srst1Staff.Empusrid='AWILEY  

' AND Vstb01Be.Statuscd='A')

it gives the following error
"SQLExecDirect2: rc = -1, SQLSTATE 22008, Error Msg: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax

error converting datetime from character string."


5.
Now if I remain connected to MS SQL Server, remove the datetime field in the GUI, then add it back, open the

veiw custome SQL and hit the reset button the code is:

select srst1staff.empusrid, vstb01be.subusrid
FROM From.Vstb01Be, From.Srst1Staff, From.Cont1Conti WHERE (Cont1Conti.Companyid=Vstb01Be.Companyid AND

Cont1Conti.Conid=Srst1Staff.Conid AND Srst1Staff.Conid=Vstb01Be.Supid)  
AND (Vstb01Be.Subdate>={ts '2002-06-11 00:00:00.000'} AND Vstb01Be.Batstcd='O' AND Srst1Staff.Empusrid='AWILEY  

' AND Vstb01Be.Statuscd='A')

This run ok with the custom sql window open or closed.


So I have proof that BrioQuery is NOT A WYSIWYG (What you see is what you get).

QUESTION
Is there a bug fix for this from the vendor?



COPY OF FOLLOW UP EMAIL WITH HYPERION TECH SUPPORT 10/27/04
COPY OF FOLLOW UP EMAIL WITH HYPERION TECH SUPPORT 10/27/04
COPY OF FOLLOW UP EMAIL WITH HYPERION TECH SUPPORT 10/27/04
COPY OF FOLLOW UP EMAIL WITH HYPERION TECH SUPPORT 10/27/04


As we discused on the phone.

Here are the identical SQL exports.

What I did.

1. open report
2. run report against db2
3. put in user id, password
4. export sql to query_db2.txt
5. break connection
6. open view custome sql
7. run report against ms
8. put in user id, password
9. export sql to query_ms.txt
10. close custom sql window.
11. run report again.
12. here is the error
SQLExecDirect2: rc = -1, SQLSTATE: 22008, Error Msg: [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax

error converting datetime from character string.

13. export sql to query_ms_error.txt

14. I can reproduce the error by not saving the report and closing it, then opening it againg and following the

same sequence.
THIS IS IMPORTANT, SHOW THIS TO A PROGRAMMER, I CAN REPRODUCE THE ERROR BY FOLLOWING THE SAME STEPS AS ABOVE.


query_db2.txt
SELECT AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT, SUM ( AL3.CQTYSLD ), SUM ( AL3.CONSLSC ), SUM ( AL3.NCQTYSLD ), SUM ( AL3.NCONSLSC )  FROM SRIS.VPTB01NF AL1, VHA.MEMT1PHINQ AL2, SRIS.VPTB01SP AL3 WHERE (AL3.COMPANYID=AL2.COMPANYID AND AL3.HSPID=AL2.HSPID AND AL3.NDC=AL1.NDC)  AND (AL1.NDC IN ('61563020021', '65044999705') AND AL3.ADADT>='2004-01-01') GROUP BY AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT


query_ms.txt
SELECT AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT, SUM ( AL3.CQTYSLD ), SUM ( AL3.CONSLSC ), SUM ( AL3.NCQTYSLD ), SUM ( AL3.NCONSLSC )  FROM SRIS.VPTB01NF AL1, VHA.MEMT1PHINQ AL2, SRIS.VPTB01SP AL3 WHERE (AL3.COMPANYID=AL2.COMPANYID AND AL3.HSPID=AL2.HSPID AND AL3.NDC=AL1.NDC)  AND (AL1.NDC IN ('61563020021', '65044999705') AND AL3.ADADT>='2004-01-01') GROUP BY AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT


query_ms_error.txt
SELECT AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT, SUM ( AL3.CQTYSLD ), SUM ( AL3.CONSLSC ), SUM ( AL3.NCQTYSLD ), SUM ( AL3.NCONSLSC ) FROM SRIS.VPTB01NF AL1, VHA.MEMT1PHINQ AL2, SRIS.VPTB01SP AL3 WHERE (AL3.COMPANYID=AL2.COMPANYID AND AL3.HSPID=AL2.HSPID AND AL3.NDC=AL1.NDC)  AND (AL1.NDC IN ('61563020021', '65044999705') AND AL3.ADADT>={d '2004-01-01 00:00:00.000'}) GROUP BY AL1.BRANDNM, AL1.GENNAME, AL1.NDC, AL1.DSGFRMDE, AL1.DRGFRMCD, AL1.DRGSTRDE, AL1.DRGSTRNO, AL1.DRGSTRUN, AL3.ADADT
0
Comment
Question by:Duane Lawrence
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Author Comment

by:Duane Lawrence
ID: 12466141
I answered my own question.

There is a bug in BrioQuery that causes problems with dates.  I have a work around.  I just wanted to show documented proof from the vendor on the known problem.
 
Explanation:
When the dates are sent to DB2:  Cont1Conti.Conenddt>='2000-01-01'
 
When the dates are sent to SQL Server: Cont1Conti.Conenddt>={d '2000-01-01 00:00:00.000'}
         NOTE: The proprietary syntax {d }
 
What should be sent to SQL Server and is sent when a new date is added:  Cont1Conti.Conenddt>={ts '2000-01-01 00:00:00.000'}
         NOTE: The proprietary syntax {ts }
 
Please keep a copy of this email for long term.
Duane
 
 
 
 
-----Original Message-----
From: Timothy Dillingham [mailto:Timothy_Dillingham@hyperion.com]
Sent: Monday, November 01, 2004 12:47 PM
To: Lawrence, Duane
Subject: RE: SR# 2-4099804


Duane, converting from a DB2 to a Microsoft SQL Server database will cause problems with the SQL that is sent to the respective database.  
In your situation the DB2 database expects date/time SQL syntax differently the the Microsoft SQL Server database.  We currently do not automatically
update the SQL syntax when the database changes.  This must be done manually by removing then adding the date/time field.  This will change the SQL
syntax that is sent to the database.  Please let me know if you have any questions.  Thanks
 
Tim
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12466797
Question answered by asker or dialog valuable.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question