perl win32:odbc exceution of query with unions

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am using win32::odbc I want to exceute 7 queries written in MS access code shown below.

Now Sp1 query works well. this does not have a join. Sp2 query that i am executing gives an error

"Unable to execute query: -3084 1 There is no default value in ..Microsoft ODBC M crosoft Access Driver.. parameter Guidance for ..0"... "

can some body help me with this??

Sp2 query is a join query The sql view of the Sp1 & Sp2 Queries are as follows

Sp1 Query:

PARAMETERS [Enter Week Ending Date (yyyy/mm/dd)] DateTime;
SELECT [WAR Data].[Week Ending], [WAR Data].Resource, MRs.[AXA Number]
+ AS [NIKU Acct Cd], [WAR Data].[Module code] & " - MR Related Activit
+ies" AS [Task Desc], Sum([WAR Data].Mon) AS Mon, Sum([WAR Data].Tue) 
+AS Tue, Sum([WAR Data].Wed) AS Wed, Sum([WAR Data].Thu) AS Thu, Sum([
+WAR Data].Fri) AS Fri, Sum([WAR Data].Sat) AS Sat, Sum([WAR Data].Sun
+) AS Sun
FROM [WAR Data] INNER JOIN MRs ON [WAR Data].[Module code]=MRs.[MR Num
+ber]
WHERE ((([WAR Data].[Activity code]) Not In ("PPMPT","PCAL","PCSPT")))
GROUP BY [WAR Data].[Week Ending], [WAR Data].Resource, MRs.[AXA Numbe
+r], [WAR Data].[Module code] & " - MR Related Activities"
HAVING ((([WAR Data].[Week Ending])=[Enter Week Ending Date (yyyy/mm/d
+d)]));




Sp2 Query:
PARAMETERS [Enter Week Ending Date (yyyy/mm/dd)] DateTime;
SELECT [WAR Data].[Week Ending], [WAR Data].Resource, [MR Type - NIKU 
+Acct Cd Mapping].[NIKU Account Code], "Guidance for " & [WAR Data].[M
+odule Code] & " (" & [MRs].[AXA Number] & ")" AS Task, Sum([WAR Data]
+.Mon) AS Mon, Sum([WAR Data].Tue) AS Tue, Sum([WAR Data].Wed) AS Wed,
+ Sum([WAR Data].Thu) AS Thu, Sum([WAR Data].Fri) AS Fri, Sum([WAR Dat
+a].Sat) AS Sat, Sum([WAR Data].Sun) AS Sun
FROM ([WAR Data] INNER JOIN MRs ON [WAR Data].[Module code] = MRs.[MR 
+Number]) INNER JOIN [MR Type - NIKU Acct Cd Mapping] ON MRs.[Request 
+Type] = [MR Type - NIKU Acct Cd Mapping].[MR Type]
WHERE ((([WAR Data].[Activity code])="PPMPT"))
GROUP BY [WAR Data].[Week Ending], [WAR Data].Resource, [MR Type - NIK
+U Acct Cd Mapping].[NIKU Account Code], "Guidance for " & [WAR Data].
+[Module Code] & " (" & [MRs].[AXA Number] & ")"
HAVING ((([WAR Data].[Week Ending])=[Enter Week Ending Date (yyyy/mm/d
+d)]))
UNION SELECT [WAR Data].[Week Ending], [WAR Data].Resource, [MR Type -
+ NIKU Acct Cd Mapping].[NIKU Account Code], "Onsite Coordination for 
+" & [WAR Data].[Module Code] & " (" & [MRs].[AXA Number] & ")" AS Tas
+k, Sum([WAR Data].Mon) AS Mon, Sum([WAR Data].Tue) AS Tue, Sum([WAR D
+ata].Wed) AS Wed, Sum([WAR Data].Thu) AS Thu, Sum([WAR Data].Fri) AS 
+Fri, Sum([WAR Data].Sat) AS Sat, Sum([WAR Data].Sun) AS Sun
FROM ([WAR Data] INNER JOIN MRs ON [WAR Data].[Module code] = MRs.[MR 
+Number]) INNER JOIN [MR Type - NIKU Acct Cd Mapping] ON MRs.[Request 
+Type] = [MR Type - NIKU Acct Cd Mapping].[MR Type]
WHERE ((([WAR Data].[Activity code])="PCSPT"))
GROUP BY [WAR Data].[Week Ending], [WAR Data].Resource, [MR Type - NIK
+U Acct Cd Mapping].[NIKU Account Code], "Onsite Coordination for " & 
+[WAR Data].[Module Code] & " (" & [MRs].[AXA Number] & ")"
HAVING ((([WAR Data].[Week Ending])=[Enter Week Ending Date (yyyy/mm/d
+d)]));


use strict;
use Win32::ODBC;
my $db;
#print "\nDisplay Storored Procedure \n";
#print "----------------------------------\n";
$db = new Win32::ODBC( "Niku Data" ) || die "Error connecting: " ;    
Win32::ODBC::Error();
#
#Execute_Query("Sp1", "2004/05/16");       
#print "debug $db\n";
Execute_Query("Sp2exec", "2004/05/16");
print "debug $db\n";
#Execute_Query("Sp3", "2004/05/16");
#print "debug $db\n";
#Execute_Query("Sp4", "2004/05/16");
#print "debug $db\n";
#Execute_Query("Sp5", "2004/05/16");
#print "debug $db\n";
#Execute_Query("Sp6", "2004/05/16");
#print "debug $db\n";
#Execute_Query("Sp7", "2004/05/16");
#print "debug $db\n";
#Execute_Query("Sp8", "2004/05/16");
#print "debug $db\n";
$db-> Close();    

sub Execute_Query {
print "query".$_[0]." ".$_[1];
 if( ! $db-> Sql( "execute ". $_[0]." ".$_[1] ) )   {
 #if( ! $db-> Sql( "execute  Sp2  2004/05/16" ) )   {
        while( $db-> FetchRow() ) {
            Prt_Row($db);
        }
        #while (  $db-> MoreResults() ) {
#                while( $db-> FetchRow() ) {        
#                Prt_Row($db);
#            }
#        }             
    } else {
        print "Unable to execute query: " . $db-> Error() . "\n";
        
    
    
    
    }

}
sub Prt_Row {
     $db = shift;
     my %Data = $db-> DataHash;
     my @list = keys(%Data); 
     print @list;
     print %Data;
#    print $Data{"Resource"}  .",";
#    print $Data{"Task Desc"} .",";
#    print $Data{"Mon"} .",";
#    print $Data{"Tue"} .",";
#    print $Data{"Wed"} .",";
#    print $Data{"Thu"} .",";
#    print $Data{"Fri"} .",";
#    print $Data{"Sat"} .",";
#    print $Data{"Sun"} .",";
     print "\n";        
}

# End
[download]

20040524 Edit by Corion: Added formatting, added readmore tag

Comment on perl win32:odbc exceution of query with unions Download Code