需要帮助才能列出职责名称中的重复功能

Question

我需要在Oracle的职责名称中列出重复的功能

我已经尝试了下面的sql查询

select ffl.user_function_name, ff.function_name, rtl.responsibility_name
  from apps.fnd_form_functions ff,
       apps.fnd_form_functions_tl ffl,
       apps.fnd_responsibility_vl rtl,
       apps.fnd_responsibility r,
       apps.fnd_compiled_menu_functions cmf
 where cmf.function_id = ff.function_id
   and r.menu_id = cmf.menu_id
   and rtl.responsibility_id = r.responsibility_id
   and cmf.grant_flag = 'Y'
   and ff.function_id = ffl.function_id
   and ffl.user_function_name = ffl.user_function_name
   and rtl.responsibility_name like "responsibility name"
   and ffl.language = 'US'
 order by ffl.user_function_name;

我预计sql结果将是职责名称中值为responsibility name的唯一重复函数。

Answer 1

您需要一个带有having count(*) > 1子句的group by

select  ffl.user_function_name, ff.function_name, rtl.responsibility_name
  from  apps.fnd_form_functions ff
  join  apps.fnd_form_functions_tl ffl on ffl.function_id = ff.function_id
  join  apps.fnd_compiled_menu_functions cmf on cmf.function_id = ff.function_id
  join  apps.fnd_responsibility r  on r.menu_id = cmf.menu_id
  join  apps.fnd_responsibility_vl rtl on rtl.responsibility_id = r.responsibility_id            
 where  cmf.grant_flag = 'Y'
   and  ffl.user_function_name = ffl.user_function_name
   and  rtl.responsibility_name = 'responsibility name'
   and  ffl.language = 'US'
 group by ffl.user_function_name, ff.function_name, rtl.responsibility_name
 having count(*) > 1
 order by ffl.user_function_name

并继续使用ANSI-92 SQL标准。