如何使用with...as...构造SQL语句
WITH num AS (SELECT rownum rn FROM cat WHERE rownum <= 31),
rec AS (SELECT decode(on_duty,'0','','1','√')||decode(rest_type,'0','','1','○')||
decode(absent_work,'0','','1','旷')||
decode(vacation_type_code,null,'', ( SELECT vacation_mark
FROM hr_c_vacationtype
WHERE vacation_type_code = wt.vacation_type_code ) )||
decode(overtime_type_code,null,'', ( SELECT overtime_mark
FROM hr_c_overtime
WHERE overtime_type_code = wt.overtime_type_code ) ) attend_state,
to_number(to_char(attendance_date,'dd')) attend_day
FROM hr_j_workattendance wt
WHERE to_char(attendance_date,'yyyy-mm') = v_year||'-'||v_month AND
worker_code = v_worker_code
ORDER BY attendance_date )
SELECT rec.attend_state FROM num, rec
WHERE (num.rn = rec.attend_day(+))
ORDER BY num.rn;
rec AS (SELECT decode(on_duty,'0','','1','√')||decode(rest_type,'0','','1','○')||
decode(absent_work,'0','','1','旷')||
decode(vacation_type_code,null,'', ( SELECT vacation_mark
FROM hr_c_vacationtype
WHERE vacation_type_code = wt.vacation_type_code ) )||
decode(overtime_type_code,null,'', ( SELECT overtime_mark
FROM hr_c_overtime
WHERE overtime_type_code = wt.overtime_type_code ) ) attend_state,
to_number(to_char(attendance_date,'dd')) attend_day
FROM hr_j_workattendance wt
WHERE to_char(attendance_date,'yyyy-mm') = v_year||'-'||v_month AND
worker_code = v_worker_code
ORDER BY attendance_date )
SELECT rec.attend_state FROM num, rec
WHERE (num.rn = rec.attend_day(+))
ORDER BY num.rn;
要注意,当需要with 多个临时结果时,必需写在一个with r_name as 。。。语句中,
不能写成 with a as (select * from table1)
with b as (select * from table2)
select a.*,b.*
from a,b
where a.id = b.id;
这样写是错误的。
正确的是:
with a as (select * from table1),
b as (select * from table2)
select a.*,b.*
from a,b
where a.id = b.id;