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

    Alternative to Column alias in a Where clause

    Ora-aff

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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

                            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.

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

                              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

                                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

                                  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