Join two sources and mapping table to produce status column.

[Anand]

Hi Tim,

I need your expert advice on the following requirement:-

I have two main tables namely HLR and BSS ,each contains unique "SUBSCRIBER_NO" , list of comma separated "Service ID's" and their comma separated "Service Status's ( A- Active , I - In Active)". These two tables can be joined on column "SUBSCRIBER_NO".
Table creation and population script is as follows:-

Code: Select all

create table HLR(SUBSCRIBER_NO Number, Service_ID varchar2(100), Service_Status VARCHAR2(100));
insert into HLR (SUBSCRIBER_NO, SERVICE_ID, SERVICE_STATUS)
values (100001, '110,111', 'A,I');
insert into HLR (SUBSCRIBER_NO, SERVICE_ID, SERVICE_STATUS)
values (100002, '110,115', 'I,A');
insert into HLR (SUBSCRIBER_NO, SERVICE_ID, SERVICE_STATUS)
values (100003, '110,115', 'A,I');

create table BSS(Subscriber_No Number, Service_ID varchar2(100), Service_Status VARCHAR2(100));
insert into BSS (SUBSCRIBER_NO, SERVICE_ID, SERVICE_STATUS)
values (100001, '10,15', 'I,A');
insert into BSS (SUBSCRIBER_NO, SERVICE_ID, SERVICE_STATUS)
values (100002, '15,25', 'A,I');
insert into BSS (SUBSCRIBER_NO, SERVICE_ID, SERVICE_STATUS)
values (100003, '15,-1', 'A,I');


In addition to the above two tables, I have one mapping table "HLR_BSS_MAPPING" which contains the mapped "Service_ID" of these two sources(i.e. HLR & BSS). Creation /Population script is as follows:-

Code: Select all

create table HLR_BSS_MAPPING(Group_ID Number, MAPPED_SERVICE_ID_SOURCES Number);
insert into hlr_bss_mapping (GROUP_ID, MAPPED_SERVICE_ID_SOURCES)
values (1, 110);
insert into hlr_bss_mapping (GROUP_ID, MAPPED_SERVICE_ID_SOURCES)
values (1, 15);
insert into hlr_bss_mapping (GROUP_ID, MAPPED_SERVICE_ID_SOURCES)
values (2, 111);
insert into hlr_bss_mapping (GROUP_ID, MAPPED_SERVICE_ID_SOURCES)
values (2, 10);
insert into hlr_bss_mapping (GROUP_ID, MAPPED_SERVICE_ID_SOURCES)
values (3, 115);
insert into hlr_bss_mapping (GROUP_ID, MAPPED_SERVICE_ID_SOURCES)
values (3, 25);


I have to look into this mapping table to decide which Service ID of "HLR" source is to be mapped with which Service ID of "BSS" source.
For example :- The Service ID "110" of source "HLR" (taken from first row value 110,111') will be matched with Service ID "15" of source "BSS" (taken from first row value 10,15') , as these two Service ID's have common Group_ID in "HLR_BSS_MAPPING" table( i.e. GROUP_ID 1).

I have to write a Query/Procedure(Preferably Query) which will produce a column "STATUS" which holds the following three status:-
1) Status "0" (Matched Services) : These services are those , whose Comma separated Service Status corresponding to the Comma Separated Service ID's are same in both HLR and BSS sources.
For example ,The SUBSCRIBER_NO "100001" should have the Matched Status as from the comma separated service ID's , service ID "110" (of HLR) & "15 " (of BSS) has the same Service Status as "A-Active" and also second Service ID in this i.e 111(HLR) and 10(BSS) has the same Service Status as "I-In Active".

2) Status "1" (Mis-Matched Services) : These services are those , whose Comma separated Service Status corresponding to the Comma Separated Service ID's are not same in both HLR and BSS sources.
For example ,The SUBSCRIBER_NO "100002" should have the Mis-Matched Status as the Service Status of Service ID's 110 & 15 are "A" and "I" which is not same , same applicable for Service ID's 115 & 25.

3) Status "2" (Missing Services): These services are those , whose Service ID is not mapped in "HLR_BSS_MAPPING" table i.e. Service ID not available in HLR_BSS_MAPPING table.For example The SUBSCRIBER_NO "100003" should have the Missing Status as the Service ID "-1" of source "BSS" is not available/mapped in "HLR_BSS_MAPPING" table.

Can you guide me to write a query /PL SQL for the above ,that query/procedure should
1) first join the HLR & BSS table based on "SUBSCRIBER_NO" column.
2) then find out the mapped service ID based on "GROUP_ID" column of HLR_BSS_MAPPING table.
3) then produces a third "STATUS" column based on the "SERVICE_STATUS" column value for the mapped Service_ID of the HLR and BSS sources.

I am stuck very badly and need your expert advice,please guide me.

Thanks in advance.

Thanks & Regards,
Anand Kumar Ojha

[Tim...]

Hi.

Not sure what you mean by this, "then find out the mapped service ID based on "GROUP_ID" column of HLR_BSS_MAPPING table."

You have nothing in the tables to join to GROUP_ID. I could join to MAPPED_SERVICE_ID_SOURCES, but this doesn't appear to be what you are asking for. Help?

Cheers

Tim...

[Anand]

By the line "then find out the mapped service ID based on "GROUP_ID" column of HLR_BSS_MAPPING table."

I mean to say that ,
From the table "HLR" for SUBSCRIBER_NO "100001" , pick the first "Service ID" say 110 (from the comma separated service list i.e. '110,111' ),
search this service ID in the column "MAPPED_SERVICE_ID_SOURCES" of table "HLR_BSS_MAPPING" and read its corresponding "Group_ID" i.e. 1,
Under the same Group ID we have Service ID as 15 in the column "MAPPED_SERVICE_ID_SOURCES" , this value (i.e. 15) will be the Service ID for SUBSCRIBER_NO "100001" in the table "BSS".

This way we can determine that the Service ID of each sources against which we need to check their corresponding Service Status "Active(A) or In-Active(I)"
,if the service status are same then "Status" column would have the value "0(Matched)", if not then "Status" would be "1(Mis-Matched)", and if
Service ID available in HLR and BSS table is not found in "HLR_BSS_MAPPING" table then the "Status" would be "2(Missing)".

I hope I clear myself.

Regards,
Anand Ojha

[Tim...]

Hi.

I'm still not really sure what you are asking me to do. Initially you said to join the two tables, which is pretty easy.

Code: Select all

SELECT hlr.subscriber_no AS subscriber_no_hlr,
       SUBSTR(hlr.service_id, 1, INSTR(hlr.service_id, ',')-1) AS service_id_hlr_1,
       SUBSTR(hlr.service_id, INSTR(hlr.service_id, ',')+1) AS service_id_hlr_2,
       hlr.service_status AS service_status_hlr,
       bss.subscriber_no AS subscriber_no_bss,
       SUBSTR(bss.service_id, 1, INSTR(bss.service_id, ',')-1) AS service_id_bss_1,
       SUBSTR(bss.service_id, INSTR(bss.service_id, ',')+1) AS service_id_bss_2,
       bss.service_status AS service_status_bss
FROM   hlr
       JOIN bss ON hlr.subscriber_no = bss.subscriber_no

Then you say use this data to join to the mapping table, which is pretty easy also,

Code: Select all

WITH base_data AS (
  SELECT hlr.subscriber_no AS subscriber_no_hlr,
         SUBSTR(hlr.service_id, 1, INSTR(hlr.service_id, ',')-1) AS service_id_hlr_1,
         SUBSTR(hlr.service_id, INSTR(hlr.service_id, ',')+1) AS service_id_hlr_2,
         hlr.service_status AS service_status_hlr,
         bss.subscriber_no AS subscriber_no_bss,
         SUBSTR(bss.service_id, 1, INSTR(bss.service_id, ',')-1) AS service_id_bss_1,
         SUBSTR(bss.service_id, INSTR(bss.service_id, ',')+1) AS service_id_bss_2,
         bss.service_status AS service_status_bss
  FROM   hlr
         JOIN bss ON hlr.subscriber_no = bss.subscriber_no
)
SELECT a.*,
       b.group_id AS group_id_hlr_1,
       c.group_id AS group_id_hlr_2,
       d.group_id AS group_id_bss_1,
       e.group_id AS group_id_bss_2
FROM   base_data a
       JOIN hlr_bss_mapping b ON a.service_id_hlr_1 = b.mapped_service_id_sources
       JOIN hlr_bss_mapping c ON a.service_id_hlr_2 = c.mapped_service_id_sources
       JOIN hlr_bss_mapping d ON a.service_id_bss_1 = d.mapped_service_id_sources
       JOIN hlr_bss_mapping e ON a.service_id_bss_2 = e.mapped_service_id_sources;

Not sure what I am supposed to do from here...

Did you really mean join the two together in step (1), or do you mean to only join via the mapping table? I'm confused.

Also, you've not explained if there can be more than 2 elements in the comma separated column. If so, then this method would not work.

I think your problem stems from a badly designed database. Holding comma-separated-values in a column is not really in line with relational design. This should actually be stored in another table, with an FK to the current table. That way joins would be obvious. Maybe you should consider reviewing your design in light of this requirement?

Cheers

Tim...

[Anand]

Regarding your questions.

Yes, you are right I mean join the two together in step (1), that is query which will join the two tables HLR and BSS based on subscriber no and at the same time that query should be capable enough to join with mapping table also.

Secondly ,more than 2 elements is comma separated column is definitely possible, in fact I mentioned just element for the sake of clarity.

Thirdly,We am keeping comma-separated-value in a column just for the simple reason that "a subscriber can be enrolled upto maximum of 50 different services which means there would be 50 different rows against each subscriber ", something like this:-

TABLE NAEM : HLR
SUBSCRIBER_NO SERVICE_ID SERVICE_STATUS
------------------ ---------------- --------------------------
100001 110 A
100001 111 I
100001 112 A
100001 113 I
100001 114 A
-----
----
--- UPTO 50 times
100001 115 I

Please note that I have 50 millions subscribers so total rows generated for one single table would be something around 50 millions subscribers * 50 services.

*** Please advice me , if there could be better database design.

Eagerly waiting for your advice.

Thanks in advance,

With Regards,
Anand Kumar OJha

[Tim...]

Hi.

There would not be 50 rows. There would be 1 row, with 50 rows in a child table to hold the services for that row. This would be the correct relational design.

For example, HRL_SERVICES containing the SUBSCRIBER_NO and SERVICE_ID. One HRL record, many HRL_SERVICES. That's what relational databases do.

Cheers

Tim...

[Anand]

Ok, even though I go with your approach and keep a separate table for services say HLR (parent table) and HLR_SERVICES(for keeping 50 different services) and also BSS(Parent table) and BSS_SERVICES(for keeping 50 different services) , then don't you think that in order to accomplish my requirement I have to perform a join between HLR, HLR_SERVICES which generates 50 MILLIONS * 50 different services rows and similarly BSS with BSS_SERVICES which generates
30 MILLIONS * 50 different services,hence this two tables together will generates ( 50 MILLIONS * 50 different services * 30 MILLIONS * 50 different services) which will be a huge amount of rows. In order to minimize this huge number of rows we have thought of the comma-separated-values in columns.Please suggest.

Please note that my requirement is to ,
Display all those rows after the join of HLR and BSS table (based on subscriber no) where "Service Status (i.e. Active/InActive) is Mismatched.
For example, for particular Subscriber No say 10001 ,Service Status in "HLR" table is "A(Active)" while in "BSS" table same subscriber has status "I(In-Active) this is the example of Mismatch.Similarly I have to find out the "Matched" and "Missing" Subscriber as already depicted intially.

[Tim...]

Hi.

OK. So let's assume you do not use the chaild table approach and store 50 services in a CSV. You now have to manually process those using something like a pipelined table function to make them appear like a table. Why? Because you don't know how many you are going to have, so you must take the dynamic approach. In splitting this up using a pipelined table, you are effectively creating the equivalent of the "good design" at runtime, using PL/SQL, which is going to eat at your CPU. You will then do all the same joins that you would with real tables. The net result is your current solution will have to do all the same work, but in addition, take your list and turn it into tables which will require extra effort.

If you look at what I was doing, I was pulling the data apart and placing it into the existing row. I was able to do that because I believed there were a fixed number of services (2). If there are not, then you have no choice but to take the row-based approach.

Just to repeat, your design is bad. Storing comma-separated values in a single column goes against all relation design rules. Think 3rd normal form.

Cheers

Tim...