oracle sql调错

来源：互联网发布：mac照片导入编辑：程序博客网时间：2024/05/16 14:30

select count(*)

from ((select (case

when (instr(',' || wm_concat(type) || ',', ',develop-plan,') > 0) then

'develop-plan'

else

'unRead'

end) type,

max(id) id,

name,

node_level,

project,

end,

max(created_date) created_date,

center,

year_flg,

sequence,

min(isRead) isRead,

MyReplace(wm_concat(msgCreateDate), ',') msgCreateDate,

wm_concat(group_name),

max(planId),

0 owe_days,

0 owe_money

from (select 'develop-plan' as type,

node.node_id as id,

template_node.node_name as name,

template_node.node_level,

project.project_name as project,

node.schedule_end_date as end,

warning.CREATED_DATE as created_date,

'' as center,

'' as year_flg,

ROW_NUMBER() OVER(PARTITION BY node.templet_id, project.project_project_id ORDER BY warning.created_date ASC) as sequence,

(select min(t.is_read)

from plan6_message_user_read t

where t.node_id = node.node_id

and t.need_reader = 'zhangch'

group by t.node_id) isRead,

'' msgCreateDate,

info.name group_name,

info.biz_cd planId,

0 owe_days,

0 owe_money

from plan6_warning warning,

plan6_node node,

project_distribution info,

project_period pp,

project_project project,

plan6_templet_node template_node

where warning.node_id = node.node_id

and node.plan_id = info.biz_cd

and info.project_period_id = pp.project_period_id

and pp.is_enabled = 1

and pp.project_project_id = project.project_project_id

and template_node.node_id = node.templet_id

and info.if_in_plan = 1

and (warning.charger_cd = 'zhangch' OR

node.charger_cd = 'zhangch' OR

node.center_manager_cd = 'zhangch' OR

project.project_charger_cd = 'zhangch' OR

project.project_charger_cd2 = 'zhangch' OR

project.project_charger_cd_bis = 'zhangch' OR

('wubc' = 'zhangch' AND

template_node.node_level in (1, 2)) OR

(('xuhf' = 'zhangch' OR 'zhanghf' = 'zhangch') and

template_node.node_level = 1))

and warning.stat = 2

and node.status = 1

and node.is_enabled <> 0

and warning.stat = 2

union

select 'unRead' tyep,

node.node_id id,

template_node.node_name as name,

template_node.node_level,

project.project_name as project,

node.schedule_end_date as end,

to_date('', 'yyyymmdd hh24:mi:ss') as created_date,

'' as center,

'' as year_flg,

ROW_NUMBER() OVER(PARTITION BY node.templet_id, project.project_project_id ORDER BY r.created_date ASC) as sequence,

r.is_read isRead,

to_char(r.created_date, 'yyyymmdd hh24:mi:ss') msgCreateDate,

info.name group_name,

info.biz_cd planId,

0 owe_days,

0 owe_money

from plan6_message_user_read r,

plan6_node node,

project_distribution info,

project_period pp,

project_project project,

plan6_templet_node template_node

where r.node_id = node.node_id

and node.plan_id = info.biz_cd

and info.project_period_id = pp.project_period_id

and pp.is_enabled = 1

and pp.project_project_id = project.project_project_id

and template_node.node_id = node.templet_id

and info.if_in_plan = 1

and node.is_enabled <> 0

and r.need_reader = 'zhangch'

and r.is_read = '0') warning

where warning.sequence = 1

group by name,

node_level,

project,

end,

center,

year_flg,

sequence) union

(select 'oa_meeting' as type,

metting.oa_meeting_id as id,

TO_CHAR(metting.business) as name,

10 as node_level,

'' as project,

metting.target_date as end,

warning.created_date as created_date,

'' as center,

'' as year_flg,

0 as sequence,

'' isRead,

'' msgCreateDate,

'' group_name,

'' planId,

0 owe_days,

0 owe_money

from oa_meeting metting, plan_warning warning

where metting.oa_meeting_id = warning.target

and metting.HIDDEN_FLG = 0

and metting.status in (1, 2, 4)

and metting.target_date is not null

and instr(';' || warning.responsible_person, ';zhangch;') > 0));

报错如下：

ORA-01790的错误，查到是因为报错的那个wm_concate后面的字段不是group by的字段。

0 0