我有一个程序，我想比较带来的日期和带来所需的数据

Question

我在dbfiddle上写了所有细节。我有两个表，分别命名为GAZZETED_DAYS和PAY_IN_OUT，分别有列(GAZZETED_DATE，DESCRIPTION(和列(EMP_CODE，ATT_DATE(。请检查我已经粘贴在dbfiidle中的数据。我想要的输出是这样的

01-JAN-21   The Day of:     Present
02-JAN-21   The Day of:     Present
03-JAN-21   The Day of:     Present
04-JAN-21   The Day of:     Present
05-JAN-21   The Day of:     Present
06-JAN-21   The Day of:     Present
07-JAN-21   The Day of:     Present
08-JAN-21   The Day of:     Present
09-JAN-21   The Day of:     Its Holyday two
10-JAN-21   The Day of:     Present

当它与att_date进行比较时，它在所有具有范围的日期中都显示为这样

Answer 1

它应该是外部联接；像这样的东西：

SQL> declare
2    descr      varchar2(90);
3    gz_dt      date;
4    date1      date :=to_date('2021-01-01','YYYY-MM-DD');
5    date2      date :=to_date('2021-01-31','YYYY-MM-DD');
6    vatt_date  date;
7    vempcode   number;
8    cursor c_gzdt is
9      select g.gazzeted_date, g.description, p.att_date, p.emp_code
10      from pay_in_out p left join gazzeted_days g
11         on p.att_date = g.gazzeted_date
12        and gazzeted_date between date1 and date2
13        and p.emp_code=111
14      order by p.att_date;
15  begin
16    open c_gzdt;
17    loop
18      fetch c_gzdt into gz_dt, descr, vatt_date, vempcode ;
19      exit when c_gzdt%notfound;
20
21      if vatt_date = gz_dt then
22         dbms_output.put_line(vatt_date||' THE DAY OF : '||descr);
23      else
24         dbms_output.put_line(vatt_date||' THE DAY OF : '||'PRESENT');
25      end if;
26
27    end loop;
28    close c_gzdt;
29  end;
30  /

导致

01.01.21 THE DAY OF : PRESENT
02.01.21 THE DAY OF : PRESENT
03.01.21 THE DAY OF : PRESENT
04.01.21 THE DAY OF : PRESENT
05.01.21 THE DAY OF : PRESENT
06.01.21 THE DAY OF : PRESENT
07.01.21 THE DAY OF : PRESENT
08.01.21 THE DAY OF : PRESENT
09.01.21 THE DAY OF : Its Holyday two
10.01.21 THE DAY OF : PRESENT
11.01.21 THE DAY OF : PRESENT
12.01.21 THE DAY OF : PRESENT
13.01.21 THE DAY OF : Its Holyday three
14.01.21 THE DAY OF : PRESENT
15.01.21 THE DAY OF : PRESENT
16.01.21 THE DAY OF : Its Holyday four
17.01.21 THE DAY OF : PRESENT
18.01.21 THE DAY OF : PRESENT
19.01.21 THE DAY OF : PRESENT
20.01.21 THE DAY OF : PRESENT
21.01.21 THE DAY OF : Its Holyday five
22.01.21 THE DAY OF : PRESENT
23.01.21 THE DAY OF : PRESENT
24.01.21 THE DAY OF : PRESENT
25.01.21 THE DAY OF : PRESENT
26.01.21 THE DAY OF : Its Holyday six
27.01.21 THE DAY OF : PRESENT
28.01.21 THE DAY OF : PRESENT
29.01.21 THE DAY OF : PRESENT
30.01.21 THE DAY OF : PRESENT
31.01.21 THE DAY OF : PRESENT
PL/SQL procedure successfully completed.
SQL>

---

从Oracle Reports问题开始：完全跳过PL/SQL，使用稍微修改过的游标查询作为报告的查询。在第8行中，您仍然会使用参数，这些参数很可能是在对象导航器中的报表的用户参数下创建的。我想，报表会从其他地方(如Oracle Forms或Apex或…(获得它们的值。我相信ID也是如此——你真的不想硬编码111，是吗？

SQL> select p.att_date     ||
2       ' THE DAY OF : ' ||
3         case when p.att_date = g.gazzeted_date then g.description
4              else 'PRESENT'
5         end result
6  from pay_in_out p left join gazzeted_days g
7     on p.att_date = g.gazzeted_date
8    and gazzeted_date between date '2021-01-01' and date '2021-01-31'
9    and p.emp_code = 111
10  order by p.att_date;
RESULT
--------------------------------------------------------------
01.01.21 THE DAY OF : PRESENT
02.01.21 THE DAY OF : PRESENT
03.01.21 THE DAY OF : PRESENT
04.01.21 THE DAY OF : PRESENT
05.01.21 THE DAY OF : PRESENT
06.01.21 THE DAY OF : PRESENT
07.01.21 THE DAY OF : PRESENT
08.01.21 THE DAY OF : PRESENT
09.01.21 THE DAY OF : Its Holyday two
10.01.21 THE DAY OF : PRESENT
<snip>

---

[外部加入报告]

啊，是的。。。在Reports中，您必须使用；旧的"；Oracle的外部联接(+)运算符。case也是如此-使用decode：

select p.att_date     ||
' THE DAY OF : ' || 
decode(p.att_date, g.gazzeted_date, g.description, 'PRESENT') result
from pay_in_out p, gazzeted_days g 
where p.att_date = g.gazzeted_date (+)
and g.gazzeted_date (+) between date '2021-01-01' and date '2021-01-31'
and p.emp_code = 111
order by p.att_date;