| Column Alias [message #6532] |
Wed, 23 April 2003 10:53  |
Michele
Messages: 77
Registered: December 2000
|
Member |
|
|
Hello everyone,
I have a select query that looks like the below:
select terr_manpower.territory,last_name || ',' || first_name AS Name,sum(decode(to_char(creation_date,'MON'),'NOV',count,0 )) NOV,
sum(decode(to_char(creation_date,'MON'),'DEC',count,0 )) DEC
from terr_manpower, cntrctr
where cntrctr.rep_status = '1' and cntrctr.territory = terr_manpower.territory group by terr_manpower.territory,last_name || ',' || first_name;
However, I would like the Nov and Dec headings to display the text plus the actual date value for that month.
ie:
sum(decode(to_char(creation_date,'MON'),'NOV',count,0 )) NOV || creation_date,
The report would look like this:
Territory Name Nov 12-NOV-03, Dec 15-DEC-03
40 Smith,John 10 15
Can this be done?
Thanks
Michele
|
|
|
|
| Re: Column Alias [message #6546 is a reply to message #6532] |
Thu, 24 April 2003 05:24  |
Keith
Messages: 88
Registered: March 2000
|
Member |
|
|
The only way I know to do this would be pretty nasty......
You'd basically have to do a select statement before your main query to setup the column name correctly, and then reference this column name in the main query.
Assuming you're in SQL*Plus, here's an example:
krjf@ora92> set verify off
krjf@ora92> column nov new_value nov_col
krjf@ora92> select 'NOV '||sysdate nov from dual;
NOV
----------------------
NOV 24-APR-03
krjf@ora92> select 'Smith, John' name, to_char(sysdate, 'dd Month yyyy') "&nov_col" from dual
2 ;
NAME NOV 24-APR-03
----------- --------------------------------------------
Smith, John 24 April 2003
krjf@ora92>
HTH
keith
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
