Need help understanding something

[ssmith001]

I have a table that looks like this:

PLNRCODE	PARAM	     VALUE	PROCESSFLAG
M01	     DlrGrp	    AMOS	      N
M01	     DlrGrp	    BALTIMORKW	N
M01	     DlrGrp	    BAY AREA	  N
M01	     DlrGrp	    CAROLINAKW	N
M01	     DlrGrp	    C-B KW	    N
M01	     Abc	       A	         N
M01	     Abc	       B	         N
M01	     Abc	       C	         N
M01	     Abc	       E	         N
M01	     StkClass	  010	       N
M01	     StkClass	  010A	      N
M01	     TargetAmount  15000         N

and using this code:

### Prepare a SQL statement for execution  
my $sql = qq{ select param, value from pac.promo_params where plnrcode = ? and processflag = 'N' };

my $sth = $dbh->prepare( $sql );

### Execute the statement in the database
$sth->execute($planner);

my $hash_ref;

### Fetch rows into a hash reference with lowercase field names
while ($hash_ref = $sth->fetchrow_hashref('NAME_lc')) {
    print "Param: $hash_ref->{param}   Value: $hash_ref->{value}\n";
}

I get the following output:

Param: DlrGrp   Value: AMOS
Param: DlrGrp   Value: BALTIMORKW
Param: DlrGrp   Value: BAY AREA
Param: DlrGrp   Value: CAROLINAKW
Param: DlrGrp   Value: C-B KW
Param: Abc   Value: A
Param: Abc   Value: B
Param: Abc   Value: C
Param: Abc   Value: E
Param: StkClass   Value: 010
Param: StkClass   Value: 010A
Param: TargetAmount   Value: 15000

Here's my question. I'm very new to Perl and using hashes so please bear with me. In this case, is the ParamlrGrp the Key:Value pair or is it the ParamlrGrp and Value:AMOS that is the Key:Value pair?

What I need to be able to do is to fetch all this data and then grab all the DlrGrp records and run off and query additional tables using this data, but I don't know how to refer to these records. How do I do that?

I need to run a query later like "select * from loc where dlrgrp in ('AMOS','BALTIMORKW','BAY AREA', etc.)

 

[1DMF]

the way I do this is I wrote a module with the SQL functions i needed, one is getSQL(), as follows...

###############################################
############## Get SQL Routine ################
###############################################

sub getSQL {

#_0 = Table
#_1 = Columns
#_2 = Where
#_3 = Order By

# Define Record Set Array 
my @rs;

# Build SQL Statement
my $sel = "SELECT $_[1] FROM $_[0] WHERE $_[2]";

# Check for ORDER BY
if($_[3]){$sel .= " ORDER BY $_[3]";}

# Open DB Connection
 
my $db = new Win32::ODBC("FILEDSN=$DSN;") || die "getSQL Error Connecting: " . Win32::ODBC::Error();

# Run SQL Command
if(!$db->Sql("$sel")) {

	# Loop SQL Record Set
		while($db->FetchRow()){ 
				# Build Array of Hashes with SQL Data
				my %dt = $db->DataHash();
			    $rs[@rs] = \%dt;	
		}
	# Close DB Connection
	$db->Close();

	# Return Record Set Array of Hashes
	@rs;

} 
else{die "Error in getSQL ($sel)" . Win32::ODBC::Error();}

}

Now this is run using WIN32::ODBC for the SQL bit, it would seem you are using DBI, but the priciples are the same, so you could easily modify it to your requirements.

The way you can then call the routine is like so..

my @rs = &getSQL("pac.promo_params","param,value","plnrcode = ? and processflag = 'N'","");

you will then be returned an array of hashes, which is basically a recordset!

hope it makes sense, have a play and let me know how you get on.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[cfmartin2000]

To answer your question:

The word 'param' is the key and 'DlrGrp' is the value. Likewise, the word 'value' is the key and 'AMOS' is the value.

To see this more clearly, and if you have the time, you can use this:

use Data:umper; #At the beginning of your script.

#And then later...
### Fetch rows into a hash reference with lowercase field names
while ($hash_ref = $sth->fetchrow_hashref('NAME_lc')) {
print Dumper($hash_ref);
print "Param: $hash_ref->{param} Value: $hash_ref->{value}\n";
}

This will show you exactly what is happening in your sql when you fetch a row. good luck. Let me know how it works out.