Prev: OCI "ping" function for 10.2?
Next: Oracle BI Training...!!!
From: Ubiquitous on 31 Mar 2008 05:03
bigbuck714(a)aol.com wrote:
>On Mar 28, 3:19�pm, Ubiquitous <web...(a)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.

From: sybrandb on 3 Apr 2008 18:28
On Mon, 31 Mar 2008 05:03:21 -0400, Ubiquitous <weberm(a)polaris.net>
wrote:

>bigbuck714(a)aol.com wrote:
>>On Mar 28, 3:19�pm, Ubiquitous <web...(a)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
From: Shakespeare on 4 Apr 2008 03:52

<sybrandb(a)hccnet.nl> schreef in bericht
news:ifmav3thsi29mtldvscrpdr85a6ccs624s(a)4ax.com...
> On Mon, 31 Mar 2008 05:03:21 -0400, Ubiquitous <weberm(a)polaris.net>
> wrote:
>
>>bigbuck714(a)aol.com wrote:
>>>On Mar 28, 3:19 pm, Ubiquitous <web...(a)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


 | 
Pages: 1
Prev: OCI "ping" function for 10.2?
Next: Oracle BI Training...!!!