1 2 Previous Next 19 Replies Latest reply: Dec 28, 2012 9:10 AM by AlbertoFaenza Go to original post RSS
      • 15. Re: convert strings into row
        Solomon Yakobson
        You can also try model solution:
        select  c "char",
                s sum_position
          from  your_table
          model
            return updated rows
            partition by (rowid)
            dimension by('A' c)
            measures(your_column,0 s)
            rules
              iterate(20) until(iteration_number + 1 = nvl(length(your_column['A']),1))
              (
               s[substr(your_column['A'],iteration_number + 1,1)] = nvl(s[substr(your_column['A'],iteration_number + 1,1)],0) + iteration_number + 1
              )
        /
        SY.
        • 16. Re: convert strings into row
          VI
          Hi ,

          I have tried the query, still it's taking more than half hour to execute. I have 60,000 records in my table

          Edited by: Vi on Dec 26, 2012 11:05 PM
          • 17. Re: convert strings into row
            VI
            Is there any other way to achieve this?? the query performance is very low :-(
            • 18. Re: convert strings into row
              BluShadow
              Vi wrote:
              Is there any other way to achieve this?? the query performance is very low :-(
              No doubt it will be slow. You're taking individual characters from strings, summing up their positions within the grouping of that string to generate new rows of data, and you're doing that for 60,000 rows of strings. That's undoubtably generating a lot of new rows (probably in the millions) and taking a lot of cpu time to to process all those characters and do the summing up.

              What is the real reason you're doing this? I can't think of any valid reason for wanting to sum up the position of characters in a string and generating those as new rows of data. What valuable information are you getting from the sum of the positions?

              Sounds like you're trying to solve some other issue, but don't want to share what that is.
              Having data stored in such a way that you need to split it down to the characters to perform some sums on the positions... sounds like bad design.
              Half an hour to process all 60,000+ rows of data.... think yourself lucky it finished in that time.
              • 19. Re: convert strings into row
                AlbertoFaenza
                Vi wrote:
                Hi ,

                I have tried the query, still it's taking more than half hour to execute. I have 60,000 records in my table

                Edited by: Vi on Dec 26, 2012 11:05 PM
                You have posted just one string and what you want to get.

                Can you post 2 ore more rows from your input table and corresponding output for this sample data?

                Regards.
                Al
                1 2 Previous Next