[SOLVED] SQL Alias problem... point out my error please.

struct · 01-17-2011, 01:42 PM

Hi Everyone,
I'm having some difficulties understanding why this isn't working. why would some alias work and some doesn't? Could someone point out what I'm doing wrong?
While not using Alias for t1.PARENT and t2.PROJECT_ID, it works without any errors.

without alias for t1.PARENT & t2.PROJECT_ID

Code:

SELECT t1.PARENT, t2.PROJECT_ID, t2.PROJECT_NAME AS PARENT_NAME
FROM TABLE1 t1 INNER JOIN TABLE1 t2
ON t1.PARENT = t2.PROJECT_ID
ORDER BY PARENT_NAME ASC

using alias PARENTID & PROJECTID

Code:

SELECT t1.PARENT AS PARENTID, t2.PROJECT_ID AS PROJECTID, t2.PROJECT_NAME AS PARENT_NAME
FROM TABLE1 t1 INNER JOIN TABLE1 t2
ON PARENTID = PROJECTID
ORDER BY PARENT_NAME ASC

causes

Code:

ERROR: An undefined column or parameter name was detected.

paulsm4 · 01-17-2011, 02:52 PM

Hi -

This might make it clearer (mySQL 4.1 or higher, MSSQL 7.0, Oracle 8i or higher):

Code:

select c1.center_name, c2.address_city from
    -> centers c1 inner join center_addresses c2
    -> on c1.center_id=c2.center_id;
+-------------------------------------+----------------------+
| center_name                         | address_city         |
+-------------------------------------+----------------------+
| 300 Bowl                            | Albany               |
| 300 Bowl                            | Medford              |
| XYZ   Lanes                         | Waterford            |
...

Code:

select c1.center_name as 'Customer', c2.address_city as 'City'
    ->   from centers c1 inner join center_addresses c2
    ->   on c1.center_id=c2.center_id;
+-------------------------------------+----------------------+
| Customer                            | City                 |
+-------------------------------------+----------------------+
| 300 Bowl                            | Albany               |
| 300 Bowl                            | Medford              |
| XYZ   Lanes                         | Waterford            |
...

PS:
You can get rid of the syntax error simply by quoting 'ProjectID', 'ParentID', etc. I'm just not sure that's what you were looking for

cheesus · 01-17-2011, 03:04 PM

Well, I believe this is no standard SQL, so it depends on your database server (you didn't
say which...). I believe PostgreSQL can do that, MS SQL Server can't...
Cheers, Tom...

Tinkster · 01-17-2011, 03:15 PM

Standard SQL doesn't allow for an alias that aliases both table & column.
You can do EITHER, but not BOTH.

struct · 01-17-2011, 03:31 PM

Paulsm4,
thank you for taking your time to illustrate that. Unfortunately putting a single quote around the alias causes same error in my system. Your method worked because you joined the columns with tableAlias.column name instead of column Alias.

Cheesus,
I'm on DB2.

Tinkster,
Your answer is sound but ORDER BY PARENT_NAME works on my system. So... I don't think your answer applies here.

Anyways, I decided to go with Paulsm4's method. Thank you for giving me your valuable input.