CODEWITHSUNDEEP
sqloracle-databaseoracle11gbusiness-intelligencebusiness-objects
Jacobo Geada
Jacobo Geada

Reputation: 117

Query without extra records

I have a problem with a query in Business Objects. My table has record for events identified by a id, client, state and date. One client can have two states open and close as follow

enter image description here

My select with duplicate register of client 1 is

SELECT T1.ID, T1.CLIENT, T1.DATE AS DATE_OPEN,
CASE WHEN T2.STATE = 'CLOSE' THEN T2.DATE END AS DATE_CLOSE 
FROM MYTABLE T1 
inner join MYTABLE T2 
  on (T1.CLIENT = T2.CLIENT)
where T1.STATE='OPEN' AND T2.STATE IN ('OPEN','CLOSE');

Please, could you help me to achieve a query without the ID=111??

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

I think you just want a left join like this:

SELECT T1.ID, T1.CLIENT, T1.DATE AS DATE_OPEN,
       T2.DATE AS DATE_CLOSE 
FROM MYTABLE T1 LEFT JOIN
     MYTABLE T2 
     ON T1.CLIENT = T2.CLIENT AND t2.STATE = 'Close'
WHERE T1.STATE = 'OPEN' ;

That said, I might also suggest aggregation:

select min(id), client, min(date) as date_open,
       nullif(max(date), min(date)) as date_close
from mytable t
group by client;

This assumes that the open date is less than the close date. It also assumes that the smaller id is for the open. Both of these can be handled using conditional logic, but such logic seems unnecessary.

Upvotes: 0

Craig
Craig

Reputation: 5820

I believe you want something like:

SELECT T1.ID, T1.CLIENT, T1.DATE AS DATE_OPEN,
    CASE WHEN T2.STATE = 'CLOSE' THEN T2.DATE END AS DATE_CLOSE 
FROM MYTABLE T1 
LEFT OUTER JOIN MYTABLE T2 
  ON (T1.CLIENT = T2.CLIENT)
  AND t2.state = 'CLOSE'
WHERE T1.STATE='OPEN';

Note this will "remove" the 333 ID line, but the 111 ID line should have the DATE_CLOSE populated like I believe you are looking for. If you really need the 111 line gone, you can replace T1.ID with CASE WHEN T2.STATE='CLOSE' THEN T2.ID ELSE T1.ID END AS ID in the select.

I am making several assumptions here including:

  • The table you showed was the output you are currently getting (and only wanting a single line per client)
  • Your data model has a row per "event": ID, CLIENT, DATE, STATE

Upvotes: 1

Related Questions