Apache derby table alias

[ceyesuma]

I can't seem to find the correct way to add an alias in a select statement.
Is there a way to do this using an embedded Apache derby database?

The usual way will not work.
ex: SELECT uid AS User,fName AS First,lName AS Last FROM table

[CrazyDieter]

Did you try to remove the 'as' keywords, some sql engines support this.

SELECT uid User,fName First,lName Last FROM table

[~s.o.s~]

Which version of Derby DB are you using? I can make those statements work perfectly fine with Derby 10.5.

BTW, is the name of your table actually "table"?

[ceyesuma]

Which version of Derby DB are you using? I can make those statements work perfectly fine with Derby 10.5.

db-derby105.3.0-bin

BTW, is the name of your table actually "table"?

no. I can't even imagine the confusion that would cause. My bad. Bad choice of words

SELECT uid User,fName First,lName Last FROM table

I tried that statement and it did not encounter any errors yet it did not change the table name either. Thanks, I'm not sure how that is.

[ceyesuma]

Hello -s.o.s-

Is this the same apache derby embedded db version you use?
Thank you for your time.

[~s.o.s~]

Yes, it's the same major version I'm using (10.5.1.1). Paste your table creation script with the "exact" query you are trying to execute; I might just give it a try. For the time being, download 10.5.1.1 instead of using 10.5.3.0 and try out your script on that version. If it works, then you'd have to take this to the Derby forums.

[ceyesuma]

Thanks:
Here is the table and the query.
I will have to figure out how to replace 10.5.3.0 with the new version.Just replace the file and use the new driver name to create db?

<entry key="createAdminTable"> CREATE TABLE admin (    
    admin_uid VARCHAR(11) PRIMARY KEY,
    admin_password VARCHAR(11),
    admin_lname VARCHAR(20),
    admin_mname VARCHAR(20),
    admin_fname VARCHAR(20),
    admin_gender VARCHAR(1),
    admin_dob DATE,
    admin_start_date DATE,
    admin_end_date DATE,
    admin_address VARCHAR(20),
    admin_state VARCHAR(2),
    admin_zip VARCHAR(5),
    admin_area_code VARCHAR(3),
    admin_phone VARCHAR(10),
    admin_pay_rate DECIMAL(5,2),
    admin_email VARCHAR(90) ) </entry>

select statement : SELECT
    admin_uid,
    admin_password,
    admin_lname,
    admin_mname,
    admin_fname,
    admin_gender,
    admin_dob,
    admin_start_date,
    admin_end_date,
    admin_address,
    admin_state,
    admin_zip,
    admin_area_code,
    admin_phone,
    admin_pay_rate,
    admin_email
    FROM admin

[~s.o.s~]

That query you posted doesn't have any use of 'AS'? Post the query which doesn't work for you.

> I will have to figure out how to replace 10.5.3.0 with the new version

Use the new 'bin' folder when starting the server and use the new client libraries when accessing the server using JDBC.

[ceyesuma]

select statement : SELECT
    admin_uid,
    admin_password,
    admin_lname AS LAST,
    admin_mname,
    admin_fname,
    admin_gender,
    admin_dob,
    admin_start_date,
    admin_end_date,
    admin_address,
    admin_state,
    admin_zip,
    admin_area_code,
    admin_phone,
    admin_pay_rate,
    admin_email
    FROM admin 

I am not sure what is needed. I was just planning on changing the script as so but the usual way will not work. It encounters unknown at 'AS'.

[~s.o.s~]

When it comes to Derby, LAST has a special meaning. Instead of using 'LAST' as your column alias, use something like LNAME; it should work.

SELECT admin_lname as lname from admin

[ceyesuma]

thanks
that does work.

SELECT 
admin_uid,
admin_password,
admin_fname AS FNAME,
....

I was just looking at the wrong table output.(for hours and hours)
I lost track of the EXACT query.
thanks.