I run a query on SQL Developer which returns me 1715 rows. Then I export it into XLS format and open the file in OpenOffice Spreadsheett. In the SQL Developer, I see the following rows as:
D21211572 | NEW | 08/29/2013 07.45.21.060000000 | CLOSED | 08/29/2013 10.42.43.700000000 |
D21211572 | REOPENED | 08/30/2013 07.09.36.768000000 | CLOSED | 08/30/2013 10.57.51.138000000 |
D21211572 | REOPENED | 09/03/2013 05.41.22.568000000 | CLOSED | 09/03/2013 07.49.47.051000000 |
D21211572 | REOPENED | 09/03/2013 11.46.05.104000000 | CLOSED | 09/06/2013 19.26.06.618000000 |
Howver the same ticket D21211572 looks strange in the spreadsheet:
D21211572 | NEW | 08/29/2013 07.45.21.060000000 | CLOSED | 08/29/2013 10.42.43.700000000 |
D21211572 | NEW | 08/30/2013 07.09.36.768000000 | CLOSED | 08/30/2013 10.57.51.138000000 |
D21211572 | NEW | 09/03/2013 05.41.22.568000000 | CLOSED | 09/03/2013 07.49.47.051000000 |
D21211572 | NEW | 09/03/2013 11.46.05.104000000 | CLOSED | 09/06/2013 19.26.06.618000000 |
REOPENED is replaced with NEW in the spreadsheet.
I am using Oracle XE 11g.
The SQL Developer version is :
Java(TM) Platform | 1.6.0_35 |
Oracle IDE | 3.2.20.09.87 |
Versioning Support | 3.2.20.09.87 |
My Query is:
select ticket_id, ticket_status, created_date, closed_status, 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_status_history h
WHERE ticket_status in ('NEW', 'REOPENED', 'CLOSED')
) b
where ticket_status != 'CLOSED' and closed_status = 'CLOSED';
Not Sure what is wrong here??