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?

 

[LKBrwnDBA]

Yes, if you code a PL/SQL procedure to do it.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[amr67]

Could this procedure by used inside a Select statement?

 

[LKBrwnDBA]

NO, the select statement must be inside the procedure.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[amr67]

How can I make a view to retrive the data I need in such way?

 

[SantaMufasa]

AMR,

To clarify, if your data in REGION appears as
"PARIS11,30,CANNE40,C22,TOULOUSE1"

...how do you want the results to appear?
...do all of your REGION entries contain 5 sub-fields, as above?
...What does this mean: "Is there any possibility to retrieve each region in one single record?"

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]

No, the number of sub-fields is not fixed and may change from one entry to the other.

Note that some sub-fields data may be repeated in other enties. This is like the regions serviced by each maintenance employee.

I need the end result to be like this:

record 1 -> PARIS11
record 2 -> PARIS30
record 3 -> CANNE40
record 4 -> CANNEC22
record 5 -> TOULOUSE1

and so on.

 

[Turkbear]

Hi,
With that data structure ( who designed it that way?) you will need a Pl/Sql procedure and much parsing to get what you want..I am not even sure there is enough information in the field to determine how it should be parsed..

Any hope of changing the data structure to avoid this problem?







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[amr67]

This is what I need to do in the future entry. However, I cannot adjust the previous entry done. I need to create a view to be able to analyze the data based on the single entry record and not many subfields.

I have no experience in using pl/sql inside select statements to create the required view. That's why I posted this message to find experienced support.

 

[SantaMufasa]

AMR,

If you can post the rules for parsing out the region information from the incoming rows, then perhaps we can create a VIEW 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]

I can put the rule as follows:

1- sub-fields separator is ',' (comma).
2- if sub-field data begins with character and total length greater than 4, this is a complete information.
3- if sub-field data begins with character and total length equal or less than 4, this must be concatenated with the characters from the previous sub-field. Numbers from the previous sub-field will be replaced with numbers from current sub-field.
4- if sub-field data begins with number, this must be concatenated with the characters from the previous sub-field. Numbers from the previous sub-field will be replaced with numbers from current sub-field.

Note, in the examples previously posted, we had:
Rule 2:- subfield is PARIS11 -> record is PARIS11
Rule 3:- subfield is C22 -> record is : CANNEC22
Rule 4:- subfield is 30 -> record is : PARIS30

I hope I were able to demonstrate clearly what I want.

 

[Beilstwh]

The logic can easly be put into a stored function that would return the correct value in the select, something like

select region_parse(region) region
from my_table;

If you need help writing the function, let up know. However, if I was you, I would write a procedure to correct the data in the region field so that it is formatted correctly. Or even better, that the information is stored in seperate columns. Frankly, using a single column to store multiple pieces of information is normally a very bad idea.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

AMR,

Bill's post here reminded me that I have dropped the ball on my commitment to help you out with this. Is this still an issue for which you need help, or have you resolved it in some fashion and moved on?

Let us know. (With the weekend here, I can concentrate on some "recreational programming" to TCB 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]

The problem of correcting old data is that the tables belong to the Oracle Application (the ERP package). I am not sure this could be done because each line in the transaction table is unique and this correction will need specific identifier for each row which I do not know from where the application is providing.

So, I have to build the query for the view regardless of the users data entry.

For the procedure, I am not expert with such programming. I only can do simple select statements.
I would be very graceful if you can support for this case.

 

[SantaMufasa]

AMR,

I'm sure that the sooner you get a resolution/work-around to this issue, the better for you. But what is the deadline for having such a resolution? I am slammed right now with a customer request that came through since my last post and it may be Tuesday/Wednesday before I can create a proof-of-concept/example of a SELECT-based solution for you.

As far as timing, I'm guessing that you are GMT +2/+3. Is that about right?

Let me know,

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, I am GMT+2.
I may still have a week to finish this task.

Please let know when you can provide a solution so that I may reschedule my plans.

 

[SantaMufasa]

AMR,

I'm finally able to work on this project now. I do have a couple of more questions for you, however:

Do you want a VIEW that shows:

A) All of the DISTINCT regions from all of your Maintenance employees, or

B) All of the regions from each of the Maintenance employees, along with the source employee's employee ID.

I know that your local time is midnight Sunday morning right now, but I'm hoping that you are still awake, working on your machine.

Thanks,


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]

More questions, AMR:

1) Can you please post a "DESCRIBE <table>" of the subject table that contains the strung-together REGION information?

2) What is the high number of rows might reside in that table?

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]

AMR,

Is it true that:

1) the REGION field must be NOT NULL?

2) the first subfield in REGION is always a complete region, i.e., it will NEVER need concatenation with a previous region since there is no previous region?

Thanks,

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]

Sorry for my late reply.

1- All of the regions from each of the Maintenance employees, along with the source employee's employee ID.

2- It is true for both that Region must not be null and the first subfield is always a complete region. For null regions, this does not belong to the required views and are related to other transaction types not required.

3- For the describe of the subject table, you mean the input table or the output table?