How to select an unaliased numeric literal from a sub-select

Question

I'm wondering if there is a general SQL syntax allowing for selecting unaliased numeric literals from sub-selects:

-- Seems to work in MySQL / Oracle
select table_alias."1"
from (
   select 1 from dual
) table_alias

I know I could alias the fields in the subselect:

-- Works everywhere
select table_alias.column_alias
from (
   select 1 column_alias from dual
) table_alias

But what if I don't have control over the subselect? Also, some RDBMS allow to provide both table AND column aliases when aliasing tables:

-- Seems to work in Postgres / SQL Server
select table_alias.column_alias
from (
  select 1 from dual
) table_alias (column_alias)

But some RDBMS (e.g. MySQL) can't do that. Is there another way?

Note: This isn't about any specific RDBMS, but just SQL in general
Note: I'd like to omit the asterisk, i.e. no select *...

A related question is this one here:

Is there a generic workaround to express a derived column list in Oracle (and MySQL)?

@Randy, I forgot to mention that I want to omit the asterisk — Lukas Eder, Jul 12 '11 at 18:26
Is this MySQL or SQL Server (or something else)? In SQL Server, you have to give your columns an alias when using derived tables. — Jerad Rose, Jul 12 '11 at 18:28
@Jerad: Like the third example? That's also valid in Postgres, for instance... — Lukas Eder, Jul 12 '11 at 18:29
If its a hard wired literal, why even have it in the sub query in the first place? — Limey, Jul 12 '11 at 18:38
@Limey: Consider this to be a rather academic question about syntax in general — Lukas Eder, Jul 12 '11 at 18:39

score 1 · Answer 1 · answered Jul 12 '11 at 18:25

1

In Oracle, you can do

select aa."2" from ( select 2 from dual ) aa

answered Jul 12 '11 at 18:25

Kal

24,724
7
65
65

I had the impression that this didn't work, for instance with 10g...? – Lukas Eder Jul 12 '11 at 18:27
I actually tried it and made sure. Not really sure what you would use it for , but it does work. – Kal Jul 12 '11 at 18:29
You're right, it does work in Oracle. I'm sorry, the question was maybe a bit weird. This is for syntax abstraction in a database framework. I need to know the general rule for these sorts of use cases. Or the per-RDBMS rules – Lukas Eder Jul 12 '11 at 18:32

score 1 · Accepted Answer · edited Sep 05 '12 at 19:18

1

According to the ANSI-92 standard it is implementation dependent. From section 7.9, 9.c:

Otherwise, the <column name> of the i-th column of the is implementation-dependent and different from the <column name> of any column, other than itself, of a table referenced by any <table reference> contained in the SQL-statement.

In other words, it's all going to depend on the RDBMS that you're using at the time.

BTW, you can check out the ANSI-92 standards if you're looking for some fun reading.

edited Sep 05 '12 at 19:18

Lukas Eder

211,314
129
689
1,509

answered Jul 12 '11 at 18:51

Tom H

46,766
14
87
128

Found it, thanks for the pointer. I know this document, but I clearly don't know how to read it when it comes to such detail ... :-) Alright, so I have no luck. There isn't even a way to access columns by that `i` index? I have never seen such an access-by-index syntax, so I don't believe there is... – Lukas Eder Jul 12 '11 at 18:54

How to select an unaliased numeric literal from a sub-select

2 Answers2

Linked