[ic] Order of columns for hashref returned from query?

[Kyle Cook]

At 06:15 PM 4/17/02, you wrote:
>I'm working on a piece of code that will be called by CommonAdjust to
>only return the columns in the pricing table which are populated with
>data for the given item.  This will force the quantity discount
>mechanism to discount quantity orders which are equal to or exceed a
>previous price break.  I had this code working before, but it used a
>$Tag->data call for each column which was inefficient to do.  Below is
>the code as it stands right now.  It selects the data then outputs the
>columns.  It will be modified to only output the columns that have a
>value once I find a solution to the problem.
>
>[perl tables=pricing]
>                         my $code = $Scratch->{tmp_code};
>                         my $available_breaks =
>"q2,q3,q4,q5,q6,q7,q8,q9,q10,q12,q15,q25,q50,q100";
>                         my $sql = "SELECT " . $available_breaks . " FROM
>pricing WHERE sku ='" . $code . "';";
>                         my $sql_results = $Tag->query( {     sql =>
>$sql, hashref => 'price_results' } );
>                         my $hash = $sql_results->[0];
>                         my $out = "";
>                         my $key, $value;
>                         while ( ($key, $value) = each %$hash ) {
>                                 $out .= " $key => $value ";
>                         }
>                         #foreach $key (sort keys %$hash) {
>                         #       $out .= " $key => $$hash{$key} ";
>                         #}
>                         return $out;
>                 [/perl]
>
>The problem with this code is the ordering of the hash does not match
>the ordering of the column list in the select statement.  It is output
>in this order:
>
>q15,q9,q25,q50,q2,q3,q10,q4,q5,q12,q6,q7,q100,q8,

Ron,

I'm no expert in quantity breaks (never used them), but as far
as generating the string that looks like what you want,
letting the sql server handle it would seem to be the easiest....


my $sql = "SELECT CONCAT(
                 'q2 => ', q2,
                 ' q3 => ', q3,
                 ' q4 => ', q4,
                 ' q5 => ', q5,
                 ' q6 => ', q6,
                 ' q7 => ', q7,
                 ' q8 => ', q8,
                 ' q9 => ', q9,
                 ' q10 => ', q10,
                 ' q12 => ', q12,
                 ' q15 => ', q15,
                 ' q25 => ', q25,
                 ' q50 => ', q50,
                 ' q100 => ', q100)
                 FROM pricing
                 WHERE sku ='" . $code . "';";

Enjoy,

Kyle Cook