SQL の CASE 使い方

(PostgreSQL 9.4)

---

background

reports (レポート提出管理)テーブルが次の通り定義されている

CREATE TABLE reports(
  reportid  INTEGER -- レポート番号
    NOT NULL,
  submitter CHAR(16) -- 提出者名
    NOT NULL,
  acceptflag CHAR(1) -- 承認フラグ '0':無効 '1':有効
    NOT NULL
    CONSTRAINT reports_acceptflag_validvalue
    CHECK(acceptflag = '0' OR acceptflag = '1'),
  rejectflag CHAR(1) -- 否認フラグ '0':無効 '1':有効
    NOT NULL
    CONSTRAINT reports_rejectflag_validvalue
    CHECK(rejectflag = '0' OR rejectflag = '1'),
  submitdate DATE    -- 提出日
    NOT NULL,
  PRIMARY KEY(reportid)
);

次の通りデータ集合が存在する

reportid |    submitter     | acceptflag | rejectflag | submitdate 
----------+------------------+------------+------------+------------
       1 | ichiro           | 0          | 0          | 9999-12-31
       2 | jiro             | 0          | 0          | 2015-11-01
       3 | saburo           | 0          | 0          | 2015-11-01
       4 | shiro            | 1          | 0          | 2015-11-01
       5 | goro             | 1          | 0          | 2015-11-01
       6 | rokuro           | 0          | 1          | 2015-11-01
       7 | shichiro         | 1          | 1          | 2015-11-01

want

SQL を用いて次の表示を行いたい

• submitdate が '9999-12-31' で登録されているならば「'not submitted'」(未提出)
• submitdate が '9999-12-31' 以外で登録されている場合
  • 承認フラグ, 否認フラグともに無効ならば「'submitted'」(提出済)
  • 承認フラグが有効, 否認フラグが無効ならば「'accepted'」(承認)
  • 承認フラグが無効, 否認フラグが有効ならば「'rejected'」(否認)
  • 承認フラグ, 否認フラグともに有効ならば「'should re submit(cancel)'」(再提出(提出無効))
  • この条件分岐に当てはまらないならば「'NO MATCH'」
• 上のいづれにも当てはまらないものを「'N/A'」

operation

SELECT
  submitter,
  CASE
    WHEN (submitdate = '9999-12-31') THEN 'not submitted'
    WHEN (submitdate <> '9999-12-31') THEN
      CASE
        WHEN ((acceptflag = '0') AND (rejectflag = '0')) THEN 'submitted'
        WHEN ((acceptflag = '1') AND (rejectflag = '0')) THEN 'accepted'
        WHEN ((acceptflag = '0') AND (rejectflag = '1')) THEN 'rejected'
        WHEN ((acceptflag = '1') AND (rejectflag = '1')) THEN 'should re submit(canceled)'
      ELSE 'NO MATCH'
      END 
  ELSE 'N/A'
  END submission_state
FROM reports;

result

    submitter     |      submission_state      
------------------+----------------------------
 ichiro           | not submitted
 jiro             | submitted
 saburo           | submitted
 shiro            | accepted
 goro             | accepted
 rokuro           | rejected
 shichiro         | should re submit(canceled)