Brett and Chas,
I did create two file handles and alter my conditional statements, one to
work when there is a variable, and one for when there's no variable. The
first SQL statement should return everything for a given filename; the other
should return everything for all filenames that meet the select criteria.
Under the first condition, the script works and creates an output file. The
second is still failing, as if it doesn't know what to do when $filename =
1. However, I did add a print $filename , and sure enough Filename = 1 when
no parameter is given. Could you look at the script below to determine
where the problem is?
Here is a portion of the script:
#-------------------Prepare Query - when parameter is given
$sth = $dbh->prepare("SELECT distinct sh.service_name,nef.filename,
st.service_type_name, cnh.node_name
FROM service_history sh,
service_type st,
customer_node_history cnh,
normalised_event_error nee,
normalised_event_file nef
WHERE nee.normalised_event_file_id=nef.NORMALISED_EVENT_FILE_ID
AND nee.error_message_id = 3024
AND nef.event_source like '%GC%' AND sh.service_status_code IN (3,11)
AND sh.service_type_id = st.service_type_id
AND nee.c_party_id = sh.service_name
AND SYSDATE BETWEEN sh.effective_start_date AND sh.effective_end_date
AND cnh.customer_node_id = sh.customer_node_id
AND sysdate between cnh.effective_start_date and cnh.effective_end_date
AND st.service_type_id in (20000,1000033,20009,3000181,
1000224,1000225,20003,3000022,3000023,1000036,
3000002,1000184,3000161,1000037,1000026,3000063,3000064,1000083,
3000141)
AND nef.filename = ?
AND NOT EXISTS (SELECT *
FROM product_instance_history pih,
product_history ph
WHERE pih.base_product_instance_id =
sh.base_product_instance_id
AND SYSDATE BETWEEN pih.effective_start_date AND
pih.effective_end_date
AND pih.product_instance_status_code IN (3,11)
AND pih.base_product_instance_id !=
pih.product_instance_id
AND pih.product_id = ph.product_id
AND SYSDATE BETWEEN ph.effective_start_date AND
ph.effective_end_date
AND ph.companion_ind_code = 1)
ORDER BY sh.service_name,nef.filename,st.service_type_name,
cnh.node_name") or die "Couldn't execute statement: ";
#----------------------Prepare Query - for when there is no parameter
$sthb = $dbhb->prepare("SELECT distinct sh.service_name,nef.filename,
st.service_type_name, cnh.node_name
FROM service_history sh,
service_type st,
customer_node_history cnh,
normalised_event_error nee,
normalised_event_file nef
WHERE nee.normalised_event_file_id=nef.NORMALISED_EVENT_FILE_ID
AND nee.error_message_id = 3024
AND nef.event_source like '%GC%' AND sh.service_status_code IN (3,11)
AND sh.service_type_id = st.service_type_id
AND nee.c_party_id = sh.service_name
AND SYSDATE BETWEEN sh.effective_start_date AND sh.effective_end_date
AND cnh.customer_node_id = sh.customer_node_id
AND sysdate between cnh.effective_start_date and cnh.effective_end_date
AND st.service_type_id in (20000,1000033,20009,3000181,
1000224,1000225,20003,3000022,3000023,1000036,
3000002,1000184,3000161,1000037,1000026,3000063,3000064,1000083,
3000141)
AND NOT EXISTS (SELECT *
FROM product_instance_history pih,
product_history ph
WHERE pih.base_product_instance_id =
sh.base_product_instance_id
AND SYSDATE BETWEEN pih.effective_start_date AND
pih.effective_end_date
AND pih.product_instance_status_code IN (3,11)
AND pih.base_product_instance_id !=
pih.product_instance_id
AND pih.product_id = ph.product_id
AND SYSDATE BETWEEN ph.effective_start_date AND
ph.effective_end_date
AND ph.companion_ind_code = 1)
ORDER BY sh.service_name,nef.filename,st.service_type_name,
cnh.node_name") or die "Couldn't execute statement: ";
#print "Enter Error File Name Beginning With GC: ";
#while (<>) || ($filename ne '')) { Read input from user
# if (defined($ARGV[1]))
# {
$filename = $ARGV[1] || 1;
# $filename = $ARGV[1];
if (defined($ARGV[1]))
{
$sth->execute($filename) || die "Couldn't execute
statement " . $sth->errstr;
# Read the matching records and print them out
$outputfile = "No_LD_$date";
$outputfile = "$L_DIRECTORY$outputfile";
$header = "Serv. Name Filename Service
Type Customer Name\n";
# Put this file into File array
push @file_array,$outputfile;
open (FILE,">>$outputfile") || die "can't open file:
$!\n";
print FILE "$header\n";
while (@data = $sth->fetchrow_array())
{
my $servicename = $data[0];
my $filename = $data[1];
my $service_type_name = $data[2];
my $node_name = $data[3];
#print " $servicename $filename
$service_type_name $node_name\n";
$each_file = "$servicename $filename
$service_type_name $space $node_name";
open (FILE,">>$outputfile");
print FILE "$each_file\n";
#write; # invoke format STDOUT to STDOUT
}
close FILE;
}
else
{
if ($filename = 1)
{
$sthb->execute() || die "Couldn't execute
statement.";
# Read the matching records and print them out
$outputfile = "No_LD_$date";
$outputfile = "$L_DIRECTORY$outputfile";
$header = "Serv. Name Filename Service
Type Customer Name\n";
# Put this file into File array
push @file_array,$outputfile;
open (FILE,">>$outputfile") || die "can't open file:
$!\n";
#print FILE "$header\n";
while (@data = $sthb->fetchrow_array())
{
my $servicename = $data[0];
my $filename = $data[1];
my $service_type_name = $data[2];
my $node_name = $data[3];
#print " $servicename $filename
$service_type_name $node_name\n";
$each_file = "$servicename $filename
$service_type_name $node_name";
open (FILE,">>$outputfile");
print FILE "$each_file\n";
}
}
}
close FILE;
print "Filename = $filename\n";
Thanks,
Kim
-----Original Message-----
From: Brett W. McCoy [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 29, 2001 9:39 AM
To: Kim Green
Cc: '[EMAIL PROTECTED]'
Subject: Re: Optional Variables
On Fri, 29 Jun 2001, Kim Green wrote:
> What's the proper syntax to indicate that a variable is optional? The
script
> that I have created works great when I pass in a variable, but the script
> need to execute the SQL even if I don't pass in a variable.
You can set a sane default for the variable:
my $option = $ARGV[0] || 1;
If $ARGV[0] is not defined, then $option will be 1.
Or you create your program logic accordingly to do one thing if the option
is defined and another thing is if isn't defined.
-- Brett
http://www.chapelperilous.net/btfwk/
------------------------------------------------------------------------
I B M
U B M
We all B M
For I B M!!!!
-- H.A.R.L.I.E.
