johnirl has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks
I have the results from a SQL statement. I wish to order it in an unusual way . The main key I want to use to order them is a memory key. I want to order them in loop starting with the smallest value "0" and moving up to the largest "256". Then I want to repeat the process for the rest of the values ie 0, 2, 4, 8, 16, 32, 64, 128, 256, 0, 2, 4, 8, .....

The values are ordered correctly (as above) in my database but when I retrieve them using a rather complicted SQL statement they become sorted ie 2, 2, 2, 2, 2, 2, 2, 2, 2, 4, 4, 4, 4, 4, 4, 4, 4.......

Another problem I have is that all the Zero values are stored as null in the database. After my SQL statement is run it sends all the nulls to the end of the file. Unfortunatly not all the nulls are Zeros. Some are in fact nulls.

I'm comparing two tables using an outer join so some of the nulls would be non comparable results ie a result from table one that can't be matched to the second table.

memory    .    memory    .
 .        .      .       .
 .        .      .       .
 .        .      .       .
256     78879   256     8706    [Full result ordered
256     80700   256     7898     incorrectly (most common)]
256     53454   256     2345
--      76896   --      7860    [Result for 2
--      76967   --      8087     zero values]
--      34456   --      2344
--       --     2       7697    [No comparable values]
--       --     2       7696
--       --     2       7687
--       --     2       7869
--       --     4       6797
2       6798    --       --
2       7008    --       --
 .        .      .       .
 .        .      .       .
 .        .      .       .

[download]

So what I am hoping to do is
1) Order the results in the desired way.
2) Seperate nulls from zeros.

Any ideas are appreciated. I realise that this is a complex issue but even bit answers and suggestions are helpful.

Thanks Guys.

j o h n i r l .

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

Replies are listed 'Best First'.
Considerations on ordering
by cebrown (Pilgrim) on Aug 13, 2002 at 12:49 UTC
    You mention that the results are ordered correctly in your database but that the order is altered by your SELECT statement. It might be helpful to know that one of the features (no, really, it's a feature) of most relational databases is that a result set from a query will be in an indeterminate order unless you ask for it to be ordered. (Some RDBMS's might have a feature to get around this; since I don't know what you're using and spend most of my time in SAS and DB2, so I'm not sure.)

    Ideally, you will have a column in the table on which you can order, but if you don't, do not despair -- most RDBMS's will provide the table in its "natural" (physically stored) order if you do "select * from table". You might then need to add another counter column (or use an RDBMS-specific function to get to it) and either run SQL against that or just do your processing in straight Perl.

    Hope this helps.

[reply]
      Thanks for that and I am actually using DB2 also.
      I realise that when you do a simple select the order is maintained however I'm using a full outer join. So this orders the table fo me in the result. Even when I add an unsorted or order by clause it does not help. I really think that perl is my only option however if you can think of something within DB2 which would save me having top program it I would be delighted to hear about it.

      j o h n i r l .

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

[reply]
        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.

[reply]
Re: Sorting
by Anonymous Monk on Aug 13, 2002 at 13:00 UTC 
    # This is pseudo code
for each $record (@records)
{
  push $record into the right $bin;
  $records++;
}
$key = 1;
while ($records--)
{
  ++$key while(empty $bin{$key});
  pop $record from $bin{$key} and print it;
}

    [download]

    That's at least the way I understood what you want to do. If there's actually a sorting criteria for the output (other than "0 2 4 8 ...", this would have to be different. Unless I missed something, the problem is there's no way to tell that "1 0" comes before "2 0" or after "3 256", that is, your description is too loose.

[reply]
[d/l]

Back to Seekers of Perl Wisdom