如何仅从此查询返回唯一行？

In SQL, I have the following SQL (oracle) query :

在SQL中,我有以下SQL(oracle)查询:

SELECT  
     hur.reg_request_id AS "Request No", 
     RESPONSIBILITY_NAME  AS "Module",    
     to_char(hur.creation_date, 'DD-Mon-YYYY HH24:MI:SS') 
          AS "Submitted" ,  /* Dy DD-Mon-YYYY HH24:MI:SS */
     to_char(hur.last_update_date,    'DD-Mon-YYYY HH24:MI:SS') AS "Supervisor"  ,  /* COMMA */          

     ROUND((hur.last_update_date - hur.creation_date), 3 )      
          || ' days' AS "Avg Supervisor Appr Duration" ,   
         hura.creation_date AS "Responsibility" , NULL AS "Avg Resp Appr Duration", NULL AS "Training",
            NULL AS "Avg Training Appr Duration" , 
     hur.LAST_UPDATE_DATE AS "Security", NULL AS "Avg Security Appr Duration",
     NULL AS "SOD", 
     NULL AS "Average SOD Approval Duration" , 
     NULL AS "Configuration",            
     NULL AS "Avg Config Appr Duration",               
     hurr.last_update_date AS "Approved", 
     ROUND( hurr.last_update_date - hur.creation_date, 2 )  
         AS  "Total Overall Duration",
     NULL AS "Notes"
     FROM HHS_UMX_REG_SERVICES hur ,  
     FND_RESPONSIBILITY_VL frt , 
     HHS_UMX_REG_REQUESTS hurr  , 
     HHS_UMX_RESP_ACTIVITY hura

  WHERE hur.RESPONSIBILITY_ID = frt.RESPONSIBILITY_id
             AND 
     hur.RESPONSIBILITY_APPLICATION_ID = frt.APPLICATION_ID           
             AND hurr.reg_request_id  = hur.reg_request_id
             AND hura.created_by = hur.created_by
             AND hura.creation_date >  hur.creation_date /* AND (hur.creation_date + 100 ) */

               AND hur.reg_request_id IN



               /* Manually filling in the 6-digit requisition numbers,based

               on the FDA UFMS User Prov. inbox usually from last 3 days */

                ('261507')

             ORDER BY hur.reg_request_id ASC

The problem is that it's returning duplicate rows. I tried to add DISTINCT to the query, but this didn't work.

问题是它返回了重复的行。我试图将DISTINCT添加到查询中,但这不起作用。

thanks

1 个解决方案

#1

If you have duplicate rows then you might be joining to a child table that you should be performing a correlated subquery against.

如果您有重复的行,那么您可能正在加入一个您应该执行相关子查询的子表。

I suspect from the query semantics that the problem is the join between HHS_UMX_REG_SERVICES and HHS_UMX_RESP_ACTIVITY, which ought to be an EXISTS correlated subquery. You should also use ANSI join syntax (and make the code more readable)

我怀疑从查询语义来看,问题是HHS_UMX_REG_SERVICES和HHS_UMX_RESP_ACTIVITY之间的连接,它应该是一个EXISTS相关的子查询。您还应该使用ANSI连接语法(并使代码更具可读性)

...
from hhs_umx_reg_services  hur
join fnd_responsibility_vl frt  on (hur.responsibility_id             = frt.responsibility_id and 
                                    hur.responsibility_application_id = frt.application_id ) 
join hhs_umx_reg_requests  hurr on (hurr.reg_request_id               = hur.reg_request_id )
where exists (
        select null
        from   hhs_umx_resp_activity hura
        where  hura.created_by    = hur.created_by and
               hura.creation_date > hur.creation_date)
...

The subquery would be implemented as a semi-join, which will be more efficient than the inner join.

子查询将实现为半连接,这将比内连接更有效。

#1