时间戳

首页 > 解决方案 > 需要帮助列出责任名称中的重复功能

sql - 需要帮助列出责任名称中的重复功能

问题描述

我需要在 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 结果将是责任名称中唯一具有 value 的重复函数responsibility name。

标签： sqloracle

解决方案

你需要一个group bywithhaving count(*) > 1子句

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-92SQL 标准。

推荐阅读