Hello folks,
I tried searching for this scenario in OTN and could not find any hits so I am posting the question here.
I have two tables -- One with detail records and the other being a code table. I need to join these two tables based on the code and the length of the code. Let me explain using examples.
Scripts for table creation and inserts
create table ILLNESS_CODES(illness_code varchar2(4), illness_description varchar2(100));
create table PATIENT_TB(patient_id varchar2(4), primary_cause varchar2(4));
insert into illness_codes values('B10', 'Flu');
insert into illness_codes values('B30', 'Hepatitis');
insert into illness_codes values('B301', 'Hepatitis A');
insert into illness_codes values('B302', 'Hepatitis B');
insert into illness_codes values('B303', 'Hepatitis C');
insert into patient_tb values ('1001', 'B101');
insert into patient_tb values ('1002', 'B102');
insert into patient_tb values ('1003', 'B30');
insert into patient_tb values ('1004', 'B301');
insert into patient_tb values ('1005', 'B302');
insert into patient_tb values ('1006', 'B302');
insert into patient_tb values ('1007', 'B303');
insert into patient_tb values ('1008', 'B30');
As you can see that patients *1001* and *1002* do not have corresponding codes in the Master table. In that case, I only want the first 3 characters from the ILLNESS_CODES table.
However, for B30, there is a Code and the same for B301, B302 and B303 where I would like to pick up the description based on the exact code.
Sample Output
Patient ID Illness Description
=====================================
1001 Flu
1002 Flu
1003 Hepatitis
1004 Hepatitis A
1005 Hepatitis B
1006 Hepatitis B
1007 Hepatitis C
1008 Hepatitis
Thanks in advance!