'getting duplicate rows

If I run the below query I am getting duplicate rows. Could anyone please help .

using distinct making query running long. never ending

adate -> 5-may-2022 rdate -> 28th -may-2022

query output getting as

7-may-2022
14-may-2022
21-may-2022
21-may-2022
21-may-2022

code is as below

select * from (
    with qry as (
        SELECT /* + parallel(12) */  adate     AS DATE1,
                     MIN(rdate)    AS DATE2,
           a.id
                FROM
                    t1                      a,
                    t2          b,
                    t3  c
                WHERE 
                        a.id = b.id
                GROUP BY
                    a.id,adate
    )select * from (
      select   id,
      date1 + level - 1 as CurDate,date1,date2
        from qry
      connect by level <= (date2 - date1) + 1)
      --group by id,date1 + level - 1,date1,date2 having count(date1 + level - 1)>1)
    where to_char(curdate,'DY','NLS_DATE_LANGUAGE=ENGLISH') IN('SAT')
    and  (CurDate) NOT IN(date1,date2)) dual;
sqloraclesql-query-store


Solution 1:[1]

Wrong use of generators... Read more:

Fix with minimal changes:

select * from (
    with qry as (
        SELECT /* + parallel(12) */  adate     AS DATE1,
                     MIN(rdate)    AS DATE2,
           a.id
                FROM
                    t1                      a,
                    t2          b,
                    t3  c
                WHERE 
                        a.id = b.id
                GROUP BY
                    a.id,adate
    )select * from (
      select   id,
      date1 + lvl - 1 as CurDate,date1,date2
        from qry
      ,lateral(select level lvl from dual connect by level <= (date2 - date1) + 1)
      --group by id,date1 + level - 1,date1,date2 having count(date1 + level - 1)>1)
    where to_char(curdate,'DY','NLS_DATE_LANGUAGE=ENGLISH') IN('SAT')
    and  (CurDate) NOT IN(date1,date2)) dual;

Solution 2:[2]

dm_exec_function_stats has a property called last_execution_time so you can use that to filter your query.

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'function name',   
    d.cached_time, d.last_execution_time, d.total_elapsed_time,  
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
    d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_function_stats AS d  
WHERE d.last_execution_time > DATEADD(DAY, -1, GETDATE())
ORDER BY [total_worker_time] DESC;

You can read more about it here.

P.S. you forgot to define total_worker_time.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Sayan Malakshinov
Solution 2 gajo357

Related Questions