Oracle Select Query - Returning Rows

Toad for Oracle & DevOps Toolkit
1

I have this silly query which returns a bunch of data for each person on a claims system we have:

SELECT BR.L_EN_UK, c.claimnumber, to_char(c.CREATETIME, ‘DD/MM/YYYY HH24:MI’), lt.NAME, con.HAPPYRECEIVESMS_INS, con.firstname, con.lastname, con.NAME, con.CELLPHONE, con2.FIRSTNAME, con2.LASTNAME, CR.L_EN_UK
FROM CSR_CC_OWNER.cc_claim c, CSR_CC_OWNER.cc_claimcontact cc, CSR_CC_OWNER.cc_contact con,
CSR_CC_OWNER.CC_USER u, CSR_CC_OWNER.cc_contact con2, CSR_CC_OWNER.cc_policy pol, csr_cc_owner.cctl_losstype lt, CSR_CC_OWNER.cctl_brand_ins br, csr_cc_owner.cc_claimcontactrole ccr, csr_cc_owner.cctl_contactrole cr
WHERE c.ID = cc.claimid
and c.losstype = lt.ID
and cc.contactid = con.ID
and con.HAPPYRECEIVESMS_INS = 2
and con.ADDRESSBOOKFINGERPRINT IS NULL
and c.CREATEUSERID = u.ID
and con2.ID = u.CONTACTID
and c.createtime > SYSDATE-1
and c.POLICYID = pol.ID
and pol.BRAND_INS = br.ID
and c.STATE in (2,3)
and c.HOWREPORTED = ‘10001’
and c.REPORTEDBYTYPE = ‘10011’
and ccr.claimcontactid = cc.ID
and ccr.ROLE = CR.ID
and ccr.ROLE IN (1, 2, 13)
ORDER BY 3 asc

Each person can have multiple CR.L_EN_UK values, so what I often end up with is 3 rows for the same person where the information in the last column varies. Due to my IN (1, 2, 13) the values of this can be Policyholder, Insured or Claimant. What I want to do is only return one row per person but if they have Policyholder or Insured then only return one of those (ignoring Claimant) and if they have neither of those two but have Claimant then only return Claimant.

Any ideas on how I would do this?

2

Hi hirenshah !

Look this example:

CREATE TABLE RANK_SAMPLE
(
ID NUMBER(10) NOT NULL,
CREATE_TIME DATE,
CLASS_TYPE VARCHAR2(50 BYTE)
)

/

–Data

INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080625, TO_DATE(‘14/10/2014 10:32’, ‘DD/MM/YYYY HH24:MI’), ‘T2’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080640, TO_DATE(‘14/10/2014 10:34’, ‘DD/MM/YYYY HH24:MI’), ‘T3’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080619, TO_DATE(‘14/10/2014 10:34’, ‘DD/MM/YYYY HH24:MI’), ‘T4’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080618, TO_DATE(‘14/10/2014 10:34’, ‘DD/MM/YYYY HH24:MI’), ‘T5’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080628, TO_DATE(‘14/10/2014 10:39’, ‘DD/MM/YYYY HH24:MI’), ‘T6’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080621, TO_DATE(‘14/10/2014 10:29’, ‘DD/MM/YYYY HH24:MI’), ‘T6’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080623, TO_DATE(‘14/10/2014 10:31’, ‘DD/MM/YYYY HH24:MI’), ‘T6’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080620, TO_DATE(‘14/10/2014 10:29’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080643, TO_DATE(‘14/10/2014 10:37’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080642, TO_DATE(‘14/10/2014 10:36’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080641, TO_DATE(‘14/10/2014 10:34’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080639, TO_DATE(‘14/10/2014 10:50’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080638, TO_DATE(‘14/10/2014 10:50’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080635, TO_DATE(‘14/10/2014 10:43’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080636, TO_DATE(‘14/10/2014 10:45’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080637, TO_DATE(‘14/10/2014 10:47’, ‘DD/MM/YYYY HH24:MI’), ‘T1’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080624, TO_DATE(‘14/10/2014 10:31’, ‘DD/MM/YYYY HH24:MI’), ‘T7’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080644, TO_DATE(‘14/10/2014 10:37’, ‘DD/MM/YYYY HH24:MI’), ‘T7’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080634, TO_DATE(‘14/10/2014 10:43’, ‘DD/MM/YYYY HH24:MI’), ‘T7’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080631, TO_DATE(‘14/10/2014 10:42’, ‘DD/MM/YYYY HH24:MI’), ‘T7’);
INSERT INTO RANK_SAMPLE(ID, CREATE_TIME, CLASS_TYPE) VALUES(2080629, TO_DATE(‘14/10/2014 10:39’, ‘DD/MM/YYYY HH24:MI’), ‘T7’);

/

– Normal “Select”

SELECT * FROM RANK_SAMPLE
ORDER BY CLASS_TYPE, CREATE_TIME

– Look for multiple data column CLASS_TYPE

8814.Captura1.jpeg

– Now, using the RANK function enumerate the rows of the same CLASS_TYPE according to the order we need.

–In this case I need to get the record with the lowest date of each type, then in rank function we set CLASS_TYPE column and order by CLASS_TYPE and CREATE_TIME

SELECT RANK() OVER(PARTITION BY CLASS_TYPE ORDER BY CLASS_TYPE, CREATE_TIME) ITEM,
ID, CREATE_TIME, CLASS_TYPE
FROM RANK_SAMPLE
ORDER BY CLASS_TYPE

– Look data

Captura2.jpeg

–Finally we use a sub query and select where item = 1

SELECT ID, CREATE_TIME, CLASS_TYPE
FROM (
SELECT RANK() OVER(PARTITION BY CLASS_TYPE ORDER BY CLASS_TYPE, CREATE_TIME) ITEM,
ID, CREATE_TIME, CLASS_TYPE
FROM RANK_SAMPLE
) XX
WHERE ITEM = 1
ORDER BY CLASS_TYPE

Captura3.jpeg

I hope can help you.

Apologies if I have problems with translation to English.

Good luck !

3

Hi isalgado, your post was very help and gets me partially to where I want to be (your English translation was perfect by the way). I want to be able to pick my CLASS_TYPE (I will use your example) depending on what there is in there?

For example, if I have 3 rows for ID 2080620 called T1, T2 and T3, if all 3 exist I want to give it rank 1. If only T1 and T2 exist I want to give it a rank 2, and lastly if only T3 exists then I want to give it a rank 3. Is that possible?

4

I have made some progress actually:

select BR.L_EN_UK, c.claimnumber, to_char(c.CREATETIME, ‘DD/MM/YYYY HH24:MI:SS’), lt.NAME, con.HAPPYRECEIVESMS_INS, con.firstname, con.lastname, con.NAME, con.CELLPHONE,

con2.FIRSTNAME, con2.LASTNAME, CR.L_EN_UK, RANK() OVER(PARTITION BY to_char(c.CREATETIME, ‘DD/MM/YYYY HH24:MI:SS’), con.lastname ORDER BY to_char(c.CREATETIME, ‘DD/MM/YYYY HH24:MI:SS’) asc, con.lastname desc, CR.L_EN_UK desc) Test

FROM CSR_CC_OWNER.cc_claim c, CSR_CC_OWNER.cc_claimcontact cc, CSR_CC_OWNER.cc_contact con,

CSR_CC_OWNER.CC_USER u, CSR_CC_OWNER.cc_contact con2, CSR_CC_OWNER.cc_policy pol, csr_cc_owner.cctl_losstype lt, CSR_CC_OWNER.cctl_brand_ins br,

csr_cc_owner.cc_claimcontactrole ccr, csr_cc_owner.cctl_contactrole cr

WHERE c.ID = cc.claimid

and c.losstype = lt.ID

AND cc.contactid = con.ID

AND con.HAPPYRECEIVESMS_INS = 2

and con.ADDRESSBOOKFINGERPRINT IS NULL

and c.CREATEUSERID = u.ID

and con2.ID = u.CONTACTID

and c.createtime > SYSDATE-1

and c.POLICYID = pol.ID

and pol.BRAND_INS = br.ID

and c.STATE in (2,3)

and c.HOWREPORTED = ‘10001’

and c.REPORTEDBYTYPE = ‘10011’

AND ccr.claimcontactid = cc.ID

AND ccr.ROLE = CR.ID

AND ccr.ROLE IN (1, 2, 13)

ORDER BY 3 asc, 7 asc, 12 desc

This gets me close to what I want. Now I want to select all the rows that select is returning, but only get where TEST (RANK in your case) is 1. This bit was throwing up errors because I am grabbing data from multiple tables and it was saying they weren’t defined.

5

Not sure if this is exactly what your talking about, but it’s probably close. There might be other ways to do this with a single query, but sometimes it’s just easier to create something like that with a pl/sql block imo. You then have control over the data as well.

I based it on the data isalgado provided, but applied your requirement for the class type of ‘Claimant’. You might need to adjust it a bit to fit you exact needs, but this might get you started. Then just put it in a simple function, and then you can call it directly from a select statement as needed.

declare

l_class_type varchar2(20) := null;

/– Main Cursor –/

cursor rank_cursor is

select distinct id c_id

from rank_sample

order by id;

begin

for cur_rec in rank_cursor loop

begin

l_class_type := null;

begin

select class_type

into l_class_type

from rank_sample

where class_type = ‘Claimant’

and id = cur_rec.c_id;

exception when others then

l_class_type := null;

end;

dbms_output.put_line(cur_rec.c_id ||’, '||nvl(l_class_type,‘Insured’));

end;

end loop;

exception when others then

dbms_output.put_line('Error: '||SQLERRM);

end;

/

6

I managed to do it in the end! YAY!