[Solved] SQL-Issue in order to create an report

[pete_porner]

Syntaxerror im SQL-Statement ./dbaccess/source/core/api/SingleSelectQueryComposer.cxx:108
I want to use an external counter. The internal reportcounter doesn't work that i want to.
OK - My idea
SELECT @a:=0;
SELECT @a:=@a+1, columns from mytable;
SELECT @a:=cast(@a as signed)+1, columns from mytable ;
In mariadb-console is everything ok - But if i send it through the report , i got the error from above.
select (@a:=@a+1) as positionen,'XXXXXXXXXXXXXXXXXXX' as adress,'von: 2017-09-10 bis: 2017-10-10' as zeitspanne,'xxxx' as kundennummer,'1020' as rechnungsnummer from erprechnungen left join erprechnungmeta on erprechnungmeta.kdnr=erprechnungen.kdnr where erprechnungen.erbrachtvon like 'xxxxxxxxxx' and erprechnungen.lieferdatum BETWEEN CAST('2017-09-10' AS DATE) AND CAST('2017-10-10' AS DATE);
Thanks for any advice.

[UnklDonald418]

According to your signature you are using a 15 year old version of OpenOffice, so I assume you are using the standard report writer that is the default with Base. There is a much better report writer, the Oracle-report-builder extension to OpenOffice (LibreOffice uses it as the default report writer)
https://extensions.openoffice.org/en/pr ... t-builder
Try that to see if it will display the results of your query.

If your query works in the Direct Mode in Base, another option would be to create a View from the query, then use the View as the data source for the report.

[pete_porner]

Ok, i don't know the signature of my officeversion. And i don't understand the option "view". My database is mariadb. Mariadb is able to create views, Base is not able to see these views.(I have tried anyway). Already Query's in directmode doesn't work. I have to solve the things with buildin stuff of the reportbuilder. It seems like a elegant method to write the content of one or more SQL-counter in the report. Would be a nice dream. Thanx

[Mountaineer]

Signature questions the last line in your posts here: It reads Ubuntu 9.04.

Are you still using the old Ubuntu 9.04 (Jaunty Jackalope) or did you upgrade.

You may also check the version of your office in the menu (of the office suite): Rightmost Menu (help), last entry is usually some "about this suite" - which would be LibreOffice on modern Ubuntu....

[Mountaineer]

.... Mariadb is able to create views, Base is not able to see these views.(I have tried anyway). Already Query's in directmode doesn't work. I have to solve the things with buildin stuff of the reportbuilder....

Can we start with analysing what is working?
You see the regular tables, can open them and their contents?
You can create a simple SQL-query like
SELECT kdnr FROM erprechnungen;
but switching to direct mode then fails?
Or did your query fail only with parameters like :Kunde ?

You will need to have a working query or view, to put the report on top.
From a query I can create a view (with Base) via the context-menu of the query (in the query tab of Base) - the view is then shown as a table. You may need to reconnect (close OpenOffice and re-open).

PS: You know the guide (Handbuch) for Base for LibreOffice. Recently updated to reflect changes up to 7.5 in the german version (I assume you can read german...)
https://www.familiegrosskopf.de/robert/ ... e_handbuch

On the official site, switch to german language before selecting menu Hilfe>Handbücher
https://de.libreoffice.org/get-help/documentation/

[pete_porner]

Thanx for your reply. Everything is working well on the mysql console. UnklDonald418 give me an advice. The problem is solved.
The SQL-Statement generated in Libreoffice was not suitable(mariadb grips), including in the direct mode. I push them with this code:
SET @:=0; or more counter, (i need it without auto_increment).
CREATE VIEW(TABLE) reportview as SELECT @a:=cast(@a as signed)+1, columns from mytable join...;
Report data=reportview
oReport = ThisDatabaseDocument.reportdocuments.getbyname("erp_report")
oReport.open
oReport.close(True)
But therefore i make a "view" and that's the way it works. And the code is more compact. Now i can
do, what i wanted to do.
Thanx to all.

[Villeroy]

menu:Tools>SQL...

Code: Select all

 CREATE VIEW reportview as SELECT * FROM `Table1`;

menu:View>Refresh Tables

[pete_porner]

Ok. Solved