Perl Database Report 2

[V00D00]

I need some guidance on the best way to start a new project. I have to run several queries against a database to retrieve counts. I would like to have a perl script make all these separate queries, store the values, and then print them to a text file so it is nice and clean.


Attached is the code I have started with, but I think I am going to have some problems with this implementation do to fetchrow.

#!/usr/local/bin/perl
use warnings;
#use strict;
use DBI;
use Mail::Sender;

$ENV{DBPATH} = "/usr/task";
$ENV{DBF} = "/usr/task/task.dbs";
$ENV{DB} = "task";
$ENV{DBTEMP} = "/tmp";
$ENV{INFORMIXDIR} = "/usr/informix";
$ENV{PATH} = "$ENV{PATH}:/usr/local/bin:/usr/informix/bin";
my $wkd ="/usr/task/leadcount/";


#Esatblish todays date

my $todaysdate = (time - 25200);
my ($todaysec,$todaymin,$todayhour,$todayday,$todaymon,”.
“$todayyear,$todaywday,$todayyday,$todayisdst) = gmtime ($todaysdate);

my $today = "$todaymon/$todayday/$todayyear";
my $tdate = sprintf("%02d%02d%04d", (split('/',$today)));

# Establish the Begining and Ending Dates for Records that are only a week old.

my $week1_begin = (time - 543600);
my $week1_end = (time - 25200);

my ($week1_begin_sec,$week1_begin_min,$week1_begin_hour,$week1_begin_day,”.
$week1_begin_mon,$week1_begin_year,$week1_begin_wday,$week1_begin_yday,”.
$week1_begin_isdst) = gmtime ($week1_begin);

my ($week1_end_sec,$week1_end_min,$week1_end_hour,$week1_end_day,”.
$week1_end_mon,$week1_end_year,$week1_end_wday,$week1_end_yday,”.
$week1_end_isdst) = gmtime ($week1_end);

	$week1_begin_mon = $week1_begin_mon + 1;
	$week1_begin_year = $week1_begin_year + 1900;

	$week1_end_mon = $week1_end_mon + 1;
	$week1_end_year = $week1_end_year + 1900;	


my $week1_startdate = "$week1_begin_mon/$week1_begin_day/$week1_begin_year";
my $week1_enddate = "$week1_end_mon/$week1_end_day/$week1_end_year";

$week1_startdate = sprintf("%02d/%02d/%04d",(split('/',$week1_startdate)));
$week1_enddate = sprintf("%02d/%02d/%04d",(split('/',$week1_enddate)));

print "\n";
print "Week One Start Date = $week1_startdate\n";
print "Week One End Date = $week1_enddate\n";

my $sql_first_week_wkv_spg =	"select count(*), leadcode.description ".
				"from leadcode, outbound ".
				"where leadcode.lead_src = outbound.lead_src ".
				"and (listid between \"1000\" and \"7599999\") ".
				"and (load_date between \"$week1_startdate\" ".
				"and \"$week1_enddate\") ".
				"and azcc_outbound.lead_src = \"03\" ".
				"and (status = \"A\" or status ".
				"= \"B\" or status = \"LM\" ".
				"or status = \"N\" or status = \"NA\" ".
				"or status = \"NC\" ".
				"or status = \"SK\") ".
				"group by leadcode.description";

open(FH,">$wkd$tdate.leadcount.txt");

my $dbh = DBI->connect('task','','','AtomixDBD');

my $sql_fw_wkv_spg = $dbh->prepare($sql_first_week_wkv_spg);

my $rc = $sql_fw_wkv_spg->execute;

my @fw_wkv_spg;

print FH "LEAD COUNT SUMMARY\n\n";
print FH "\t\t\t\tWEEK1\tWEEK2\tWEEK3\tWEEK4\n";

while (@fw_wkv_spg = $sql_fw_wkv_spg->fetchrow) {
print FH "LEAD TYPE 1\t$fw_wkv_spg[0]";
	}

$sql_fw_wkv_spg->finish;

$dbh->disconnect;
close (FH);
undef $dbh;

As you can see I need to establish dates to apply to the query. I have established the dates for a 4 week period, and was planning on building queries using each of the 4 weeks. But fetchrow doesn't allow for me to place all the data from each query in the text file at once.

Any advice would be greatly appreciated, as always.

 

[V00D00]

After reading my own post I thought I would simplify things. I am attempting to run multiple queries against a database. The results from these queries will always be one row. I would like to put the results of each of these queries in a text file/report. The output should be similiar to the following:

LEAD COUNT SUMMARY

			WEEK1	WEEK2	WEEK3	WEEK4
LEAD TYPE 1	123	80	50	20
LEAD TYPE 2	234	101	75	54
LEAD TYPE 3	596	403	23	1

For each lead type/week there will be a separate query to establish the count.

 

[stevexff]

Look up the SQL UNION statement. It wil catenate the results of your individual queries into a single result table, so long as all the queries produce similar columns.

Then you can just use fetchall_arrayref() to get the results.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[V00D00]

If there was a way to give you 5 stars I would. Thank you. This is going to be be one monsterously long sql statement.

 

[Thargy]

Voodoo,

I know nothign about PERL, but some about SQL. I'm wandering from a differen forum, so please excuse the intrusion.

W.R.T. your proposed query, I think it could be simplified a bit. Ignoring the PERL'y bits (because I know nothing of them,) how about querying with

SELECT COUNT(*), description,
  FROM leadcode 
 INNER JOIN outbound USING (lead_src)
 INNER JOIN azcc_outbound USING (lead_src)
 WHERE lead_src = 3
   AND listid BETWEEN x AND y
   AND load_date BETWEEN start_date AND end_date
   AND status IN ('A','B','LM','N','NA','NC','SK')
 GROUP BY description

I noticed that your proposed SQL selects from azcc_outbound without mentioning it in the FROM clause. I don't see how this could have worked. Please note that I haven't tested the above SQL, so there's probably a syntax error in there somewhere. I hope you understand that I'm only trying to tweak the SQL and not the PERL.

Regards

Tharg

Grinding away at things Oracular

 

[V00D00]

Thanks Tharg,

Actually the code I posted is a much abbreviated version for simplicity sakes. I most likely screwed things up a bit in my simplification.

But thank you very much for suggesting an optimized version of the SQL.

On the Perl side of things, I have remaining issue. Using the UNION I get all the results in one dataset. But, if I query for something and there is no data to return I run in to a problem. In order for this to work I need to make sure that I can account for a 0 count as well.

My dataset with lead_src will be a two digit number between 00 and 99. So in this scenario I need to make sure the same data set appears in the same order every time.

Any ideas?

 

[Thargy]

Voodoo,

a union does an implicit sort and distinct on the result set (at least it does in oracle).

SQL> 
SQL> SELECT 'AAA' FROM DUAL
  2  UNION
  3  SELECT 'BBB' FROM DUAL
  4  UNION
  5  SELECT 'CCC' FROM DUAL;

'AA                                                                             
---                                                                             
AAA                                                                             
BBB                                                                             
CCC                                                                             

SQL> 
SQL> 
SQL> --Now scramble the order
SQL> SELECT 'AAA' FROM DUAL
  2  UNION
  3  SELECT 'CCC' FROM DUAL
  4  UNION
  5  SELECT 'BBB' FROM DUAL
  6  UNION
  7  SELECT 'CCC' FROM DUAL;

'AA                                                                             
---                                                                             
AAA                                                                             
BBB                                                                             
CCC                                                                             

SQL> 
SQL> --Note the implicit distinct and sort in the above result set.
SQL> 
SQL> SELECT 'AAA' FROM DUAL
  2  UNION
  3  SELECT 'CCC' FROM DUAL
  4  UNION
  5  SELECT 'BBB' FROM DUAL
  6  UNION
  7  SELECT 'CCC' FROM DUAL
  8  order by 1 desc;

'AA                                                                             
---                                                                             
CCC                                                                             
BBB                                                                             
AAA                                                                             

SQL> 
SQL> spool off

Note that in the above script, I had to use an 'order by' clause to get the descending sort. The only way to guarantee the order is to use the order by clause. Never rely on anything default or implicit, as this makes your code incredibly sensitive to its environment. If you want it sorted, always say so explicitly.

I hope this helps

Regards

Tharg

Grinding away at things Oracular

 

[V00D00]

In your example:

SQL> SELECT 'AAA' FROM DUAL
  2  UNION
  3  SELECT 'CCC' FROM DUAL
  4  UNION
  5  SELECT 'BBB' FROM DUAL
  6  UNION
  7  SELECT 'CCC' FROM DUAL
  8  order by 1 desc;

What is the return set if there is no BBB in DUAL.

For my example I am counting the instances of AAA BBB and CCC, will I get

128 AAA
0 BBB
13 CCC

 

[V00D00]

Okay. I have come up with the SQL that will return all the results for counts even if the count is 0. My question returns to Perl.

I will be running 4 different queries, one for each week to produce its counts. My question now is how do I use the fetchall_arrayref() to populate my report?

Hope this is not to generic of a question, but I have not used fetchall_arrayref() before.

 

[stevexff]

fetchall_arrayref() is a shorthand equivalent for looping through all the result rows and stuffing them into an array. It returns an array reference. The simplest way to visualise what comes back is to

use Data::Dumper;

# connect, run query

print Dumper($array_ref);

Once you understand this structure, it's easy to loop through it to print your report.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]