CODEWITHSUNDEEP
sqloracle-databaseunion
SKARVA Bodavula
SKARVA Bodavula

Reputation: 957

Query Refactor without union

Can we re-factor below query without using union. May be we can use or logical operator in where condition.

select a.RequestNumber, a.ticket_id as TicketID, a.requester,   num_business_days (a.last_modified_date, sysdate) as businessdays,a.REM_MAIL_SENT_DTTS from (SELECT ticket_id, request_number as  RequestNumber, created_by as requester,
  last_modified_date,REM_MAIL_SENT_DTTS

      From Ri_Ticket_Info
      Where Status_Id = 6 And REM_MAIL_SENT_DTTS   Is Null)A
      where num_business_days (a.last_modified_date, sysdate) >= 5

      union

      select a.RequestNumber, a.ticket_id as TicketID, a.requester,   num_business_days (a.last_modified_date, sysdate) as businessdays,a.REM_MAIL_SENT_DTTS from (SELECT ticket_id, request_number as  RequestNumber, created_by as requester, 
      last_modified_date,REM_MAIL_SENT_DTTS

      From Ri_Ticket_Info
      Where Status_Id = 6 And REM_MAIL_SENT_DTTS   Is not Null  )A
      where num_business_days (a.REM_MAIL_SENT_DTTS, sysdate) >= 5

Upvotes: 1

Views: 297

Answers (2)

Boneist
Boneist

Reputation: 23578

I think this can be rewritten as just:

SELECT a.requestnumber,
       a.ticket_id AS ticketid,
       a.requester,
       num_business_days (a.last_modified_date, SYSDATE) AS businessdays,
       a.rem_mail_sent_dtts
FROM   ri_ticket_info a
WHERE  a.status_id = 6
and    num_business_days (nvl(a.rem_mail_sent_dtts, a.last_modified_date), SYSDATE) >= 5;

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93704

You don't need any logical operator, Remove REM_MAIL_SENT_DTTS condition from where clause

SELECT a.RequestNumber,
       a.ticket_id                                       AS TicketID,
       a.requester,
       Num_business_days (a.last_modified_date, sysdate) AS businessdays,
       a.REM_MAIL_SENT_DTTS
FROM   (SELECT ticket_id,
               request_number AS RequestNumber,
               created_by     AS requester,
               last_modified_date,
               REM_MAIL_SENT_DTTS
        FROM   Ri_Ticket_Info
        WHERE  Status_Id = 6)A
WHERE  Num_business_days (a.last_modified_date, sysdate) >= 5

Upvotes: 2

Related Questions