Re: Static Select statements

<sybrandb_at_hccnet.nl> schreef in bericht news:ifmav3thsi29mtldvscrpdr85a6ccs624s_at_4ax.com...
> On Mon, 31 Mar 2008 05:03:21 -0400, Ubiquitous <weberm_at_polaris.net>
> wrote:
>
>>bigbuck714_at_aol.com wrote:
>>>On Mar 28, 3:19 pm, Ubiquitous <web..._at_polaris.net> wrote:
>>
>>>> Is there a way to force a static row in a SELECT statement?
>>>>
>>>> For example, I have a SELECT statement which is a UNION of two
>>>> identical
>>>> tables, "A" and "B". How would one create a column which identifies the
>>>> source table?
>>>>
>>>> TABLE NAME REC#
>>>> ----- ---- ----
>>>> A Bob 001
>>>> A Tim 002
>>>> B Joe 001
>>>> A Sue 003
>>>
>>>This should work:
>>>
>>>Select 'A' as 'TABLE', name, recno
>>>from a
>>>UNION
>>>select 'B' as 'TABLE', name, recno
>>>from b
>>
>>Thank you! That's exactly what I was seeking!
>>The 'TABLE' literal should be enclosed with double quoyes ("), however.
>
> Incorrect.
>
> Sybrand Bakker
> Senior Oracle DBA

No, it is correct.

"TABLE" should be in double qoutes indeed, because it is a reserved word. Normally, the column alias should be either without quotes, or with double quotes. Single quotes don't work here.

SQL> select 'a' as "table", 1 from dual;

t 1
- ----------
a 1

SQL> select 'a' as table, 1 from dual;
select 'a' as table, 1 from dual

              *
FOUT in regel 1:
.ORA-00923: FROM-sleutelwoord is niet gevonden waar verwacht.

SQL> select 'a' as 'table', 1 from dual; select 'a' as 'table', 1 from dual

              *
FOUT in regel 1:
.ORA-00923: FROM-sleutelwoord is niet gevonden waar verwacht.

SQL> select 'a' as non_reserved_word, 1 from dual;

N 1
- ----------
a 1

SQL> select 'a' as "non_reserved_word", 1 from dual;

n 1
- ----------
a 1

SQL> select 'a' as 'non_reserved_word', 1 from dual; select 'a' as 'non_reserved_word', 1 from dual

              *
FOUT in regel 1:
.ORA-00923: FROM-sleutelwoord is niet gevonden waar verwacht.

SQL> Shakespeare Received on Fri Apr 04 2008 - 02:52:55 CDT