carlosdl   29830 pts.  |   Jan 27 2006  12:00PM GMT

DaveInAZ, I think this is not valid in some DBMS (any ?). In Oracle 8i you can’t use this kind of operation as an alias for a selected column.

I would like to know if that’s possible in Oracle 9i or 10g (other, e.g. Sql Server 2005).

ws8oBPSQ:
With PL/SQL you can use a procedure. Something like this:

create or replace PROCEDURE prb_calen (fromdate IN date,todate IN date, dept in number) IS
line varchar2(500):= ‘EMP_NAME’;
inc number := 0;
BEGIN
loop
line := line||to_char(fromdate+inc,’ DD-MM-YYYY’);
inc := inc + 1;
exit when fromdate+inc > todate;
end loop;
dbms_output.put_line(line);
for i in (select emp_name from cdremp where dept_code = dept ) loop
dbms_output.put_line(i.emp_name);
end loop;
END;

That’s the idea, but I guess there is a limit of 255 bytes per line, and that’s not enough.

I hope this helps,

welcome   0 pts.  |   Jan 30 2006  5:57AM GMT

Hi,
You may try a select like the following:

select ‘EMP_NAME’,
to_date(’&from_date’,'dd-mm-rrrr’) DAY1,
to_date(’&from_date’,'dd-mm-rrrr’)+1 DAY2,
to_date(’&from_date’,'dd-mm-rrrr’)+2 DAY3,
to_date(’&from_date’,'dd-mm-yyyy’)+3 DAY4,
–up to date 31
to_date(’&from_date’,'dd-mm-yyyy’)+29 DAY30,
to_date(’&from_date’,'dd-mm-yyyy’)+30 DAY31
from DUAL
UNION ALL
select emp_name,
to_date(null) DAY1,
— repeat this 31 times
to_date(null) DAY31
from cdr_employees
where dept_code = ‘&dept’
and (sysdate between to_date(’&from_date’,'dd-mon-yyyy’) and to_date(’&to_date’,'dd-mon-yyyy’))
group by emp_name
ORDER BY 2,1 — this makes the title line appear first
/

That is, generate the first (title) line using a
select from DUAL.
If you run this in SQL*PLUS, then you should suppress
the headings, because they are generated by the select
itself.

Also, alternatively, if you use SQL*PLUS, then you may execute a
SELECT from DUAL separately, just to generate the title
DATES and store them in SQL*PLUS variables,
then use those variables in COLUMN statements for your
emplyees select, for example:

COLUMN DAY1 NEW_VALUE DAY1 ;
–
COLUMN DAY31 NEW_VALUE FAY31
SELECT to_date(’&from_date’,'dd-mm-rrrr’) DAY1,
–
to_date(’&from_date’,'dd-mm-rrrr’)+30 DAY31
FROM DUAL
/

COLUMN D1 HEADING ‘&DAY1′
–
COLUMN D31 HEADING ‘&DAY31′
select emp_name,
to_date(null) DAY1,
— repeat this 31 times
to_date(null) DAY31
from cdr_employees
where dept_code = ‘&dept’
and (sysdate between to_date(’&from_date’,'dd-mon-yyyy’) and to_date(’&to_date’,'dd-mon-yyyy’))
group by emp_name
/

From the “WHERE” clause it seems that you are using
ANY period (from_date, to_date), so that
SYSDATE is inside it.
However, the query selects a FIXED number of columns
( here 1 + 31, that is, for a single month ).
If you want a query generating a variable number of columns, then I think you should use some kind of dynamic
SQL.

Hope this could help.

Best Regards,
Iudith