Home » SQL & PL/SQL » SQL & PL/SQL » how to use functions in alias names
how to use functions in alias names [message #240524] Fri, 25 May 2007 02:54 Go to next message
nawshot
Messages: 12
Registered: May 2007
Junior Member
hi,

pls can u tell how to use functions in alias names.
for example:

i want to have 4 columns, whos names are to be
24-may, 25-may, 26-may, 27-may.


24-may 25-may 26-may 27-may
------- -------- ------- -------
a b c d
e f g h


i write it as,

select col_1 to_char('24-may-07','dd-mon'),
col_2 to_char('25-may-07','dd-mon'),
col_3 to_char('26-may-07','dd-mon'),
col_4 to_char('27-may-07','dd-mon')
from(
select 'a' col_1,'b' col_2,'c' col3,'d' col4 from dual
union all
select 'e' col_1,'f' col_2,'g' col3,'h' col4 from dual);


but its not working . Sad

pls help me in getting this.
thanks in advance.

regards,
naw
Re: how to use functions in alias names [message #240525 is a reply to message #240524] Fri, 25 May 2007 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just:
select col_1 "24-may",
col_2 "25-may",
col3 "26-may",
col4 "27-may"
from(
select 'a' col_1,'b' col_2,'c' col3,'d' col4 from dual
union all
select 'e' col_1,'f' col_2,'g' col3,'h' col4 from dual);

Regards
Michel

[Updated on: Mon, 28 May 2007 00:15]

Report message to a moderator

Re: how to use functions in alias names [message #240547 is a reply to message #240524] Fri, 25 May 2007 03:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd have to recommend that you don't do that.
If you give columns names that require you to wrap then in " " every time, then you will be seriously inconveniencing the deveopers who follow you.

Just call the columns MAY_25, MAY_26, MAY_27 and be done with it.

Re: how to use functions in alias names [message #240576 is a reply to message #240547] Fri, 25 May 2007 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe it is just in purpose to display pretty names in column headers without using "column" statement.
In this case, this does not hurt.
Of course if the names are used elsewhere then it is a bad practice.

Regards
Michel
Re: how to use functions in alias names [message #240581 is a reply to message #240576] Fri, 25 May 2007 04:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fair point, but if you're producing a report in SQL*Plus, how important can it be?
Re: how to use functions in alias names [message #240583 is a reply to message #240581] Fri, 25 May 2007 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you mean how important column headers are?
I like pretty column headers. Razz

Regards
Michel
Re: how to use functions in alias names [message #240936 is a reply to message #240581] Mon, 28 May 2007 00:01 Go to previous messageGo to next message
nawshot
Messages: 12
Registered: May 2007
Junior Member
hi
thks all for ur reply

but my actual req. is to produce a report, which dynamically have to change the column names with the given dates.

this is actually producing a weekly report. with all dates in a given week have to be displayed and also total in diff col.

pls reply.help me out.


thks and regards,
naw
Re: how to use functions in alias names [message #240943 is a reply to message #240936] Mon, 28 May 2007 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are several ways depending on your environment.
Explain yours and post a test case corresponding.

Regards
Michel
Re: how to use functions in alias names [message #241225 is a reply to message #240943] Mon, 28 May 2007 22:56 Go to previous messageGo to next message
nawshot
Messages: 12
Registered: May 2007
Junior Member
my query is,
i need to generate a report for a week( that can be any week). the column names should be extracted from the dates itself.

i tried using "column <> heading to_char('11-may-07','dd-mon')" but it doesn't work.

then i tried, in select statement itself,
select col1 to_char(TO_DATE('&START_DATE'),'DD-MON') from(
-----
--------
----
);

but this also not working.

can u pls help me out.

regards,
naw
Re: how to use functions in alias names [message #241229 is a reply to message #241225] Mon, 28 May 2007 23:26 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col col1 new_value col1
SQL> col col2 new_value col2
SQL> col col3 new_value col3
SQL> col col4 new_value col4
SQL> def start_date="24-may-2007"
SQL> select to_char(to_date('&start_date','DD-mon-YYYY'),'DD-mon') col1,
  2         to_char(to_date('&start_date','DD-mon-YYYY')+1,'DD-mon') col2,
  3         to_char(to_date('&start_date','DD-mon-YYYY')+2,'DD-mon') col3,
  4         to_char(to_date('&start_date','DD-mon-YYYY')+3,'DD-mon') col4
  5  from dual
  6  /
COL1   COL2   COL3   COL4
------ ------ ------ ------
24-may 25-may 26-may 27-may

1 row selected.

SQL> select 'aaaaaaa' "&col1", 'bbbbbb' "&col2", 'cccccc' "&col3", 'dddddd' "&col4"
  2  from dual
  3  /
24-may  25-may 26-may 27-may
------- ------ ------ ------
aaaaaaa bbbbbb cccccc dddddd

1 row selected.

Of course, in your report you enclose the first query between "set termout off" and "set termout on" and/or start the spool after it to avoid seeing it in your report.

Regards
Michel
Previous Topic: Indexes
Next Topic: How to insert Folder path name?
Goto Forum:
  


Current Time: Thu Dec 08 02:29:39 CST 2016

Total time taken to generate the page: 0.10602 seconds