Re: add sysdate to column name

On Sep 27, 1:45 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Sep 27, 12:05 pm, alex <sql_..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Sep 27, 12:52 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> > wrote:
>
> > > On Sep 27, 11:22 am, alex <sql_..._at_yahoo.com> wrote:
>
> > > > On Sep 27, 12:12 pm, vitalis..._at_gmail.com (Jerome Vitalis) wrote:
>
> > > > > alex wrote:
> > > > > > Hello experts,
> > > > > > i'm attempting to add the sysdate to (not the record) but as the
> > > > > > column name.
> > > > > > something like select record as sysdate.
> > > > > > i'm getting an error, however.
> > > > > > is this possible?
>
> > > > > Yes, with something like this:
>
> > > > > select 'foo' "sysdate" from dual;
> > > > > -----
> > > > > échangez opinions et commentaires dans les forums de discussion.http://www.usenetgratuit.com/
>
> > > > "sysdate" is returning the name "SYSDATE"
> > > > i'm looking for the system's date to become the name of the column
>
> > > Possibly this is what you desire:
>
> > > SQL> column sysdate new_value sdate noprint
> > > SQL>
> > > SQL> select sysdate from dual;
>
> > > SQL>
> > > SQL> create table mytest(testcol varchar2(20));
>
> > > Table created.
>
> > > SQL>
> > > SQL> insert into mytest
> > > 2 values ('This is a test.');
>
> > > 1 row created.
>
> > > SQL>
> > > SQL> select testcol as "&sdate"
> > > 2 from mytest;
> > > old 1: select testcol as "&sdate"
> > > new 1: select testcol as "27-SEP-07"
>
> > > 27-SEP-07
> > > --------------------
> > > This is a test.
>
> > > SQL>
> > > SQL> drop table mytest;
>
> > > Table dropped.
>
> > > SQL>
> > > SQL> clear columns
> > > SQL>
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -
>
> > thanks for your help David.
>
> > it looks like your doing a substitution (&). is there a way to simply
> > provide a column alias with the sysdate? i'd like it as simple as
> > possible for the end user!- Hide quoted text -
>
> > - Show quoted text -
>
> No. Using the select as the alias produces:
>
> SQL> select testcol as (select sysdate from dual)
> 2 from mytest;
> select testcol as (select sysdate from dual)
> *
> ERROR at line 1:
> ORA-00923: FROM keyword not found where expected
>
> SQL>
>
> Notice how I substituted the current date for the column header:
>
> SQL> column sysdate new_value sdate noprint
> SQL>
> SQL> select sysdate from dual;
>
> SQL>
>
> This sets sdate's value to the current date. At that point one can
> use the value in most any way one likes, including as a column header:
>
> SQL> select testcol as "&sdate"
> 2 from mytest;
> old 1: select testcol as "&sdate"
> new 1: select testcol as "27-SEP-07"
>
> 27-SEP-07
> --------------------
> This is a test.
>
> SQL>
>
> As far as I know (and I've tested a number of options) my solution is
> the only one in SQL*Plus to provide the desired output. But, I do not
> have 11g installed. There may be some fantastic and ingenious new
> method to do just what you're asking.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

thanks David...i appreciate the time

alex Received on Thu Sep 27 2007 - 13:29:19 CDT