When converting statements the SQL view, found that the parameters of the view of a part of the query conditions field not in view, if this field is added to the view, the group by is need to add this field, lead to check the results will be repeated data,

Here is one of the need to modify the SQL statement,
Select sum (t.w ork_hour - t.o utside_work_hour) AS TEST, t.d epartment_name from si_monthly_time t
Where 1=1 and to_number (tc ount_date) & gt;=to_number (to_char (to_date (? BeginTime, 'yyyy - MM),' yyyyMM '))
And to_number (tc ount_date) & lt;=to_number (to_char (to_date (? EndTime 'yyyy - MM'), 'yyyyMM'))
And t.i n_instrument_id in (select v.i n_instrument_id from in_instrument v where v.i s_to_count='0')
Group by t.d epartment_name

If
The CREATE OR REPLACE the VIEW TEST
AS
Select sum (t.w ork_hour - t.o utside_work_hour) AS TEST, t.d epartment_name from si_monthly_time t
Create such view, do not contain count_date this field,
Can't use the where a query into the field,

Turn your bosses, how to modify,

CodePudding user response:

The SUM () OVER PARTITION BY (a) whether to this

CodePudding user response:

reference 1st floor ZJHZ_ leaf response:

SUM () OVER PARTITION BY (a) whether to this

Thank you very much, the above SQL statement containing ginseng can you change to view?