Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!green.octanews.net!news-out.octanews.net!teal.octanews.net!nx01.iad01.newshosting.com!newshosting.com!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!feeder.news-service.com!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!transit.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail From: "Shakespeare" Newsgroups: comp.databases.oracle.misc References: <742e3b07-4192-443c-bf77-0d5e7e6d1099@a23g2000hsc.googlegroups.com> Subject: Re: Static Select statements Date: Fri, 4 Apr 2008 09:52:55 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3138 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 Lines: 89 Message-ID: <47f5de59$0$14349$e4fe514c@news.xs4all.nl> NNTP-Posting-Host: 82.95.215.210 X-Trace: 1207295577 news.xs4all.nl 14349 [::ffff:82.95.215.210]:49648 X-Complaints-To: abuse@xs4all.nl Xref: usenetserver.com comp.databases.oracle.misc:252348 X-Received-Date: Fri, 04 Apr 2008 02:52:58 EST (text.usenetserver.com) schreef in bericht news:ifmav3thsi29mtldvscrpdr85a6ccs624s@4ax.com... > On Mon, 31 Mar 2008 05:03:21 -0400, Ubiquitous > wrote: > >>bigbuck714@aol.com wrote: >>>On Mar 28, 3:19 pm, Ubiquitous 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