Hi,
I would like to obtain the code from location_lookup table based on some descriptive text in the location_source table but this would require a join using a like condition. Also there are multiple location lookups with unique codes but the requirement would be to select the first code:
create table location_source
(code number,
description varchar2(100)
);
insert into location_source values('', 'check in london');
insert into location_source values('', 'flight Paris 9.30');
insert into location_source values('', 'baggage allowance 20kg Hong kong');
insert into location_source values('', 'check out 6.30');
insert into location_source values('', 'new york 23rd Jul');
insert into location_source values('', 'flight jy688 sydney');
create table location_lookup
(code number
location_lookup varchar2(10)
);
insert into location_lookup values (001, 'London');
insert into location_lookup values (002, 'London');
insert into location_lookup values (003, 'Paris');
insert into location_lookup values (004, 'Hong Kong');
insert into location_lookup values (005, 'Paris');
insert into location_lookup values (006, 'New York');
The SQL looks something like this:
select s.*, min(l.code) , l.location_lookup
from location_source s, locaction_lookup l
and UPPER(s.description||'%') like upper(l.location_lookup||'%')
group by s.*, l.location_lookup
Output Required:
| Code | Description | Code | Location_Lookup |
| | check in london | 001 | London |
| | flight Paris 9.30 | 003 | Paris |
| | baggage allowance 20kg Hong kong | 004 | Hong Kong |
| | check out 6.30 | | |
| | new york 23rd Jul | 006 | New York |
| | flight jy688 sydney | | |