Re: What is the best PLSQL solution to this problem?

Thanks for the suggestions.

Here's more details of what the task is that I need to do. I get a large data set from a 3rd party app as a large varchar. The data set is divided into groups, each group into records and finally each record into fields delimited by some special characters. (I have no problems parsing this in PLSQL).

One record of the incoming group contains 1 to 40 fields. The first 1..X fields are copy verbatim from the INPUT DATA SET to the OUTPUT DATA SET but the X..Z fields of the INPUT are copied to the OUTPUT by a mapping controlled by data in the a mapping table (the one I mentioned before). So, I look in field 12 of the INPUT and find that the value = 'key2'. I goto the mapping table using KEYWORD 'key2' to retrieve array '5,N,23,24,26,N,N,N'. This show me how to the data transformation to OUTPUT which follows

INPUT(5)    to OUTPUT(X)
' '         to OUTPUT(X+1)     # the 'N' means put in a blank space  
INPUT(23)   to OUTPUT(X+2)
 

    and so forth until the
    OUTPUT DATA SET is complete
    then it is inserted into a
    table.

INPUT DATA SET                OUTPUT DATA SET
1 -+                      1
.  |                      .
.  |                      .


.  |    =============>    .

.  |                      .
.  |                      .
X--+                      X     
12 ---+            +----> .
.  |  |            |      .
.  |  |            |      .
.  |  |            |      .
Z--+  |            |      Z
      |            |
      |            |
      |  KEYWORD   |  ARRAY (the values are separated by commas)
      |  -------   |  ----------------------------
      |  key1      |  12,N,N,4
      +> key2      +- 5,N,23,24,26,N,N,N
         key3         1,2,5,6,7,8

The records identified by the KEYWORD can come in any order so each time I get a record from a group I need to retreive the array to do the transformation. That is why it would be really usefull to have a hash of arrays like I would do in Perl or even C/C++. Maybe I'm missing something in the PLSQL but it would seem like I would need a PL/SQL table for each KEYWORD in the mapping table and then a bunch if statements like
if ( keyword is key1)

   use key2_plsqltable
elseif (keyword is key1 )

   use key2_plsqltable
  .
  .
Or I could just query the mapping table every time an then split the array.

Being that I do not want to create a physical table, too much collateral impact which I won't bother boring you with, I can't think of a very elegant way to do this other than brute force. BTW, Daniel, I'm lucky in this 3rd party app if they put indexes on their tables, :-(. I swear a lot of some companies would do a lot better if they had a DBA on their design teams. They seem to concentrate on the GUI, performance and data integrity are afterthoughts which by then its too late to do anything so they just ship it.

javadude
(and still missing it -- soon, real soon I will back) Received on Wed Jun 20 2001 - 16:15:15 CDT