To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=66837
------- Additional comments from [EMAIL PROTECTED] Thu Jun 29 09:26:19 -0700
2006 -------
Not sure which database engine you are working with, but if it is the embedded
HSQL then this may be a limitation of the engine.
If I have a table of school information with address fields and try to run this
statement directly in HSQL ( SQL direct mode TRUE ) HSQL will generate a column
not found error:
SELECT
"ID",
"NAME",
CONCAT(CONCAT("ADD1", ' ' ), "ADD2") AS "ADDRESS" ,
SUBSTRING( "ADDRESS", 1, 3 ) AS "STREET NUM"
FROM "SCHOOLS"
However, if I change this to a sub-select it will run as expected - I presume
since the outer select statement now has visibility of the aliased column name:
SELECT
"ID",
"NAME",
"ADDRESS",
SUBSTRING( "ADDRESS", 1, 3 ) AS "STREET NUM"
FROM (
SELECT
"ID",
"NAME",
CONCAT(CONCAT("ADD1", ' ' ), "ADD2") AS "ADDRESS"
FROM "SCHOOLS"
)
The problem with this, at the moment, is that this type of query will not work
properyly with the form or report wizard. Should that change, then I would not
see why Base wouldn't be adequate as
The same holds true for MySQL by the way. This query will fail:
SELECT
`EmployeeID`,
`DepartmentID`,
CONCAT( `FirstName`, ' ' , `MiddleName`, ' ', `LastName`) AS FULL_NAME,
SUBSTR( `FULL_NAME`, 1, 3 ) AS FOO
FROM `openoffice`.`employees` `employees`
This will run as expected:
SELECT
`EmployeeID`,
`DepartmentID`,
`FULL_NAME`,
SUBSTR( `FULL_NAME`, 1, 3 ) AS FOO
FROM (
SELECT
`EmployeeID`,
`DepartmentID`,
CONCAT( `FirstName`, ' ' , `MiddleName`, ' ', `LastName`) AS FULL_NAME
FROM `openoffice`.`employees` `employees`
) TMP1
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
