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

[interchange-users@interchange.redhat.com]

On Wed, Apr 17, 2002 at 09:53:36PM -0700, Kyle Cook wrote:
> 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]


make available_breaks an array ref

$available_breaks=['q2','q3',...]

$sql=join ' ',
'SELECT',
(join ',',@{$available_breaks}),
'FROM asdfasdf'

and below

for(@{$available_breaks}) {
....

I'm not familiar with Tag->query, we've been doing this so long
we have our own libraries that predate it.  :-)  I'd be there
is a way to get back the results as array or arrayref; that's the
way it used to be in my youth.




> >
> >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
> 
> 
> _______________________________________________
> interchange-users mailing list
> interchange-users@interchange.redhat.com
> http://interchange.redhat.com/mailman/listinfo/interchange-users

-- 

Christopher F. Miller, Publisher                               cfm@maine.com
MaineStreet Communications, Inc           208 Portland Road, Gray, ME  04039
1.207.657.5078                                         http://www.maine.com/
Content/site management, online commerce, internet integration, Debian linux