Issue 66837 - HSQL improvement: Cannot use column aliases as variables in queries
Summary: HSQL improvement: Cannot use column aliases as variables in queries
Status: UNCONFIRMED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.1
Hardware: All All
: P3 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2006-06-28 21:43 UTC by jwt
Modified: 2013-08-07 15:45 UTC (History)
1 user (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description jwt 2006-06-28 21:43:06 UTC
It is not possible to, say, to assemble a date string AS "dte" and then use it
in a function, e.g. MONTHNAME("dte")
Comment 1 drewjensen.inbox 2006-06-29 17:26:19 UTC
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
Comment 2 jwt 2006-06-29 21:31:09 UTC
Yes, that is the problem.  I have got a workaround, but like yours, it is
clumsy.  I am using the inbuilt HSQL database.  Previously I have used access in
which using column aliases as variables is straightforward and very convenient.
Comment 3 christoph.lukasiak 2006-07-05 10:11:50 UTC
this is a valid feature enhancement => send further to the requirement team