http://qs321.pair.com?node_id=475588

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

Hi im new to this website and fairly new to perl. here is my code first and output. 
 my @label = ( "F%", "G%" );

my $str = $dbh->prepare( "SELECT ID, NUM
                          FROM INDEX
                          WHERE ID like ?
                        );

for ( @label ) {
     $str->bind_param( 1, SQL_VARCHAR );
     $str->execute();

     my ( $label, $num );
     $str->bind_column( undef, \$label, \$num );

     while( $str->fetch() ) {
           print "$label $num\n";
     }

[download]
here is my output: F01212 F01213 G01212 My problem is i only want the F labels if they dont exist in the G labels so in this case i would want my ouput to be F01213. Sorry if this is long winded. thanks for any help.

Replies are listed 'Best First'.
Re: Sort label
by gryphon (Abbot) on Jul 17, 2005 at 21:59 UTC

    Greetings celliott,

    I'm not sure I entirely follow what you're wanting to do, but if I'm understanding you correctly, this might help:

    my $data = $dbh->selectall_arrayref(
    'SELECT ID, NUM FROM INDEX WHERE ID LIKE F% OR ID LIKE G%',
    { Columns => {} }
);

my %index;
foreach (@{ $data }) {
    if ($_->{'ID'} eq 'G') {
        $index{$_->{'NUM'}} = -1;
    } else {
        $index{$_->{'NUM'}} = 1 if (not exists $index{$_->{'NUM'}});
    }
}

my @index;
foreach (keys %index) {
    push @index, 'F' . $_ if ($index{$_} == 1);
}

print join("\n", @index), "\n";

    [download]

    Instead of dealing with all the bind_param and bind_column stuff, I just selectall_arrayref into a reference, then foreach through that to populate %index. It seems to work for me, but I don't like it. It requires looping twice, which just feels like it would be slow. Plus, there's got to be a way to do this with a couple maps, but apparently I'm not smart enough to figure that out today.

    gryphon
    Whitepages.com Development Manager (DSMS)
    code('Perl') || die;

[reply]
[d/l]
Re: Sort label
by jdporter (Paladin) on Jul 18, 2005 at 01:30 UTC 
    my %G;

{
    local $_ = 'G%';
    $str->bind_param( 1, SQL_VARCHAR );
    $str->execute();

    my( $label, $num );
    $str->bind_column( undef, \$label, \$num );

    while ( $str->fetch() )
    {
        my $lbl = $label;
        $lbl =~ s/^G/F/;
        $G{$lbl}++;
    }
}

{
    local $_ = 'F%';
    $str->bind_param( 1, SQL_VARCHAR );
    $str->execute();

    my( $label, $num );
    $str->bind_column( undef, \$label, \$num );

    while ( $str->fetch() )
    {
        print "$label $num\n"
        unless exists $G{$label};
    }
}

    [download]
[reply]
[d/l]
Re: Sort label
by graff (Chancellor) on Jul 18, 2005 at 03:00 UTC
    First a couple nits about your post (just to show how picky monks can be): you're missing a close-quote on the sql statement, as well as the close curly on the for loop. Also, the print statement in the for loop shows two values per line, separated by space, but (viewing the source html for your page, just to be sure) the output you show has only one value per line -- BTW, it's okay to put <code> tags around data as well as around code -- and it really helps, too. (In this case, the glitches are not a big deal, because the code is both brief and fairly clear in general, but sometimes monks will complain when the posted code is alleged to produce the posted output, but it doesn't even compile.)

    Then there's a slight confusion about the statement of the problem: you say:

    i only want the F labels if they don't exist in the G labels so in this case i would want my output to be F01213.
    Does that actually mean that you don't want the output to show the G labels at all (meaning that the G labels are only being selected so that you know which F labels to discard)?

    Something like gryphon's idea, using a hash, is probably the first thing I would try. But maybe I'd query for the "G" records first, to populate the hash using just the numeric part of those IDs, then query for the "F" records, and print the ones whose ID numbers do not exist in the hash. (If you want to print the G labels in combination with the distinct F labels, that's still easy enough -- just add the distinct F entries to the hash as you go, and print the whole hash afterwards.)

[reply]
Re: Sort label
by Roy Johnson (Monsignor) on Jul 18, 2005 at 14:00 UTC
    I think you just want to modify your query to do the elimination: 
    SELECT id, num
FROM   index i1
WHERE  id LIKE ?
  AND NOT EXISTS (
    SELECT 1 FROM index
    WHERE  id = 'G'||Substr(i1.id, 2)
  )

    [download]
    Index is a poor choice for a table name, by the way.
    Caution: Contents may have been coded under pressure.
[reply]
[d/l]

Back to Seekers of Perl Wisdom