Re: add sysdate to column name

From: <fitzjarrell_at_cox.net>
Date: Thu, 27 Sep 2007 10:45:49 -0700
Message-ID: <1190915149.139744.103290@50g2000hsm.googlegroups.com>

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 Received on Thu Sep 27 2007 - 12:45:49 CDT