Home » Developer & Programmer » Reports & Discoverer » Reports 10g : SQL Query via Data Model freezes (Oracle Ids 101202. Windows XP SP2. Core2Duo. 2g Ram)
Reports 10g : SQL Query via Data Model freezes [message #319292] Fri, 09 May 2008 11:13 Go to next message
Messages: 15
Registered: May 2008
Location: NY
Junior Member
Ver: iDS

Hello to all!

I currently have this setup: 1 Reports 2.5 (Mac) and 1 PC-based Reports 10gR1.

My goal is to migrate the 2.5 Mac version to 10g PC version. Looks straight forward right?

Well, not quite.

I successfully migrated the report but whenever i try to edit the SQL statements (via Data Model) my Reports 10g freezes (the hour glass shows up - i tried waiting.. for hours!). The strange thing is that under Reports 2.5 Mac side i can do the same thing w/o problems.

Just to strike out any incompatibility issues between Mac and PC: I tried making a NEW report in Reports 10g. I copied the SQL syntax from the original program, unfortunately, i still run into the same issue.

The SQL syntax in question has lots and lots of DECODE (and SUBSTR) within a Decode within a decode (you get the idea). It runs successfully in SQL*Plus, and more importantly it runs correctly under Reports 2.5 (Mac).

Can anybody provide me a clue as to what is causing the freeze? I'm accessing the same database.. the same data/table.. just in different Reports version. My SQL runs smoothly in SQL*Plus.

I appreciate any ideas. =)

Re: Reports 10g : SQL Query via Data Model freezes [message #319370 is a reply to message #319292] Sat, 10 May 2008 03:20 Go to previous messageGo to next message
Messages: 21148
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
All I can say is that I'm glad that I don't have such a problem.

However, I have a few ideas which will probably not be helpful, but nevertheless ...

Could you migrate this report to 6i first, test whether it works or not, and if it does - save it and then try to open it using Reports Builder 10g? Maybe 10g is too far from 2.5 ... just maybe.

What makes me worry is the fact that Builder freezes if you use the same query in a brand new report. It just *shouldn't* freeze; I don't want to believe that this "complex" query is too complex for 10g if it successfully worked on 2.5.

What happens if you create a simple-query report? Such as "select 'x' my_column from dual" type; does it work correctly? If so, could you type (I really mean it - type, not copy-paste) the original (non-working) query line-by-line? For example,
SELECT DECODE(DECODE(...)) first_value
FROM some_table_1 t1, another_table_2 t2
WHERE t1.id = t2.id
and see whether it behaves normally or not; if it does, try to add some values more. I know it is probably too much of typing for nothing, but - nothing else crosses my mind. Sorry.
Re: Reports 10g : SQL Query via Data Model freezes [message #319663 is a reply to message #319370] Mon, 12 May 2008 09:22 Go to previous message
Messages: 15
Registered: May 2008
Location: NY
Junior Member
@littlefoot: thanks for the suggestions.

'Normal' queries are alright, it can generate those successfully. I'm also quite puzzled at the behavior specially since i can run SQL w/o issues via SQL*Plus or Toad.

I'll try to make the Oracle 6i conversion first as you suggested and see what happens.

Previous Topic: Parameter form
Next Topic: Oracle Report
Goto Forum:

Current Time: Tue Aug 22 04:26:09 CDT 2017

Total time taken to generate the page: 0.02218 seconds