Re: SELECT 42 AS a, a AS b FROM system.iota

dtmackenzie <david.mackenzie@xxxxxxxxxxxx> wrote:
Yes, that's what I thought, but by reducing an example, it can
become unclear what you want to achieve. Your question is
kind of ambiguous : it can be interpreted as "can I give an item
in the select list the same name as the name of a column", while you
mean "can I make a reference to an item in the select list further in the
query by using the name I gave it" :-).

What you want doesn't work in ASE (spid is a columnname of sysprocesses) :

select spid as a, a as b from sysprocesses;
Msg 207, Level 16, State 4
Server 'DSP_ST_1_b', Line 1
Invalid column name 'a'.

Luc.

Hello Luc, the ambiguity didn't occur to me, probably because in ADS
it is unambiguous, but I see that you understand now what I meant.
However, I don't understand why Michael's example works in ASE - if
spid is a column of sysprocesses then I would expect an error already
with "SELECT 42 AS spid...".

No, because you can indeed give the column a name in the result
set equal to the name of an existing column.

What you can't do is giving 2 columns the same name, so

select suid as spid, spid from sysprocesses

doesn't work :

Msg 7348, Level 15, State 1
Server 'DSP_ST_1_a', Line 1
Select expression results in more than one column having same name. Column
name 'spid' is specified more
than once

but

select suid as spid, spid as suid from sysprocesses

works :-).

Of course this is confusing. the 'as <name>' is only used in
the result set (to be used by the program who executed the
query) and nowhere else, that's why your example doesn't work.

when I execute :

select suid as spid, spid as suid from sysprocesses
where spid = 54

I get :

spid suid
----------- ------
4979 54

Luc.





Anyway, if what I want doesn't work in ASE either then I don't suppose
there's much chance of Sybase supporting it in ADS, even though I
still believe that it works in SQL Anywhere (or at least used to when
I last worked with it, about 8 years ago using PowerBuilder). I can
understand this in a way - it's not quite so easy because e.g.
circular definitions have to be avoided. Nevertheless it's a pity and
rather inconvenient - I'd still be interested to hear what the SQL
standard has to say about it.

In any case, thanks for taking an interest!
David
.

Relevant Pages

  • Re: SELECT 42 AS a, a AS b FROM system.iota
    ... What you want doesn't work in ASE (spid is a columnname of sysprocesses): ... Hello Luc, the ambiguity didn't occur to me, probably because in ADS ...
    (comp.databases.sybase)
  • Re: SELECT 42 AS a, a AS b FROM system.iota
    ... Luc, system.iota in ADS has only one row and one column, which is also ... What you want doesn't work in ASE (spid is a columnname of sysprocesses): ...
    (comp.databases.sybase)
  • Re: Help with blocking
    ... I don't think parallel queries cause multiple rows in sysprocesses. ... see multiple entries in sysprocesses related to same SPID when lightweight ... > same SPID with different values in the ecid column for each process. ...
    (microsoft.public.sqlserver.server)
  • sysprocesses table in Master db
    ... There is a column named "nt_username" in sysprocesses ... tells me the exact nt login name, ... what "spid" tells. ... Jeanny ...
    (microsoft.public.sqlserver.programming)
  • Re: Allowing only certain clients access to SQL Server 2K
    ... > select net_address from sysprocesses where spid = @spid ... >>I want to increase security on my SQL Server database by restricting ...
    (microsoft.public.sqlserver.security)