Re: add sysdate to column name

On Sep 27, 1:45 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Sep 27, 12:05 pm, alex <sql_...@xxxxxxxxx> wrote:





On Sep 27, 12:52 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx>
wrote:

On Sep 27, 11:22 am, alex <sql_...@xxxxxxxxx> wrote:

On Sep 27, 12:12 pm, vitalis...@xxxxxxxxx (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

.

Relevant Pages

  • Re: performance question
    ... possibly by changing the spacing in the SQL ... IT Manager/Oracle DBA ... SELECT SYSDATE FROM DUAL; ...
    (comp.databases.oracle.server)
  • Re: add sysdate to column name
    ... something like select record as sysdate. ... SQL> create table mytest); ... SQL> insert into mytest ... Notice how I substituted the current date for the column header: ...
    (comp.databases.oracle.misc)
  • Re: add sysdate to column name
    ... something like select record as sysdate. ... SQL> column sysdate new_value sdate noprint ... SQL> insert into mytest ...
    (comp.databases.oracle.misc)
  • Re: add sysdate to column name
    ... something like select record as sysdate. ... SQL> column sysdate new_value sdate noprint ... SQL> insert into mytest ... David Fitzjarrell- Hide quoted text - ...
    (comp.databases.oracle.misc)