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

Ron Phipps interchange-users@interchange.redhat.com
Wed Apr 17 21:21:01 2002


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,

This ultimately will cause problems for CommonAdjust as it needs to be
in order from lowest to highest quantity.  

I tried sorting on the keys in the hash but that fails because q15 is
returned before q2, q3, q4, etc... This is the order they are returned
in:

q10,q100,q12,q15,q2,q25,q3,q4,q5,q50,q6,q7,q8,q9,

I thought about reverse ordering by the value, but that will restrict
the data that can be input into each column.  For example if you had
$10.00 in q2 you couldn't have $11.00 in q4 because q4 would be returned
before q3.

Is there a way to sort on the keys so that they appear in the following
order?

q2,q3,q4,q5,q6,q7,q8,q9,q10,q12,q15,q25,q50,q100?

Thanks!
-Ron