in reply to Re: Considerations on ordering
in thread Sorting

Ah! Well, I think if you're trying to do it in straight-up SQL with very little Perl, it'll be quite tricky. In order to get the order-preservation you need, I'm pretty sure you'd have to create a temporary table with the additional "counter" column I mentioned above (not sure how you would do this in DB2 but suspect there is a function to get that); also, with a temporary table you could update all NULL values to 0, sidestepping that problem as well. Since you're in DB2 you could automate the whole shebang with SPUFI. Or if you have QMF around you might be able to do everything within a FORM.

But we're straying pretty far from Perl now and I really do think it'll be easier to dump the table into Perl's control and do all of your stuff there. It is the Practical Perfect Extraction and Reporting Language after all :). SQL is all well and good when you're working with more than one table but if you're trying to do fancy stuff within a result set it's almost always easier to program it.

Replies are listed 'Best First'.
Re: Re: Re: Considerations on ordering
by johnirl (Monk) on Aug 13, 2002 at 13:35 UTC
    Yeah I've kinda come around to the realisation that I'm gonna have to program it. But any ideas how?

    j o h n i r l .

    Sum day soon I'Il lern how 2 spelI (nad tYpe)

[reply]
      I would start with the other reply in this thread and go from there. I agree with the other poster that the requirements don't seem complete -- it might be worth posting a sample of the data from the database and hand-hacking a result of what you'd want that turned into. Right now we can only see an example of it not working.
[reply]
        Table 1.

        memory     Value
  -        64456
  2        76574
  4        36577
  8        76577 
  16       74657  
  -        76576
  2        56756 
  4          --
  8        75676 
  16       98797
  -        23432
  2        43566

        [download]

        Table 2. 

        memory     Value
  -        64456
  2        76574
  4        36577
  8        76577 
  16         -- 
  32       74576 
  -        76576
  2        56756 
  4        76457
  8        75676 
  16       75766 
  32       32446
  -        23432
  2        34534

        [download]

        Result 

        memory   Value1   Value2

  2      54623    87686 
  2      34534    54675
  2      24564    56464
  4      56745    65646 
  4      34547    45644 
  4       --      34251  
  8      45654    56454
  8      64566    56563
  8      58468    56565
 16      77457      -  
 16      56864    81678
 16      98754    65354
 32        --     56763
 32        --     64566                
 32        --     67387
 -- [0]  56345    64243
 -- [0]  43587    65456
 -- [0]  65464    45654

        [download]

        Desired Result 

        memory   value1   value2

  0       56465    54655
  2       66455    64564
  4       48765    35345
  8       23451    66456 
 16       23526    68773
 32         -      46145
  0       87443    26452 
  2       25486    24764
  4         -      25676
  8       27798    23467 
 16       25667      - 
 32         -      66767
  0       55279    21467 
  2       78743    26874

        [download]

        Number of Value columns may vary. Highest memory values may vary.

        j o h n i r l .

        Sum day soon I'Il lern how 2 spelI (nad tYpe)

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom