How to retrieve into records data delimited into one single column 2

[amr67]

I have data entered into one column 'REGION' the same way as PARIS11,30,CANNE40,C22,TOULOUSE1.

Is there any possibility to retrieve each region in one single record?

 

[SantaMufasa]

Sorry...I meant please DESCRIBE the existing input table that contains the strung-together REGION subfields.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[amr67]

The describe output is (2 columns):

Name Type
INVENTORY_ITEM_ID NUMBER
ACTUAL_COST NUMBER
TRANSACTION_ID NUMBER
TRANSACTION_TYPE_NAME VARCHAR2(30)
TRANSACTION_ACTION VARCHAR2(29)
TRANSACTION_SOURCE_TYPE_NAME VARCHAR2(30)
TRANSACTION_SOURCE_ID NUMBER
TRANSACTION_SRC_HDR VARCHAR2(4000)
PO_HEADER_ID NUMBER
SALES_ORDER_ID NUMBER
ACCOUNT_ID NUMBER
REQUISITION_HEADER_ID NUMBER
TRANSACTION_SOURCE_NAME VARCHAR2(30)
TRANSACTION_DATE DATE
TRANSACTION_QUANTITY NUMBER
TRANSACTION_UOM VARCHAR2(3)
INVENTORY_ITEM_NAME VARCHAR2(122)
CATEGORY VARCHAR2(40)
CODE_DESCRIPTION VARCHAR2(40)
ORIGIN VARCHAR2(40)
ORGANIZATION_NAME VARCHAR2(60)
SUBINVENTORY_NAME VARCHAR2(10)
REVISION VARCHAR2(3)
SHIPMENT_NUMBER VARCHAR2(30)
TRANSACTION_REASON_NAME VARCHAR2(30)
TRANSACTION_REFERENCE VARCHAR2(240)
MN NUMBER
YR NUMBER

NB: The field required to be parsed is TRANSACTION_REFERENCE

 

[SantaMufasa]

Thanks...and what is the column name that represents "Maintenance employee ID"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

...and what is the actual name of the table that you described?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[amr67]

The actual name of the table is MTL_TRANS. This is actually a VIEW.

The field representing the employee is SUBINVENTORY_NAME and it a branch under ORGANIZATION_NAME. That is the ORGANIZATION_NAME has many SUBINVENTORY_NAME. Each employee is represented by a SUBINVENTORY_NAME that has many items to be installed in his region.

 

[SantaMufasa]

AMR,

You will see with the accompanying code, that disobeying First Normal Form (as your table’s design does with the REGION data) causes monumental headaches in the relational database world. Neither you, nor I, nor anyone else should need to write the code that I had to write to compensate for the massively silly design that someone used for your REGION (TRANSACTION_REFERENCE) column.

Having said that, here is a solution for your need:

Section 1 – Sample source data:

col transaction_reference format a60
col subinventory_name format a17
select SUBINVENTORY_NAME, transaction_reference from MTL_TRANS
/

SUBINVENTORY_NAME TRANSACTION_REFERENCE
----------------- -------------------------------------------------------
Dave              PARIS11,30,CANNES40,C22,TOULOUSE1
Amr               PARIS11,30,CANNES40,C22,TOULOUSE1
Barb              CAEN14,37,55,DIJON,21,D33,LIMOGES12,LYON5,201,M6,NIMES3

3 rows selected.

Section 2 – Final results showing the transformation of the sample source data into the results you requested. (Delay running the following code until after you have run Section 3 through Section 6, below. The purpose of showing the final results here [i.e., prior to Sections 3 – 6] is to preview the proof-of-concept for your results.) :

 col emp_id format a6
col region format a20
select * from region_view
 order by 1,2
/

EMP_ID REGION
------ ----------
Amr    CANNES40
Amr    CANNESC22
Amr    PARIS11
Amr    PARIS30
Amr    TOULOUSE1
Barb   CAEN14
Barb   CAEN37
Barb   CAEN55
Barb   DIJON
Barb   DIJON21
Barb   DIJOND33
Barb   LIMOGES12
Barb   LYON5
Dave   CANNES40
Dave   CANNESC22
Dave   PARIS11
Dave   PARIS30
Dave   TOULOUSE1

18 rows selected.

Section 3 – Creation of a synonym (“MANY_ROWS”) of an object that always exists in every Oracle database. I use “MANY_ROWS” to generate as many rows as you might ever need for your unstringing data transformation:

 create or replace synonym many_rows for sys.all_tab_columns
/

Synonym created.

Section 4 – Creation of a VIEW from your original source table (“MTL_TRANS”) that simply matches the late-arriving table and column names of your table, with code that I devised already, using the table and column names that I had “guessed” from your original specifications. (I created this VIEW to avoid having to modify code that I already had working.):

create or replace view employees as
select SUBINVENTORY_NAME id, transaction_reference region
  from MTL_TRANS
/

View created.

Section 5 – Creation of an Oracle PL/SQL PACKAGE and PACKAGE BODY (“REGION_PACK”) that represents the “brains” of your data transformation:

 create or replace package region_pack is
    num_regions             number;
    hold_emp employees%rowtype;
    hold_id                 varchar2(20);
    hold_remaining_str      varchar2(1000);
    next_piece              varchar2(100);
    current_region          varchar2(100);
    current_prefix          varchar2(100);
    first_time              boolean := true;
    cursor e is select * from employees;
    function get_num_regions return number;
    function get_emp_id return varchar2;
    function get_region return varchar2;
end;
/

Package created.

create or replace package body region_pack is
    procedure get_next_rec is
        begin
            if not region_pack.e%isopen then
                open region_pack.e;
            end if;
            fetch e INTO hold_emp;
            if region_pack.e%notfound then
                close region_pack.e;
                hold_id := null;
                hold_remaining_str := null;
                first_time := true;
            else
                hold_id :=hold_emp.id;
                hold_remaining_str :=hold_emp.region;
            end if; 
        end;
    function get_num_regions return number is
        begin
            if num_regions is null then
                select sum(length(region)-length(translate(region,'x,','x')))
                  into num_regions
                  from employees;
            end if;
            return num_regions;
        end;
    function get_next_parse return varchar2 is
            comma_loc               number;
        begin
            comma_loc := instr(hold_remaining_str,',');
            if comma_loc > 0 then
                next_piece := substr(hold_remaining_str,1,comma_loc-1);
                hold_remaining_str := substr(hold_remaining_str,comma_loc+1);
            else
                next_piece := hold_remaining_str;
                get_next_rec;
            end if;
            if length(next_piece) > 4 then
                current_region := next_piece;
                current_prefix := translate(next_piece,'x0123456789','x');
            else
                current_region := current_prefix||next_piece;
            end if;
            return current_region;
        end;
    function get_emp_id return varchar2 is
        begin
            if first_time then
                get_next_rec;
		first_time := false;
            end if;
            return hold_id;
        end;
    function get_region return varchar2 is
        begin
            if first_time then
                get_next_rec;
		first_time := false;
            end if;
            return get_next_parse;
        end;
end;
/

Package body created.
*****************************************************************************

Section 6 – Creation of a VIEW (“REGION_VIEW”) that accesses the “REGION_PACK” package, synthesising the rows you requested from the strung-together subfields from your original table.

 create or replace view region_view as
select to_number(region_pack.get_emp_id) emp_id
      ,region_pack.get_region region
  from many_rows
 where rownum <= region_pack.get_num_regions
/

View created.

You should be able to run the code, without modification, from all of the above sections. Let me know if you have any difficulty with any of the above code.

And please, Please, PLEASE prevent anyone within your scope of influence from ever designing data like that again!!!

Let us know how this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[amr67]

Many thanks for your support.

Please allow me some time to test.

I just have one question regarding the sample output above. I can see 18 Rows selected, while the expected output should be 21. Am I right?

 

[SantaMufasa]

Good catch, AMR. My error was that my code was producing as many rows as there are commas in your data. The fix is to also add "1" for each row, as well. (I highligted my correction in bold font, below.) Here, then, is the corrected code that should replace the "CREATE OR REPLACE PACKAGE BODY..." code:

create or replace package body region_pack is
    procedure get_next_rec is
        begin
            if not region_pack.e%isopen then
                open region_pack.e;
            end if;
            fetch e INTO hold_emp;
            if region_pack.e%notfound then
                close region_pack.e;
                hold_id := null;
                hold_remaining_str := null;
                first_time := true;
            else
                hold_id :=hold_emp.id;
                hold_remaining_str :=hold_emp.region;
            end if; 
        end;
    function get_num_regions return number is
        begin
            if num_regions is null then
                select sum(length(region)-length(translate(region,'x,','x')))[b]+count(*)[/b]
                  into num_regions
                  from employees;
            end if;
            return num_regions;
        end;
    function get_next_parse return varchar2 is
            comma_loc               number;
        begin
            comma_loc := instr(hold_remaining_str,',');
            if comma_loc > 0 then
                next_piece := substr(hold_remaining_str,1,comma_loc-1);
                hold_remaining_str := substr(hold_remaining_str,comma_loc+1);
            else
                next_piece := hold_remaining_str;
                get_next_rec;
            end if;
            if length(next_piece) > 4 then
                current_region := next_piece;
                current_prefix := translate(next_piece,'x0123456789','x');
            else
                current_region := current_prefix||next_piece;
            end if;
            return current_region;
        end;
    function get_emp_id return varchar2 is
        begin
            if first_time then
                get_next_rec;
		first_time := false;
            end if;
            return hold_id;
        end;
    function get_region return varchar2 is
        begin
            if first_time then
                get_next_rec;
		first_time := false;
            end if;
            return get_next_parse;
        end;
end;
/

Here is the output that results from the revised code:

col emp_id format a6
col region format a20
select * from region_view
 order by 1,2
/

EMP_ID REGION
------ ----------
Amr    CANNES40
Amr    CANNESC22
Amr    PARIS11
Amr    PARIS30
Amr    TOULOUSE1
Barb   CAEN14
Barb   CAEN37
Barb   CAEN55
Barb   DIJON
Barb   DIJON21
Barb   DIJOND33
Barb   LIMOGES12
Barb   LYON201
Barb   LYON5
Barb   LYONM6
Barb   NIMES3
Dave   CANNES40
Dave   CANNESC22
Dave   PARIS11
Dave   PARIS30
Dave   TOULOUSE1

21 rows selected.

Let me know if this is closer to your expectations.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[amr67]

Regarding the sys.all_tab_columns table.

I found this table present in the Oracle Application database and filled with many data.

Should this table be used?

Also, I am connecting to the database using APPS/APPS, while the schema used for this table is sys. Is this correct and should be kept like this?

 

[SantaMufasa]

Yes, AMR, I purposely used "sys.all_tab_columns table" because:

1) Your "MTL_TRANS" table always has far fewer rows than the number of rows that you actually need for output

2) We need a "table" to drive your query that will always have more rows than you will ever need to "unstring" your output,

3) "sys.all_tab_columns table" always has thousands of rows, whose purpose, in your case is simply to drive your query...We are not interested in any columns of data from "sys.all_tab_columns table"; we are simply interested in the rows of data, to be "place holders" to drive the excess number of output rows you need that exceed the number of input rows (since each 1 row of your input contains what will become many rows of output, right?...This contrivance that you see here represents the great damage that results from your table's poor design.) To be clear, a single REGION column on a single row of your table should have been many rows on a well-designed table.

If you are suspicious of the method(s) that I have used here to "untangle you from your knickers", I recommend our soliciting a second opinion from the other Oracle experts (that have already responded here to your thread) to provide a "sanity check" for my work. Beilstwh, Turkbear, LKBrwnDBA, others, would you like to dive in here???

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Since it's 4:00 a.m. in my timezone, I believe that I'll retire for a few hours of sleep before this all starts over again.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[amr67]

I am getting ORA-01722: Invalid number, after a while from quering the region_view.

Any idea on how to debug the error?

 

[SantaMufasa]

Yes, AMR, if I had your data, I would begin back tracking from the error and would begin substituting benign values for pieces of the code. For example, I would run a standalone query of an adjusted versoin of the "CREATE VIEW REGION_VIEW..." code to read:

select 'TEST'  from many_rows
 where rownum <= region_pack.get_num_regions
/

If that works, then you have ruled out (as the offending expression) one of the two numeric expressions in my code: "NUM_REGIONS".

I would then continue doing a "piece-meal" isolation of the code using a similar technique: adding back in a piece of code at a time until you add in a piece that causes the error again. At that point, you can start isolating data values until you discover the offending data value.

Please advise us how your troubleshooting progresses.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[amr67]

Yes, the select you provided is working for TEST.

It seems that this error comes from unexpected data in the field like this:

Dave PARIS11,30,CANNES40,C22,TOULOUSE1
Amr PARIS11,30,CANNES40,C22,TOULOUSE1
Barb CAEN14,37,55,DIJON,21,D33,LIMOGES12
Tarek 002027050
Sami PARIS11&30,CANNES40&C22,TOULOUSE1
Amr1 PARIS11-30-CANNES40-C22-TOULOUSE1
Sami1 PARIS11/30/CANNES40/C22/TOULOUSE1

Sorry, for this but this was what different users have entered.

 

[amr67]

I think it would be reasonable to standardize the previous entered data from using different delimiters ',' or'&' or '/' or '-' into one single ','.

Then, we can apply the view you supplied.

If this is way is easier, then I will need to know how to replace the different delimiter into one standard ','

 

[SantaMufasa]

Yes, AMR, you can "correct" the non-standard characters in a single update statement:

set define off
UPDATE MTL_TRANS
   SET transaction_reference = translate (transaction_reference,'-/&',',,,')
 WHERE transaction_reference like '%-%'
    or transaction_reference like '%/%'
    or transaction_reference like '%&%'
/
commit
/

Also, be sure to get rid of invalid values such as Tarek's "002027050". Let us know how things progress for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[amr67]

For the entry like that of Tarek '002027050', this is a valid entry, but the user entered the region in its region number rather than in its code. The region represents a telephone number, so the user entered the phone number instead of its representing code.

Is there a way to accept such entries, or it would be better to concatenate 'PN' with the number to make it valid?

By the way, I found also, data like this:

Tarek1 00202881,44440003,99933003

 

[SantaMufasa]

Yes, AMR, you can either:

1) pre-pend 'PN' to such entries to cause the code to work, or
2) modify the code to deal with such cases.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[amr67]

I found that I have too many of the following:

SUBINVENTORY_NAME TRANSACTION_REFERENCE
----------------- -----------------------------------------

Dave PARIS1-11,PARIS2-102,30,CANNES2-40,C22

This was because PARIS was actually divided into 2 regions.

Could this be considered during the parsing?

I have adjusted the rest of the data, but this issue remains.

 

[amr67]

Also, is it possible to BYPASS records with transaction_reference that do not start with a character?