 | |
Static Select statements
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Static Select statements -
04-03-2008
, 05:28 PM
|
bigbuck714 (AT) aol (DOT) com wrote: On Mar 28, 3:19*pm, Ubiquitous <web... (AT) polaris (DOT) 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. |
Sybrand Bakker
Senior Oracle DBA
#12
| |||
| |||
|
|
bigbuck714 (AT) aol (DOT) com wrote: On Mar 28, 3:19*pm, Ubiquitous <web... (AT) polaris (DOT) 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. |
Sybrand Bakker
Senior Oracle DBA
#13
| |||
| |||
|
|
bigbuck714 (AT) aol (DOT) com wrote: On Mar 28, 3:19*pm, Ubiquitous <web... (AT) polaris (DOT) 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. |
Sybrand Bakker
Senior Oracle DBA
#14
| |||
| |||
|
|
On Mon, 31 Mar 2008 05:03:21 -0400, Ubiquitous <weberm (AT) polaris (DOT) net wrote: bigbuck714 (AT) aol (DOT) com wrote: On Mar 28, 3:19 pm, Ubiquitous <web... (AT) polaris (DOT) 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 |
"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
#15
| |||
| |||
|
|
On Mon, 31 Mar 2008 05:03:21 -0400, Ubiquitous <weberm (AT) polaris (DOT) net wrote: bigbuck714 (AT) aol (DOT) com wrote: On Mar 28, 3:19 pm, Ubiquitous <web... (AT) polaris (DOT) 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 |
"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
#16
| |||
| |||
|
|
On Mon, 31 Mar 2008 05:03:21 -0400, Ubiquitous <weberm (AT) polaris (DOT) net wrote: bigbuck714 (AT) aol (DOT) com wrote: On Mar 28, 3:19 pm, Ubiquitous <web... (AT) polaris (DOT) 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 |
"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
#17
| |||
| |||
|
|
On Mon, 31 Mar 2008 05:03:21 -0400, Ubiquitous <weberm (AT) polaris (DOT) net wrote: bigbuck714 (AT) aol (DOT) com wrote: On Mar 28, 3:19 pm, Ubiquitous <web... (AT) polaris (DOT) 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 |
"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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.