This is what one of us, a normal guy, can integrate almost all business logic into SQL query statements.
Although I am not in favor of his practice, his SQL is worth learning.
Select
Distinct A. student_no,
A. dm_cd,
A. post_date_plan,
A. order_flg,
Case when substr (A. dm_cd, 6, 1) in ('1', '2') then 1
When substr (A. dm_cd, 6, 1) in ('s', 'x', 'J', 'L') then 2 end as regularflag,
Case when a. post_date_plan + 7> = sysdate then 1 else 0 end as newflag,
'Z' as rank
From
Tbl_dmrecord
Where
A. student_no = '***' and
A. order_flg = 0 and
A. post_date_plan <sysdate and
Substr (A. dm_cd, 6, 1) in ('1', '2', 's', 'x', 'J', 'L') and
Substr (A. dm_cd, 1, 1) Not in ('V', 'x') and
A. del_date is null
Order by A. post_date_plan DESC
Output result:
Student_no dm_cd post_date_plan order_flg regularflag newflag rank
1 *** dy101221 2006/08/07 0 1 1 Z
2 *** hy102l11 2006/08/07 0 2 1 Z
3 *** ay100103 2006/08/07 0 1 1 Z
......