I've tried the SQL in earlier versions of this script, and I had no luck using an AS statement. When I run the script now I get:
SQL ERROR: Bad set function before FROM clause.
Execution ERROR: No command found!.
$VAR1 = undef;
When I remove the AS statement:
my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0");
$sth->execute;
my @cols = @{$sth->{NAME}};
shift @cols;
my $avgSQL =
'SELECT '
. join(', ', map { "avg($_) \n" } @cols )
. ' FROM results';
my $avgsHashRef = $dbh->selectrow_hashref($avgSQL);
I get
Execution ERROR: Can't use AVG on a string! called from ./runsql3.sql
+at 24.
DBD::CSV::db selectrow_hashref failed: Attempt to fetch row from a Non
+-SELECT statement [for Statement "SELECT avg(ERWWCOMMUNITIESMemoryPag
+essec)
, avg(ERWWCOMMUNITIESNetworkInterfaceCompaqEthernetFastEthernetAdapter
+ModuleBytesTotalPERsec)
, avg(ERWWCOMMUNITIESNetworkInterfaceMSTCPLoopbackinterfaceBytesTotalP
+ERsec)
, avg(ERWWCOMMUNITIESNetworkInterfaceCompaqEthernetFastEthernetAdapter
+ModuleCurrentBandwidth)
, avg(ERWWCOMMUNITIESNetworkInterfaceMSTCPLoopbackinterfaceCurrentBand
+width)
, avg(ERWWCOMMUNITIESPhysicalDisk0CPCTDiskTime)
, avg(ERWWCOMMUNITIESPhysicalDisk1DPCTDiskTime)
, avg(ERWWCOMMUNITIESPhysicalDiskTotalPCTDiskTime)
, avg(ERWWCOMMUNITIESPhysicalDiskTotalAvgDiskQueueLength)
, avg(ERWWCOMMUNITIESProcessorTotalPCTProcessorTime)
, avg(ERWWDC1MemoryPagesPERsec)
, avg(ERWWDC1NetworkInterfaceIntelPRO100NetworkConnectionBytesTotalPER
+sec)
, avg(ERWWDC1NetworkInterfaceMSTCPLoopbackinterfaceBytesTotalPERsec)
, avg(ERWWDC1NetworkInterfaceIntelPRO100NetworkConnectionCurrentBandwi
+dth)
, avg(ERWWDC1NetworkInterfaceMSTCPLoopbackinterfaceCurrentBandwidth)
, avg(ERWWDC1PhysicalDisk0CPCTDiskTime)
, avg(ERWWDC1PhysicalDiskTotalPCTDiskTime)
, avg(ERWWDC1PhysicalDiskTotalAvgDiskQueueLength)
, avg(ERWWDC1ProcessorTotalPCTProcessorTime)
, avg(ERWWNABMemoryPagesPERsec)
, avg(ERWWNABNetworkInterfaceIntel8255xbasedIntegratedFastEthernetByte
+sTotalPERsec)
, avg(ERWWNABNetworkInterfaceMSTCPLoopbackinterfaceBytesTotalPERsec)
, avg(ERWWNABNetworkInterfaceIntel8255xbasedIntegratedFastEthernetCurr
+entBandwidth)
, avg(ERWWNABNetworkInterfaceMSTCPLoopbackinterfaceCurrentBandwidth)
, avg(ERWWNABPhysicalDisk0CPCTDiskTime)
, avg(ERWWNABPhysicalDiskTotalPCTDiskTime)
, avg(ERWWNABPhysicalDiskTotalAvgDiskQueueLength)
, avg(ERWWNABProcessorTotalPCTProcessorTime)
, avg(ERWWSEARCHMemoryPagesPERsec)
, avg(ERWWSEARCHNetworkInterfaceBroadcomNetXtremeGigabitEthernetDriver
+BytesTotalPERsec)
, avg(ERWWSEARCHNetworkInterfaceIntelPRO100NetworkConnectionBytesTotal
+PERsec)
, avg(ERWWSEARCHNetworkInterfaceMSTCPLoopbackinterfaceBytesTotalPERsec
+)
, avg(ERWWSEARCHNetworkInterfaceBroadcomNetXtremeGigabitEthernetDriver
+CurrentBandwidth)
, avg(ERWWSEARCHNetworkInterfaceIntelPRO100NetworkConnectionCurrentBan
+dwidth)
, avg(ERWWSEARCHNetworkInterfaceMSTCPLoopbackinterfaceCurrentBandwidth
+)
, avg(ERWWSEARCHPhysicalDisk0CDPCTDiskTime)
, avg(ERWWSEARCHPhysicalDiskTotalPCTDiskTime)
, avg(ERWWSEARCHPhysicalDiskTotalAvgDiskQueueLength)
, avg(ERWWSEARCHProcessorTotalPCTProcessorTime)
, avg(ERWWSQLWEBMemoryPagesPERsec)
, avg(ERWWSQLWEBNetworkInterfaceBroadcomNetXtremeGigabitEthernetDriver
+BytesTotalPERsec)
, avg(ERWWSQLWEBNetworkInterfaceMSTCPLoopbackinterfaceBytesTotalPERsec
+)
, avg(ERWWSQLWEBNetworkInterfaceBroadcomNetXtremeGigabitEthernetDriver
+CurrentBandwidth)
, avg(ERWWSQLWEBNetworkInterfaceMSTCPLoopbackinterfaceCurrentBandwidth
+)
, avg(ERWWSQLWEBPhysicalDisk0CDPCTDiskTime)
, avg(ERWWSQLWEBPhysicalDiskTotalPCTDiskTime)
, avg(ERWWSQLWEBPhysicalDiskTotalAvgDiskQueueLength)
, avg(ERWWSQLWEBProcessorTotalPCTProcessorTime)
FROM results"] at ./runsql3.sql line 24.
$VAR1 = undef;
Same error if I trim down the sql statement. I have also re-looked at my csv file and the columns data is numeric. I'm stumped!