Issue 66837 - HSQL improvement: Cannot use column aliases as variables in queries
Summary: HSQL improvement: Cannot use column aliases as variables in queries
Status: CLOSED DUPLICATE of issue 121837
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: 2023-12-10 13:23 UTC (History)
2 users (show)

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

Attachments
Add an attachment (proposed patch, testcase, etc.)

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
Comment 4 oooforum (fr) 2023-12-09 10:06:22 UTC 
Already reported with Issue #49185 - Queries can't call macro functions
If you want real date functions, HSQLDB engine must be update to 2.x : #121837

*** This issue has been marked as a duplicate of issue 121837 ***