SELECT "company_name", DATEDIFF( 'DD', '1899-12-30', TO_CHAR( CURDATE( ), 'YYYY-MM-' ) + "integer" ) AS "Zahltag" FROM "company", "integer" UNION SELECT ALL "company_name", DATEDIFF( 'DD', '1899-12-30', TO_CHAR( CURDATE( ), 'YYYY-MM-' ) + "payday2" ) AS "Zahltag" FROM "company" The above works fine as a query but only the first select will be accepted as a view. The UNION SELECT will trigger the error "The given command is not a select statement. Only queries are allowed."
@Rolf : which db backend are you using ?
HSQLDB embedded
Have tested this also. Build two tables for the content I guessed by the given example. Copied the query in a Base-query. Could only execute the query in direct SQL-Mode, not in GUI-Mode. Think this is what you mean with "query works fine ...". Now I took the query and created a view from this query. View works the same way the query works. So I could not confirm the described bug. My System: OpenSUSE 13.2, 64bit rpm Linux, different LO-versions
@Rolf : as with other bugs you have reported, you are using 4.2.8 provided by Ubuntu. TDF builds of the 4.2.8 line are EOL since Jan 2015. Please update your version of LO to a current TDF production release, test again and report back here, or else close this bug and file a report against the Ubuntu provided version
I can actually save the view when I choose to run SQL directly even in 4.2.8.2. But open opening the view in 4.2.8.2 produces an error of "The content could not be loaded - Wrong datatype: java.lang.illegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff] in statement [SELECT * FROM "VIEW1"] The UNION SELECT opens fine even as a view in 4.4.5, so this problem is indeed fixed in the latest release. Thanks.