Application Sorting Dilemna - Should I Use Oracle For Sorting 2

[TMurdach]

I've been looking for bits and pieces of code to help solve this problem - as I will continue to do but have had success getting and providing answers on this forum.

I have a home grown application which has very poor/limited functionality outside of what it was made to do - but it can execute a stored procedure. I have a starting string I would like to pass to a stored procedure then have a sorted version passed back to my application.

The starting value is a long string with each item separated by a pipe.
Within each item is a DEPT value which is treated as a string in my application but needs to be sorted as a number. The next piece of the item is the ITEMCODE which can be a 5 to 10 digit integer. The final piece of the item is the description (DESC). It can potentially be 64000 alpha characters but averages about 200 characters.

Starting unsorted value passed to Oracle via SP:
2-6055159:STORAGE_CUBE_BLUE|001-6055154:STORAGE_BOXES_RED|0-50020:FLATWARE_10PC|01-6055159:STORAGE_BOXES_BLUE

Desired sorted result passed back to application:
0-50020:FLATWARE_10PC|001-6055154:STORAGE_BOXES_RED|01-6055159:STORAGE_BOXES_BLUE|2-6055159:STORAGE_CUBE_BLUE

Perhaps importing this into a temp Oracle table would make it easier since my application can run stored procs.

Before the sort:
[pre]DEPT ITEMCODE ITEM
2 6055159 STORAGE_CUBE_BLUE
001 6055154 STORAGE_BOXES_RED
0 50020 FLATWARE_10PC
01 6055159 STORAGE_BOXES_BLUE[/pre]
Sort order DEPT then ITEMCODE

After the sort:
[pre]DEPT ITEMCODE ITEM
0 50020 FLATWARE_10PC
001 6055154 STORAGE_BOXES_RED
01 6055159 STORAGE_BOXES_BLUE
2 6055159 STORAGE_CUBE_BLUE[/pre]

Then rebuilt the desired output string as a single string with the delimeters back in place.

 

[TMurdach]

Correction: Max value for DESC is 4000 characters, not 64000

 

[taupirho]

The important bit of info - from a sorting point seems to be the dept no, so I guess you can just split the line into separate records on the field separator. Sort the resulting records then re-join them.

You dont say which system you are on but the above would be easy if you are on UNIX using
SED and/or AWK commands. A bit like in psuedo-code terms

$pipe echo
"2-6055159:STORAGE_CUBE_BLUE|001-6055154:STORAGE_BOXES_RED|0-50020:FLATWARE_10PC|
01-6055159:STORAGE_BOXES_BLUE" | awk -F"|" "{ for(i=1;i<=NF;i++){printf("""%s\n""",$i)} }" |
sort sys$pipe sys$output | awk "NR>0 { a=a $0 } END { print a }"




In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

TMurdach,

Your incoming data are so blatantly disobedient of 1st Normal Form (No repeating groups in a row of data), that I don't understand why you don't bite the bullet and fix your data (and application) once and for all so you never have to deal with this issue ever again.

I can't imagine why, if this is a home-grown application, anyone approved a design whereby multiple rows of data are strung together on a single row, separated by a pipe. Your application must parse out the logical data (from between the pipes) every time it processes data...That's just nuts !

Presently, it's rather like going through a garbage can trying to scavange for food that you can turn into a fine meal. Why not just start out with high-quality data in the first place?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[TMurdach]

Thank you both for the feedback!

taupirho - the core application is an older GUI interface similar to Eclipse but far less powerful. It is capable or running an Oracle stored procedure so I'm disecting your suggestion to perhahps make it fit my needs.

SantaMufasa - thank you for the suggestion. Unfortunately in the real world you have to support the hand (or data in this case) you are dealt.

 

[SantaMufasa]

Unfortunately in the real world you have to support the hand (or data in this case) you are dealt.

The implication from your posting is that I'm sitting in an "Ivory Tower" somewhere surveying my perfect data domains. Actually, I've been working in the "Real World" since 1973 (41 years), cleaning up data (and code) messes, similar to the one you inherited.

My earlier comments were, in no way, an indictment of your professionalism or skills, so please don't take my observations personally. My observation is that by your organization allowing good business data to reside in a really bad data structure (i.e., disobeying badly the very most basic rule of Relational Database Theory, First Normal Form), it makes life programmatically miserable for all who must touch the data, over and over again. How many hours have you, your colleagues, and we (here on Tek-Tips) had to spend making a silk purse out of the sow's ear that is your data structure? Has anyone in your IT organization considered how much money (in programming salaries) your organization has wasted because your data is not relational? I'll bet your organization has spent way more money on the on-going sow's ear-to-silk purse conversions than it would cost to perform a one-time data-structure correction that would actually reduce the code in your application to parse out the data every time someone writes code to touch the data, right?

I know that you may not be in a position to make that kind of a maintenance decision, but if enough people in your organization agree that The Emporer is wearing no clothes, perhaps the Powers-that-Be will allocate enough maintenance funds to fix the problem, once and for all.

You could even fix the problem piece-meal (and not affect your application) by storing your data in proper relational form, and writing a simple routine to concatenate your properly stored, relational data, back into strung-together crap for your crap-eating application to consume. Then, later, you, can piece-by-piece, fix your admittedly home-grown application code to behave in a proper relational fashion.

PLEASE BEAR IN MIND THAT MY HARSH CRITICISM OF YOUR POORLY FORMATTED DATA IS NO CRITICISM OF YOU !!! (since you simply inherited the mess and are not authorized to fix it) My criticism is of the horse-and-buggy data structuring that prevents your application from running on a modern, high-speed freeway.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[fredericofonseca]

Thats from me.

And to add to it. Personally I think it is part of the obligation of any professional to investigate and suggest ways of changing/improving a bad application. and keep insisting on it when told no.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[TMurdach]

I've used this forum for many years, asked a few questions, gave a few answers. I've seen the rants people go on when someone doesn't search for an answer that is already out there... I get that.

But hey, I'm just looking for help with an Oracle data parsing scenario. The data I have to work with is this way and will be for years. That won't change so I simply need to make the most of it. If you have a suggestion to how to piece together the Oracle script, great. If you want to badger my data, that's great too. It's a free country.

 

[taupirho]

Try this SQL only method, the only thing you have to sort out is an extra '|' at the end of the string

select
rtrim (xmlagg (xmlelement (e, x || '|')).extract ('//text()'), ',') final_str
from
(
with str as
(
select '2-6055159:STORAGE_CUBE_BLUE|001-6055154:STORAGE_BOXES_RED|0-50020:FLATWARE_10PC|016055159:STORAGE_BOXES_BLUE' x
from dual
)
select substr(
X,
instr('|'||X,'|',1,seq),
instr('|'||X||'|','|',1,seq+1) - instr('|'||X,'|',1,seq)-1) X
from str,(select level seq from dual connect by level <= 100) seqgen
where instr('|'||X,'|',1,seq) > 0
order by 1
)


In order to understand recursion, you must first understand recursion.

 

[taupirho]

Actually change this bit

('//text()'), ',') final_str

to

('//text()'), '|') final_str

and that last '|' should go


In order to understand recursion, you must first understand recursion.

 

[Andrzejek]

Another way I would try it: write the data into a temporary table and sort it from there. Then get rid of the temp table after you are done.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Beilstwh]

To Andrzejek,
Never create an drop an oracle table when it can be done in a simple statement. That should be a last resort, not the first.

Bill
Lead Application Developer
New York State, USA

 

[TMurdach]

taupirho. Thank you!

Here is the result of the query:

01-6055159:STORAGE_BOXES_BLUE|0-50020:FLATWARE_10PC|001-6055154:STORAGE_BOXES_RED|2-6055159:STORAGE_CUBE_BLUE

Very close!

I see it is placing the 01-6055159 before 0-50020.
Let me make an update to the desired output that might help simplify the sorting:
The output response does not have to maintain the string formatted digit values before the "-". An integer value would be adequate (actually preferred) over returning the string formatted number "01" "001" etc.
So with that in mind, the return result should be:
0-50020:FLATWARE_10PC|1-6055159:STORAGE_BOXES_BLUE|1-6055154:STORAGE_BOXES_RED|2-6055159:STORAGE_CUBE_BLUE

 

[TMurdach]

CORRECTION!

So with that in mind, the return result should be:
0-50020:FLATWARE_10PC|1-6055154:STORAGE_BOXES_RED|1-6055159:STORAGE_BOXES_BLUE|2-6055159:STORAGE_CUBE_BLUE

 

[TMurdach]

Using taupirho's query, I modified it to return the originally desired results.

How would this need to be formatted to run as an Oracle stored procedure with the string of data to sort being passed in as a variable?

select
rtrim (xmlagg (xmlelement (e, x || '|')).extract ('//text()'), '|') FINAL_STR

from
(

  with str as
  (
    select '2-6055159:STORAGE_CUBE_BLUE|001-6055154:STORAGE_BOXES_RED|0-50020:FLATWARE_10PC|01-6055159:STORAGE_BOXES_BLUE' x
    from dual
  )

  select 
  substr(X,instr('|'||X,'|',1,seq),instr('|'||X||'|','|',1,seq+1) - instr('|'||X,'|',1,seq)-1) X,
  to_number(substr(substr(X,instr('|'||X,'|',1,seq),instr('|'||X||'|','|',1,seq+1) - instr('|'||X,'|',1,seq)-1), 1 ,
  INSTR(substr(X,instr('|'||X,'|',1,seq),instr('|'||X||'|','|',1,seq+1) - instr('|'||X,'|',1,seq)-1), '-', 1, 1)-1)) DEPT,
  to_number(SUBSTR(substr(X,instr('|'||X,'|',1,seq),instr('|'||X||'|','|',1,seq+1) - instr('|'||X,'|',1,seq)-1), 
	INSTR(substr(X,instr('|'||X,'|',1,seq),instr('|'||X||'|','|',1,seq+1) - instr('|'||X,'|',1,seq)-1),'-', 1, 1)+1,
	INSTR(substr(X,instr('|'||X,'|',1,seq),instr('|'||X||'|','|',1,seq+1) - instr('|'||X,'|',1,seq)-1),':',1,1)
	-INSTR(substr(X,instr('|'||X,'|',1,seq),instr('|'||X||'|','|',1,seq+1) - instr('|'||X,'|',1,seq)-1),'-',1,1)-1)) ITEMCODE

  from str,(select level seq from dual connect by level <= 100) seqgen
  where instr('|'||X,'|',1,seq) > 0
  order by DEPT, ITEMCODE

)