Solved

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

Posted on 2004-10-27
544 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
Question by:Duane Lawrence
    2 Comments
     
    LVL 6

    Author Comment

    by:Duane Lawrence
    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:
    Question answered by asker or dialog valuable.
    Closed, 500 points refunded.
    ee_ai_construct (replacement part #xm34)
    Community Support Admin
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
    I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now