Report Wizard inserts table alias using AS - bad for Oracle

Getting your data onto paper - or the web - Discussing the reports features of Base

Report Wizard inserts table alias using AS - bad for Oracle

Postby elmicha » Mon Jul 14, 2008 10:39 pm

Hi,

I tried to use the Report Wizard to make a simple report.
The Wizard created this syntax:

SELECT "GLOBAL_NAME"."GLOBAL_NAME" FROM "SYS"."GLOBAL_NAME" AS "GLOBAL_NAME"

Oracle does not like this "AS" before the table alias. I get "The query with the statement ... could not be run".

In Database -> Advanced Settings there is a check box "Use keyword AS before table alias names". Alas, this check box is disabled (grayed out), and the check box is on instead of off.

This is with Ooo 2.4.0 and Oracle 10.2.0.1.0 on Gentoo.

Regards...
Michael
OOo 2.4.X on Linux-Other
elmicha
 
Posts: 3
Joined: Mon Jul 14, 2008 10:09 pm

Re: Report Wizard inserts table alias using AS - bad for Oracle

Postby Safway » Mon Jul 14, 2008 11:42 pm

Yes, and how can I help? What are you trying to do?

Have you considered running your query command using Oracle SQL syntax instead of OO.o syntax?
Go to Queries | Righ-click on the query | Select to edit in SQL view | Edit | "Run SQL command directly"
If someone posts a fix for your question, then please post a quick thank you and then go to your first post, use the edit button to add [Solved] as the first word of the title so other people can rely on the fix as well.
LibreOffice 3.3.3 on Fedora
Safway
Volunteer
 
Posts: 347
Joined: Thu Apr 24, 2008 4:35 pm

Re: Report Wizard inserts table alias using AS - bad for Oracle

Postby elmicha » Tue Jul 15, 2008 12:14 am

I'm trying to find out whether I can use Ooo to make some reports. So I started with a simple report.

I can't find a way to change the SQL of a report (umm, yes, I'm rather new to oobase) - the Edit menu item on the report only opens oowriter.

But with the help of your answer I found that I can indeed change the SQL of a query (and as soon as I delete that "AS", it works), and I can use a query to generate a report.

Of course it would be a lot nicer if Ooo wouldn't insert that "AS" in the first place.

In Database -> Advanced Settings there is a check box "Use keyword AS before table alias names". Alas, this check box is disabled (grayed out), and the check box is on instead of off.


It seems they just have a wrong default value there, so maybe I should report a bug?

Regards...
Michael
OOo 2.4.X on Linux-Other
elmicha
 
Posts: 3
Joined: Mon Jul 14, 2008 10:09 pm

Re: Report Wizard inserts table alias using AS - bad for Oracle

Postby Safway » Tue Jul 15, 2008 5:00 pm

There are many standards/variants of SQL (Structured Query Language). Oracle is merely one of them. The "AS" do work for specific reasons in other variants of SQL such as MySQL (http://dev.mysql.com/doc/refman/5.0/en/ ... alias.html), Postgres (http://sql-info.de/postgresql/postgres-gotchas.html), HSQL, etc.

I have also worked a little with Oracle and it does have its virtues; however, as your title says it is "bad for Oracle," but Oracle is not the only database. :-)

Regarding the check box "Use keyword AS before table alias names" being disabled, possibly worthy of a bug report.

Anyway, glad you got it.
If someone posts a fix for your question, then please post a quick thank you and then go to your first post, use the edit button to add [Solved] as the first word of the title so other people can rely on the fix as well.
LibreOffice 3.3.3 on Fedora
Safway
Volunteer
 
Posts: 347
Joined: Thu Apr 24, 2008 4:35 pm

Re: Report Wizard inserts table alias using AS - bad for Oracle

Postby elmicha » Tue Jul 15, 2008 8:17 pm

Thank you for your help, Safway.

I'm aware that there are a lot of different databases, and Oracle-SQL is not exactly the most ANSI SQL embracing dialect. They just recently (Oracle 9i) learnt about ANSI left join syntax, after all.

But at work I'm stuck with Oracle. Converting many databases with views, triggers, database links, and lots of PL/SQL just is not going to happen.

So I submitted http://www.openoffice.org/issues/show_bug.cgi?id=91679.

Regards...
Michael
OOo 2.4.X on Linux-Other
elmicha
 
Posts: 3
Joined: Mon Jul 14, 2008 10:09 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest