|
Home > Archive > SQL Anywhere database > April 2005 > column alias question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
column alias question
|
|
| Preston 2005-04-14, 8:23 pm |
| SELECT
I.*, V.VendorName as VendorLookup, V.RecordID as VendorRecordID
FROM
Items AS I LEFT OUTER JOIN Vendors AS V
ON I.VendorID = V.VendorID
where
recordid = 1
The above query gives "Column RecordID found in more than one table". Both
Items and Vendors have a recordID column. I aliased the vendors.RecordID so
why is the where clause getting confused?
| |
| Breck Carter 2005-04-14, 8:23 pm |
| Specifying a *column* alias does not prevent you from
referring to the original column name, so the WHERE clause
is ambiguous.
Breck
> SELECT
> I.*, V.VendorName as VendorLookup, V.RecordID as
> VendorRecordID FROM
> Items AS I LEFT OUTER JOIN Vendors AS V
> ON I.VendorID = V.VendorID
> where
> recordid = 1
>
>
> The above query gives "Column RecordID found in more than
> one table". Both Items and Vendors have a recordID
> column. I aliased the vendors.RecordID so why is the
> where clause getting confused?
>
>
| |
| Preston 2005-04-14, 8:23 pm |
| > Specifying a *column* alias does not prevent you from
> referring to the original column name
Is there something I can do to prevent it?
I know typing in the table name would be the best. However, the query is
being constructed by some third party components and they won't do that.
| |
| David Kerber 2005-04-14, 8:23 pm |
| In article <425ed27a.ad5.1681692777@sybase.com>, Breck Carter says...
> Specifying a *column* alias does not prevent you from
> referring to the original column name, so the WHERE clause
> is ambiguous.
Personally, I dislike this way of doing things, but have learned to live
with it. Do you know if this is characteristic of most major database
servers, or is is peculiar to ASA? ISTM that it would be more logically
consistent if you could only refer to a column by its alias.
Dave
>
> Breck
>
>
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Breck Carter [TeamSybase] 2005-04-15, 7:23 am |
| All I can think of is create a view on one of the tables, with a
different column name, and use that in the select.
Breck
On 14 Apr 2005 13:36:46 -0700, "Preston" <nospam@nospam.com> wrote:
>
>Is there something I can do to prevent it?
>I know typing in the table name would be the best. However, the query is
>being constructed by some third party components and they won't do that.
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Glenn Paulley 2005-04-22, 8:23 pm |
| David Kerber < ns_dkerber@ns_wraenv
iro.com> wrote in
news:MPG. 1cc89f42ee3139bc9897
37@forums.sybase.com:
> In article <425ed27a.ad5.1681692777@sybase.com>, Breck Carter says...
>
> Personally, I dislike this way of doing things, but have learned to
live
> with it. Do you know if this is characteristic of most major database
> servers, or is is peculiar to ASA? ISTM that it would be more
logically
> consistent if you could only refer to a column by its alias.
>
> Dave
>
>
>
ASA is the only product I am aware of that permits one to refer to an
aliased expression in the body of the same SELECT. This is not permitted
by ANSI either - an aliased expression can only be used in *outer* query
blocks, and it is the alias that is returned in a DESCRIBE.
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
| |
| David Kerber 2005-04-23, 9:23 am |
| In article < Xns9640B0DBA9FE3paul
leyianywherecom@10.22.241.106>,
paulley@ianywhere.com says...
....
> ASA is the only product I am aware of that permits one to refer to an
> aliased expression in the body of the same SELECT. This is not permitted
> by ANSI either - an aliased expression can only be used in *outer* query
> blocks, and it is the alias that is returned in a DESCRIBE.
Thanks, Glenn.
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
|
|
|
|
|