Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Report Designer enforces invalid SQL for Oracle(Multi table query with table alias enforces 'AS' keyword, rejected by Oracle)
Report Designer enforces invalid SQL for Oracle [message #551754] Mon, 09 August 2010 09:13 Go to next message
Jules is currently offline Jules
Messages: 8
Registered: August 2010
Junior Member
I've created a datasource on Oracle 10g thin driver ojdbc14-10.2.0.2.0.jar - OK

Now trying to create a dataset, I've pasted in a multi table join query in the style:

select a.col1, b.col2
from table1 a, table2 b

But the designer reworks the statement and inserts the 'AS' keyword between the table name and alias. e.g:

select a.col1, b.col2
from table1 AS a, table2 AS b

When trying to run the query it fails with ORA-00933. Copy pasting and trying to execute in TOAD delivers the same. Remove the 'AS' keywords and it works as expected.

The Oracle documentation says the 'AS' is optional but dba's say actually its not acceptable by Oracle. this seems like a very basic problem and being new to BIRT I can't see where to configure anything to make a difference.

If I edit out the 'AS' it just puts it back in every time.

Can anyone advise?

[Updated on: Mon, 09 August 2010 09:14]

Report message to a moderator

Re: Report Designer enforces invalid SQL for Oracle [message #551764 is a reply to message #551754] Mon, 09 August 2010 09:42 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. Polunsky
Messages: 197
Registered: July 2009
Location: Houston TX
Senior Member

I've written queries like that in the 2.2.1.1, 2.3.0 and 2.5.0 designer and never had the designer change my text. Is the designer capable of seeing all of the referenced tables?
Re: Report Designer enforces invalid SQL for Oracle [message #551787 is a reply to message #551754] Mon, 09 August 2010 10:39 Go to previous messageGo to next message
Jules is currently offline Jules
Messages: 8
Registered: August 2010
Junior Member
Thanks Richard - forgot to mention; because of the complex dependencies I took the easy route and downloaded the version bundled with Helios. This is BIRT 2.6.0 with report designer 2.6.0

Yes the designer can see the tables, nothing complex there

[Updated on: Mon, 09 August 2010 10:45]

Report message to a moderator

Re: Report Designer enforces invalid SQL for Oracle [message #551791 is a reply to message #551754] Mon, 09 August 2010 10:55 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. Polunsky
Messages: 197
Registered: July 2009
Location: Houston TX
Senior Member

Try using ojdbc6.jar instead and see if that makes any difference. Otherwise I have no idea. Haven't worked with 2.6.0 yet.
Re: Report Designer enforces invalid SQL for Oracle [message #551795 is a reply to message #551791] Mon, 09 August 2010 11:00 Go to previous messageGo to next message
Jules is currently offline Jules
Messages: 8
Registered: August 2010
Junior Member
Looks like the maven plugin isnt working with helios due to bugs so I think I'll have to rewind to BIRT 2.5.0 and retry
Re: Report Designer enforces invalid SQL for Oracle [message #551803 is a reply to message #551795] Mon, 09 August 2010 11:21 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Have you tried:
select table1.col1, table2.col2
from table1, table2

Jason

On 8/9/2010 11:00 AM, Jules wrote:
> Looks like the maven plugin isnt working with helios due to bugs so I
> think I'll have to rewind to BIRT 2.5.0 and retry


Jason Weathersby

BIRT Exchange
Re: Report Designer enforces invalid SQL for Oracle [message #551873 is a reply to message #551787] Mon, 09 August 2010 17:56 Go to previous message
Linda Chan
Messages: 843
Registered: July 2009
Senior Member
Jules,

It seems like you are using the SQL Query Builder?
It has a known issue with generating the AS keyword for Oracle: https://bugs.eclipse.org/bugs/show_bug.cgi?id=319927.
It is fixed in the latest BIRT 2.6.1 build.

Linda
Previous Topic:how to display all datas in line chart
Next Topic:BIRT XML Tags
Goto Forum:
  


Current Time: Thu Apr 24 11:29:33 EDT 2014

Powered by FUDForum. Page generated in 0.01669 seconds