How to write a loop statement in Oracle SQL

Question

I need a query for collecting information of check-in status for a group of staff in a particular department everyday.

People I need to collect are saved in a simple table named LEADER, like:

 - LEADERNAME    -NUM-

 - TOM             1
 - BEN             2
 - LUCY            3
 - ERIC            4

The check-in status of all staff in my company is saved in a table named TB_DAYWORK. We know that everyday, once you checked in, the table will update with a new line, including your name and the check-in time.

I've composed a SQL for ONE person's check-in information. the SQL is like this:

select m.* ,n.starttime,n.peoplename 
from ( 
select 
everyDay,
to_char(everyday,'dy') as weekday,
lpad(to_char(everyday,'w'),6) as weekinmonth,
lpad(to_char(everyday,'ww'),6) as weekinyear 
from 
  (select to_date('20150901','yyyymmdd') + level - 1 as everyDay from dual connect by level <= (last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))) m 
left join 

(
select distinct STARTTIME,
Peoplename
from TB_DAYWORK where CREATETIME between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') 
and PEOPLENAME in (select leadername from leader where num=1) ) n on m.EVERYDAY=n.STARTTIME
order by 1;

The query result is just like this:

From the image you can see that for one person, all status has been found by a left join with a dynamic calendar. If the data in PEOPLENAME and STARTTIME is not null that means they had checked-in for that day, or if the column is null, it means he was absent.

Since all people need to be selected here are in the table LEADER and sorted by the column NUM. I think there should be a way of using a loop statement to collect all people's status we need by using one SQL. Is there a way to do this?

/**********************UPDATE********************************/

@APC thank you for your help, I've just tested your code and the last code I use is like this:

    select m.* , n.starttime, n.peoplename 
from ( 
        select 
            everyDay,
            to_char(everyday,'dy') as weekday,
            lpad(to_char(everyday,'w'),6) as weekinmonth,
            lpad(to_char(everyday,'ww'),6) as weekinyear 
        from 
          (select to_date('20150901','yyyymmdd') + level - 1 as everyDay 
           from dual 
           connect by level <= (last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))
       ) m 
    left join 
        (
        select  t.STARTTIME,
                t.Peoplename,
                l.num
        from leader l
             join TB_DAYWORK t
                  on t.PEOPLENAME = leadername
        where t.STARTTIME between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss')
        order by l.num, t.Starttime
        ) n 
on m.EVERYDAY=n.STARTTIME
order by n.num,m.everyDay ;

here's the result I got:

[

Perfectly ordered by everyday and leadername, but as highlighted, you can see that the everyday column has missed data, here it lost 9/12 and 9/13.

I am a little confused, because as far as I know the LEFT JOIN will keep all data at the left side, if so the right side -- STARTTIME column and PEPOPENAME column will have some null lines, which will be easy to see (you can see it on the first pic), and that is the reason I use it. Why are they missing now?

/////***********************UPDATE********************//////

the cross join way results is like this: cross join

///////////////************UPDATE SAMPLE DATA**************///////////////

only 2 tables are involved here: TB_DAYWORK and LEADER , I prepared sample tables for further explore. same data to the original tables, but all columns with chinese characters are replaced by roman letters, just in case for the character issue.

for table LEADER which is very simple, please run below code to build a sample:

create table leader(leadername varchar2(50), num number);

insert into leader values ('mazeping','1');
insert into leader values ('zhangyi','2');
insert into leader values ('taoshengfa','3');

for table TB_DAYWORK I got its metadata:

CREATE TABLE "JSHXBJY"."TB_DAYWORK"
    (       "ROW_ID" VARCHAR2(32) NOT NULL ENABLE,
    "CREATETIME" DATE,
     "CREATEUSERID" VARCHAR2(32),
     "UPDATETIME" DATE,
     "UPDATEUSERID" VARCHAR2(32),
     "DELFLAG" NUMBER(10,0),
     "DEPTID" VARCHAR2(255),
     "STARTTIME" DATE,
     "STARTTIMESTR" VARCHAR2(255),
     "WORK_CONTENT" VARCHAR2(3000),
     "PEOPLENAME" VARCHAR2(255),
     "WORK_CONTENT_INFO" VARCHAR2(4000),
     "WORK_REMARK" VARCHAR2(255),
     "PEOPLE_SORT" NUMBER(10,0),
     "LOGINID" VARCHAR2(255),
     "HEAD_PHOTO_PATH" VARCHAR2(255)
     ) SEGMENT CREATION IMMEDIATE
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "JSHXBJY"

also I exported some raw data from the original table, please download from: http://pan.baidu.com/s/1mgtRiBI (press the 2nd button on the top-right corner to download,please let me know if this link cannot be reached).

and the control file for sqlloader is:

LOAD DATA
INFILE 'c:\oracle\tb_daywork_data1.txt'
APPEND
into TABLE TB_DAYWORK

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(ROW_ID,CREATETIME date "yyyy-mm-dd hh24:mi:ss",CREATEUSERID,UPDATETIME date "yyyy-mm-dd",UPDATEUSERID,DELFLAG,DEPTID,STARTTIME date "yyyy-mm-dd",STARTTIMESTR,WORK_CONTENT,PEOPLENAME,WORK_CONTENT_INFO,WORK_REMARK,PEOPLE_SORT,LOGINID,HEAD_PHOTO_PATH)

and the command is :

C:\>sqlldr jshxbjy/jshxbjy control=c:\oracle\sqlldr.txt discard=c:\oracle\tb_daywork_dis.txt bad=c:\oracle\tb_daywork_bad.txt log=c:\oracle\tb_daywork_log.txt

I've tested by myself, these codes should be ok to build up sample tables, which including 3 people's check-in data in this September.

Answer 1

the answer is here:

need to build a temp table with full date and peoplename, and then do the left join to get the empties.

    select tmp.* , n.starttime, n.peoplename
from
( select m.*,l.*
    from (
        select
            everyDay,
            to_char(everyday,'dy') as weekday,
            lpad(to_char(everyday,'w'),6) as weekinmonth,
            lpad(to_char(everyday,'ww'),6) as weekinyear
        from
          (select to_date('20150901','yyyymmdd') + level - 1 as everyDay
           from dual
           connect by level <= (last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))
       ) m,
       leader l ) tmp
      left join
      (
        select  t.STARTTIME,
                t.Peoplename
        from  TB_DAYWORK t
        where t.STARTTIME between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss')
          ) n
on tmp.EVERYDAY=n.STARTTIME and tmp.leadername=n.peoplename
order by tmp.num,tmp.everyDay ;

Answer 2

You don't need a "loop". A SELECT statement is a loop, in that it returns one row for each record in a table. If you want all the records in the LEADER table all you need do is remove the filter on the NUM column and join that table in the query instead.

So your query should look like this:

select m.* , n.starttime, n.peoplename 
from ( 
        select 
            everyDay,
            to_char(everyday,'dy') as weekday,
            lpad(to_char(everyday,'w'),6) as weekinmonth,
            lpad(to_char(everyday,'ww'),6) as weekinyear 
        from 
          (select to_date('20150901','yyyymmdd') + level - 1 as everyDay 
           from dual 
           connect by level <= (last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))
       ) m 
    left join 
        (
        select t.STARTTIME,
                t.Peoplename,
                1.num
        from leader l
             join TB_DAYWORK t
                  on t.PEOPLENAME = leadername
        where t.CREATETIME between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') 
        ) n 
on m.EVERYDAY=trunc(n.STARTTIME)
order by m.everyDay, n.starttime, n.num ;

I assume STARTTIME includes a time element, so you need to use TRUNC() to join by just the data element. Not sure why you have a DISTINCT in your query.

---

"STARTTIME column and PEPOPENAME column will have some null lines ... Why are they missing now?"

Your code is subtly different from my version. Specifically you sort it like this: order by n.num,m.everyDay ;.

In the documentation for ORDER BY it says:

NULLS LAST is the default for ascending order

Because you are sorting by n.num all the lines where that column is null will appear at the bottom of the result set. They are not missing, you just need to keep scrolling.

Alternatively change the sort order. Revert to the one I used, or else order by n.num,m.everyDay NULLS FIRST ;

---

Anticipating your question: how to get a null STARTTIME entry for each date that a Leader doesn't work?

The OUTER JOIN gives us one row from the generated calendar for every matching date in TB_DAYWORK plus one row for every date which doesn't match any date in TB_DAYWORK. But, as you say, we lose the visual cue of a blank STARTTIME for each Leader.

To get it back we need to generate a complete set of rows from the calendar for each Leader. To do this we need to CROSS JOIN the calendar sub-query with the LEADER table. Left-joining TB_DAYWORK to that Cartesian product will provide the visual cue you want.

select 1.num, l.leadername, m.* , t.starttime 
from ( 
        select 
            everyDay,
            to_char(everyday,'dy') as weekday,
            lpad(to_char(everyday,'w'),6) as weekinmonth,
            lpad(to_char(everyday,'ww'),6) as weekinyear 
        from 
          (select to_date('20150901','yyyymmdd') + level - 1 as everyDay 
           from dual 
           connect by level <= (last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))
       ) m 
    cross join leader l
    left join tb_daywork t
      on t.peoplename = l.leadername
      and t.starttime = m.everyday
where t.createtime between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') 
order by l.num, m.everyday ;

---

Caveat about untested code still applies,