Skip to main content



      Home
Home » Archived » 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
Eclipse UserFriend
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] by 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
Eclipse UserFriend
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
Eclipse UserFriend
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] by 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
Eclipse UserFriend
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
Eclipse UserFriend
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
Eclipse UserFriend
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
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
Eclipse UserFriend
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 Jul 24 16:56:27 EDT 2025

Powered by FUDForum. Page generated in 0.05243 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top