Page 1 of 1

Every derived table must have its own alias

Posted: Fri Apr 09, 2010 2:06 pm
by stmarco
Hello,

i'm working with the sun report builder version 1.2.0 and have the following problem

to populate the report i use this query to a MySQL database:
SELECT "user" AS "user", "werk" AS "werk", "DTSTART" AS "DTSTART","DTEND" AS "DTEND", TIMEDIFF(TIME(DTEND),TIME(DTSTART)) AS "TIMEDIF" FROM "werktimer" WHERE "DTEND" > '2001-01-01 00:00:00' ORDER BY "werk", "DTSTART"

this query works when i open it but when i want to launch the report i get the error: Every derived table must have its own alias

can anyone help me with this?
is there an alternative to the Sun Report Builder?

thanks

Marco

Re: Every derived table must have its own alias

Posted: Fri Apr 09, 2010 2:21 pm
by Villeroy
Alternatives to SRB:
- Disable the extension, restart the office and use the "classical" report wizard.
- Drag your row set into arbitrary Writer documents and import a data copy as plain text or text table.
- Drag your query or table into a spreadsheet. Calc provides sufficient capabilities for a clean print layout, cell based formatting, additional formulas and charts.
- Create a data pilot (cross table) in Calc. Calc-menu:Data>Pilot>Start... "From registered data source"
[Tutorial] Using registered datasources in Calc
Using the DataPilot

Re: Every derived table must have its own alias

Posted: Fri Apr 09, 2010 5:37 pm
by eremmel
What happens when you create a simple report in SRB directly from the table 'werktimer' or is 'werktimer' a query you defined in Base as well? In that case you might change your SQL to:

Code: Select all

SELECT X."user" as "user", ...
FROM "werktimer" X 
WHERE X."DTEND" ....

Re: Every derived table must have its own alias

Posted: Fri Apr 09, 2010 6:59 pm
by stmarco
The problem only occurs when you want to group in SRB. when you don't group there is no problem (for now)

i explored the option to put the query in a spreadsheet, here i could construct a formula that gave me the sum of the time values i needed, so i think i will go that way.

my original query was of the form select X."user" as "user" ... but this gave the same error.

thanks all for the answers