0

I have a requirement where I have column 'NOTECODE' in database table POLICY which contains multiple Note Codes in comma separated format. Ex: CC,TD,HL

POLICY :

 POLICYNO  | NOTECODE
 ----------|----------
 ABC001    | CC,TD,HL
 ----------|----------
 ABC002    | CC,TD

ALSO there is a CODEDESC table which contains the description as below

 CODE  | DESCRIPTION
 ------|----------
 CC    | Credit Card Payment
 ------|----------
 TD    | Yee
 ------|--------------
 HL    |Hospital Credit (Letter)

I want to fetch data using query which gives the POLICYNO, NOTECODE and DESCRIPTION as mentioned below:

 POLICYNO  | NOTECODE   | DESCRIPTION
 ----------|----------  |----------
 ABC001    | CC,TD,HL   | Credit Card Payment, Yee, Hospital Credit (Letter)
 ----------|----------  |----------
 ABC002    | CC,TD      | Credit Card Payment, Yee

How can I do it?

halfer
  • 19,824
  • 17
  • 99
  • 186
Ashutosh
  • 111
  • 14
  • Normalize your tables. Storing the values as comma separated string is in violation of basic rules of a Relational database. – Kaushik Nayak May 08 '19 at 04:55
  • Hi Kaushik, Normalizing the table is not possible right now. Have to handle it in query – Ashutosh May 08 '19 at 05:24
  • Now is the right time. I understand that it was someone else's bad design decision that ended up with this problem, but changing that now will go long way to help future programmers including you. Trust me, you will keep facing problems because of this flawed table structure for ages to come. Read [this](https://stackoverflow.com/a/3653574/7998591) answer which reiterates this fact. – Kaushik Nayak May 08 '19 at 05:32
  • Moreover, It's not that I or anybody else here can't provide a solution to your problem. You'll find many such already answered each day. But, considering numerous questions on SO asking solutions to this problem regularly, it becomes imperative to assert the importance of following the rules of normalisation. It is to avoid programmers from encountering such issues frequently and also to assist them in building better systems. – Kaushik Nayak May 08 '19 at 05:42
  • Hi Ashutosh, there is something odd about your data in that CODEDESC looks like a normal table but POLICY does not because of having multiple values with commas in between. There is probably a way to do what you want but we need more information. It almost looks like someone pulled NOTECODE and DESCRIPTION out of some other table and concatenated the values with a ",". – wavery May 08 '19 at 05:44

1 Answers1

1

You can use JOIN and aggregation:

select p.policyno, p.notecode,
       listagg(description, ', ') within group (order by instr(p.notecode, cd.code))
from policy p left join
     codedesc cd
     on ',' || p.notecode || ',' like '%,' || cd.code || ',%'
group by p.policyno, p.notecode;

Note that this is not efficient! This is a work-around because you are stuck with a really bad data model. Your efforts should really be to fix the data model.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786