http://qs1969.pair.com?node_id=196650


in reply to Using hash elements to create a string

I believe that I have the SQL statment constructor that you were looking for. There are a few things to note about the code. The first is of course that you need at least two items in the hash (I think you knew that). The other is that I had to sort the hash keys to ensure the ordering. I don't know if there is a better way to do that or if it actually matters for your code. I also converted your string arguments into arrays. I figured that if you were storing real values instead of strings, then this would be better. I hope this is what you were looking for.

%hash = ( "view0" => [qw/buffersize size value/],
      "view1" => [qw/buffersize size value/],
      "view2" => [qw/buffersize size value/],
      "view3" => [qw/buffersize size value/]);

my $lastview;
my $output;
foreach $key (sort(keys %hash)){
    # Do this only the first time.
    if(not defined($lastview)){
    $lastview = $key;
    $output = "$key";
    next;
    }

    $output .= " full outer join $key on $lastview.$hash{$lastview}[1]
+ = $key.$hash{$key}[1] and $lastview.$hash{$lastview}[0] = $key.$hash
+{$lastview}[0]";
    $lastview = $key;
}

print $output, "\n";

[download]

Replies are listed 'Best First'.
Re: Re: Using hash elements to create a string
by johnirl (Monk) on Sep 10, 2002 at 13:17 UTC
    Brilliant answer RollyGuy and it works pefectly, almost.
    I wish I was proficiant enough in Perl to correct it myself but alas, not yet.
    The problem is this like I said in between the code snippets in each statement the last item fom the arrays are missing. However in the code above it is presumed that it is only the first two items that are selected each time, when in fact only the last needs to be omitted.

    
input:
%hash = ( "view0" => "buffersize, size, value, speed",
          "view1" => "buffersize, size, value, speed",
          "view2" => "buffersize, size, value, speed"
          "view3" => "buffersize, size, value, speed")

output:
view0 full outer join view1 on view0.size = view1.size and view0.buffe
+rsize = view1.buffersize and view0.value = view1.value full outer joi
+n view2 on view1.size = view2.size and view1.buffersize = view2.buffe
+rsize and view1.value = view2.value full outer join view3 on view2.si
+ze = view3.size and view2.buffersize = view3.buffersize and view2.val
+ue = view3.value

    [download]

    j o h n i r l .

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

[reply]
[d/l]
      I see where I missed part of your specification. Here is try number 2. Now, the thing to note here is that my output is slightly different than the one you posted above. The "whatever = something" pairs are moved around a little bit. If I remember my SQL syntax, I don't believe it should matter too much. Let me know if it does, and I can help you fix it. Anyhow, here is the new updated code:

      %hash = ( "view0" => [qw/buffersize size value speed/],
      "view1" => [qw/buffersize size value speed/],
      "view2" => [qw/buffersize size value speed/],
      "view3" => [qw/buffersize size value speed/]);

my $lastview;
my $output;
foreach $key (sort(keys %hash)){
    # Do this only the first time.
    if(not defined($lastview)){
    $lastview = $key;
    $output = "$key";
    next;
    }
    
    $output .= " full outer join $key on ";
    my $i;
    $array_elements_used = scalar(@{ $hash{$key} })-1;
    #loop through all of the elements except for the last
    for($i=0;$i<$array_elements_used;$i++){
    $output .= "$lastview.$hash{$lastview}[$i] = $key.$hash{$key}[$i]"
+;
    # only add an "and" if this is not the last element used.
    $output .= " and " if ($i != ($array_elements_used-1));
    }
    $lastview = $key;
}

print $output, "\n";

=============
output:
view0 full outer join view1 on view0.buffersize = view1.buffersize and
+ view0.size = view1.size and view0.value = view1.value full outer joi
+n view2 on view1.buffersize = view2.buffersize and view1.size = view2
+.size and view1.value = view2.value full outer join view3 on view2.bu
+ffersize = view3.buffersize and view2.size = view3.size and view2.val
+ue = view3.value

      [download]
[reply]
[d/l]
        Hey RollyGuy
        thanks again for all your help. Your code works perfectly and I nearly have it integrated into ma program. But could you (or anybody else) tell me whats wrong with the code below.?
        Thanks
        
#!/usr/bin/perl

use strict;

my @views = ( 'view0', 'view1');
my $num_of_views = scalar(@views);
my %hash;
my @global_include = ("size, buffersize, value", "size, buffersize, va
+lue");
my $lastview;
my $output;

for (my $j = 0; $j < $num_of_views; $j++){
   my @array = split (", ",$global_include[$j]);
   pop @array;
   $hash{$j} = \@array;
}

#for (my $j = 0; $j < $num_of_views; $j++){
#   my $ref = $hash{$j};
#   print "HASH CONTAINS FOR $j: ";
#   foreach (@$ref){
#        print "$_\n";
#   }
#}

foreach my $key (sort(keys %hash)){
    # Do this only the first time.
    if(not defined($lastview)){
    $lastview = $key;
    $output = "$key";
    next;
    }

    $output .= " full outer join $key on ";
    my $i;
    my $array_elements_used = scalar(@{ $hash{$key} })-1;
    #loop through all of the elements except for the last
    for($i=0;$i<$array_elements_used;$i++){
    $output .= "$lastview.$hash{$lastview}[$i] = $key.$hash{$key}[$i]"
+;
    # only add an "and" if this is not the last element used.
    $output .= " and " if ($i != ($array_elements_used-1));
    }
    $lastview = $key;
}
print $output, "\n";

        [download]

        j o h n i r l .

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

[reply]
[d/l]

In Section Seekers of Perl Wisdom