This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 25, 2013 12:32 AM by Ora-aff RSS

Alternative to Column alias in a Where clause

Ora-aff Newbie
Currently Being Moderated

This question is related to earlier thread: https://forums.oracle.com/thread/2578735


The below query gives me millions of records which is unacceptable from performance point of view. I am only interested in Tickets that are closed in a particular week. I cannot use closed_date in the inner query as closed_date >= '08/18/2013' AND closed_date < '08/24/2013' because close_date is column alias. I can't put it in the where condition of parent query because it makes no sense because by the time control raches to WHERE condition of parent query the subquery would have already fetched millions of records.


Any alternative to this?


  1. SELECT  b.ticket_id, b.ticket_status, b.created_date, b.closed_status, b.closed_date 
  2.   FROM 
  3.   (SELECT  ticket_id, ticket_status, created_date, lead(ticket_status) over (partition BY ticket_id order by created_date) AS closed_status, lead(created_date) over (partition BY ticket_id order by created_date) AS closed_date 
  4.   FROM cc_ticket_history 
  5.   WHERE ticket_status IN ('NEW', 'REOPENED', 'CLOSED')                                   /* AND closed_date >= '08/18/2013' AND closed_date < '08/24/2013'      gives error */
  6.   ) b 
  7.   WHERE b.ticket_status != 'CLOSED' and b.closed_status = 'CLOSED                 /*AND b.closed_date >= '08/18/2013' AND b.closed_date < '08/24/2013'     -- makes no sense */';
Ticket_IDTICKET_STATUSCREATED_DATECLOSED_STATUSCLOSED_DATE
D21207155NEW06/28/2013 17.28.59.000000000CLOSED06/28/2013 18.54.23.000000000
D21207155REOPENED07/02/2013 19.55.04.000000000CLOSED07/02/2013 23.06.16.000000000
  • 1. Re: Alternative to Column alias in a Where clause
    Priyasagi Pro
    Currently Being Moderated

    Hi Ora,

     

    Try this,

     

    select  b.ticket_id, b.ticket_status, b.created_date, b.closed_status, b.closed_date from

    (select * from
          (select  ticket_id, ticket_status, created_date,
          lead(ticket_status) over (partition by ticket_id order by created_date)  as closed_status,
          lead(created_date) over (partition by ticket_id order by created_date) as closed_date
          from cc_ticket_history where ticket_status in ('NEW', 'REOPENED', 'CLOSED'))

    where closed_date >= '08/18/2013' and closed_date < '08/24/2013')    

    ) b where b.ticket_status != 'CLOSED' and b.closed_status = 'CLOSED';

  • 2. Re: Alternative to Column alias in a Where clause
    Ora-aff Newbie
    Currently Being Moderated

    Hi Priya, Thanks for the answer.

     

    In your SQL, the inner most query would already retrieve millions of records before the SQL Engine excutes the second query which has "closed_date >= '08/18/2013' and closed_date < '08/24/2013'".

  • 3. Re: Alternative to Column alias in a Where clause
    Priyasagi Pro
    Currently Being Moderated

    Why don't  you use created_date instead of closed_date in the inner query.

     

    select  b.ticket_id, b.ticket_status, b.created_date, b.closed_status, b.closed_date from

    (select  ticket_id, ticket_status, created_date,
          lead(ticket_status) over (partition by ticket_id order by created_date)  as closed_status,
          lead(created_date) over (partition by ticket_id order by created_date) as closed_date
          from cc_ticket_history where ticket_status in ('NEW', 'REOPENED', 'CLOSED') and

         created_date >= '08/18/2013' and created_date < '08/24/2013'

    ) b where b.ticket_status != 'CLOSED' and b.closed_status = 'CLOSED';

  • 4. Re: Alternative to Column alias in a Where clause
    jihuyao Journeyer
    Currently Being Moderated

    Yes.  The filter can be applied in the sub-query but the start date should be one day early,

     

    created_date >= '08/18/2013' {-1} and created_date < '08/24/2013'


    And this brings out a logic flaw, for example, there are 2 records with '08/17/2013' ahead a record with '08/18/2013' in order, and only one of the two records with '08/17/2013' will be returned.  So problem is which one should be returned?  If either one is fine, then what if the chosen one happens with b.closed_status <> 'CLOSED'?



  • 5. Re: Alternative to Column alias in a Where clause
    Ora-aff Newbie
    Currently Being Moderated

    I can't use the created_date becasue the job of query is to retrieve tickets that are closed in a particular week irrespective of it's created time. The ticket might have been created couple of months ago, a year ago or in that particular week but if it is closed in that week, it should be selected. If I use created_date filter, the resultset of the query will not include tickets that were created before that particular week.

  • 6. Re: Alternative to Column alias in a Where clause
    Ora-aff Newbie
    Currently Being Moderated

    And this brings out a logic flaw, for example, there are 2 records with '08/17/2013' ahead a record with '08/18/2013' in order, and only one of the two records with '08/17/2013' will be returned.



    How only one record will be returned out of two record with '08/17/2013'?

  • 7. Re: Alternative to Column alias in a Where clause
    Priyasagi Pro
    Currently Being Moderated

    Post sample data and your output expectations.

    Then we will discuss how to simplify the same query.  Because I have much confusion about your output expectation.

  • 8. Re: Alternative to Column alias in a Where clause
    BluShadow Guru Moderator
    Currently Being Moderated

    I'm surprised nobody has mentioned the fact that dates are being treated as strings and not dates.

     

    Statements such as:

     

     

    AND closed_date >= '08/18/2013'

     

     

    are not valid and can lead to errors or corrupted data.

     

    Dates should always be treated as dates...

     

    AND closed_date >= to_date('08/18/2013','MM/DD/YYYY')

     

    and if closed_date is not a DATE datatype then there's an even bigger problem.

  • 9. Re: Alternative to Column alias in a Where clause
    dariyoosh Journeyer
    Currently Being Moderated

    BluShadow wrote:

     

    Dates should always be treated as dates...

     

    AND closed_date >= to_date('08/18/2013','MM/DD/YYYY')

     

     

    And if we're sure that the time portion is not relevant to the context, then instead of to_date we can simply use a date literal:

     

    AND closed_date >= DATE '2013-08-18'

     

    Regards,

    Dariyoosh

  • 10. Re: Alternative to Column alias in a Where clause
    Ora-aff Newbie
    Currently Being Moderated

    As per our DBA due to table design it might not be possible what I was looking for. So I am working on alternative query all together. Thanks for helping out.

  • 11. Re: Alternative to Column alias in a Where clause
    Ora-aff Newbie
    Currently Being Moderated

    Point Noted.

  • 12. Re: Alternative to Column alias in a Where clause
    Ora-aff Newbie
    Currently Being Moderated

    Time portion is importamt. So when I say closed_date >= '08/18/2013, I actually mean closed_date >=2013-09-18 00:00:00. Ehat is the difference between : DATE '2013-08-18'   and      to_date('08/18/2013','MM/DD/YYYY')

  • 13. Re: Alternative to Column alias in a Where clause
    BluShadow Guru Moderator
    Currently Being Moderated

    There's no difference in the result of those two.  The only difference is if you want to include a time other than 00:00:00 you won't be able to use the DATE '2013-08-18' format to create a date/time, as that only deals with the date portion (and time is always 00:00:00 that way).  that's why dariyoosh said "if the time portion is not relevant".

     

    If you're going to be including times, use the TO_DATE function with the appropriate format mask to include the times.

  • 14. Re: Alternative to Column alias in a Where clause
    BluShadow Guru Moderator
    Currently Being Moderated

    Ora-aff wrote:

     

    Point Noted.

     

    It's not just a point, it's a fundamental bug in the code.

     

    Consider this...

     

    SQL> ed
    Wrote file afiedt.buf

      1  select 'Wrong' from dual
      2* where '03/18/2012' > '02/15/2013'
    SQL> /

     

    'WRON
    -----
    Wrong

     

    Comparing strings is not the same as comparing dates.

     

    SQL> ed
    Wrote file afiedt.buf

      1  select 'Wrong' from dual
      2* where to_date('03/18/2012','MM/DD/YYYY') > to_date('02/15/2013','MM/DD/YYYY')
    SQL> /

     

    no rows selected

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points