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?

 

[amr67]

I think I have to make standardize all previous and future entries to be as follows:

Dave PARIS1-11,PARIS2-102,30,CANNE2-40,C22
AMR Toulouse-1,3,44,CANNE-6,D44,PARIS1-13

That is to separate between the prefix and the IDs with '-' whatever the prefix was. This means that PARIS1, PARIS2, CANNE2, TOULOUSE, CANNE are all prefixes and what comes after the '-' is the first ID inside the region.

The record should read:

Dave PARIS1-11
Dave PARIS2-102
Dave PARIS2-30
Dave CANNE2-40
Dave CANNE2-C22
Amr Toulouse-1
Amr Toulouse-3
Amr Toulouse-44
Amr Canne-6
Amr Canne-D44
Amr PARIS1-13

Thanks for your patience with me.

 

[SantaMufasa]

AMR,

Do you wish to make the logic changes necessary to adjust the REGION_PACK package, or do you need to rely upon my making the changes?

If you need on-going consulting assistance, AMR, perhaps you should contact me at Dasages, LLC (see my signature), to establish a business relationship. Dasages has many international customers.

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]

I wish to make the logic changes necessary to adjust the REGION_PACK package.

 

[amr67]

I have completed all necessary updates to make the region field compatible with your requirements.

I requeried the data, but I still get the error of ORA-01722: Invalid number.

Any suggestions to find the any incompatible records?

 

[amr67]

By the way, I must add the field of TRANSACTION_ID to each record.

This transaction_id is the only field that is unique and will be used for further relations in other queries.

 

[amr67]

I found the problem.

It is in the region_view view because of to_number(region_pack.get_emp_id) which should return a varchar2 and not number.

Now, I am requering and waiting for the end results.

 

[amr67]

Done.

Output results are complying with required.

Many thanks for your great support.

 

[Beilstwh]

You are hereby given the dreaded Purple Star for efforts above and beyond.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Very thoughtful of you, Bill...This one certainly did take higher level of effort than typical TT advisories, but there was simply no way (as you can imagine) to simply "explain" how to do this...we'd still be "explaining" into next month.

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]

 

[Welshbird]

Santa - I have to but and give you a wee purple one too.

Just amazing,

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

Aye, and sur'in ye be a fine chappie, as well, Fee. Thank ye.

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]