The select a.o rder_quantity, a. d. eptname, a.s tatus
The from (

Select count (mv. Id) order_quantity, hr. The deptname, 'exception' status
The from hr_dept hr
Left the join mrp_wo_v mv
On mv. Manufact_Hr_dept_id=hr. Id
And the mv. Plan_end_date & gt; Sysdate
And substr (mv. Wo_date_str, 0, 4)='2017'
And substr (mv. Wo_date_str, 5, 2)='12'

Group by hr. The deptname

Union all

Select count (mv. Id) order_quantity, hr. The deptname, 'normal' status
The from hr_dept hr
Left the join mrp_wo_v mv
On mv. Manufact_Hr_dept_id=hr. Id
And the mv. Plan_end_date & lt; Sysdate
And substr (mv. Wo_date_str, 0, 4)='2017'
And substr (mv. Wo_date_str, 5, 2)='12'
Group by hr. The deptname

) a
The order by a. d. eptname, a.s tatus;

Question: when the mv. Plan_end_date all CodePudding user response:

The building Lord to the test data, and your expected results, facilitate everybody back to you

CodePudding user response:

1, when the mv. Plan_end_date all 2, put the and substr (mv) wo_date_str, 0, 4)='2017' and substr (mv) wo_date_str, 5, 2)='12' and mv. Instead wo_date_str like '201712%' will improve efficiency,

CodePudding user response:

refer to the second floor BLT response:

1, when the mv. 2, put the and substr (mv) wo_date_str, 0, 4)='2017' and substr (mv) wo_date_str, 5, 2)='12' and mv. Instead wo_date_str like '201712%' will improve efficiency,

Just want to in the case of not satisfied than sysdate, spell out the exception data, how to transform statement can achieve a normal match an exception statements, please give advice or comments!

CodePudding user response:

reference 1st floor wmxcn2000 response:

the building Lord to the test data, and your expected results, facilitate everybody back to you

When the mv. Plan_end_date all The expected result is as follows:
Number of state department
0 21 workshop exception
260 21 workshop normal
23 workshop exception 0
23 normal workshop 176
.
.

CodePudding user response:

And the mv. Plan_end_date & gt; Sysdate
And substr (mv. Wo_date_str, 0, 4)='2017'
And substr (mv. Wo_date_str, 5, 2)='12'
Not conform to the data of these three conditions at the same time, there are
And substr (mv. Wo_date_str, 0, 4)='2017'
And substr (mv. Wo_date_str, 5, 2)='12' this condition can be replaced by to_char (mv) wo_date_str, 'yyyy - mm)=' 2017-12 '