SQL Error in Report
Posted: Wed Feb 28, 2024 10:53 am
Hi,
I have two similar reports with the same type of query but one fails to retrieve data though the report still runs and produces output (i.e the layout but with no data)
The queries are potentially tortuous as I am summing sums across tables joined by 'union all' as this is the only approach I can think of at this time! However, even if I have only one table the report still fail while the other runs perfectly.
All queries work when run at the database (e.g via phpMyAdmin).
I have taken the simplest, non-union example for you to look at!
The error happens building the summary table for the report sum() functions.
The query causing the fail is:
Whilst the successful query is:
The first two columns are text labels for the report, and the subquery in the from clause is the one that may consist of two or more queries in a "union all" (though still works for the second successful query).
Neil.
I have two similar reports with the same type of query but one fails to retrieve data though the report still runs and produces output (i.e the layout but with no data)
The queries are potentially tortuous as I am summing sums across tables joined by 'union all' as this is the only approach I can think of at this time! However, even if I have only one table the report still fail while the other runs perfectly.
All queries work when run at the database (e.g via phpMyAdmin).
I have taken the simplest, non-union example for you to look at!
The error happens building the summary table for the report sum() functions.
Code: Select all
[0] : ===PDO MESSAGE===
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Scanned' in 'field list'
===SQL===========
CREATE TABLE ___nu165defff4e0fbd____nu_summary SELECT count(*) as nu_count, SUM(`Scanned`) AS `nu_sum_Scanned` ,SUM(`Cash`) AS `nu_sum_Cash` ,SUM(`Card`) AS `nu_sum_Card` ,SUM(`Revenue`) AS `nu_sum_Revenue` ,SUM(`Shadow`) AS `nu_sum_Shadow` ,SUM(`Passengers`) AS `nu_sum_Passengers` ,SUM(`Manual`) AS `nu_sum_Manual` , Service,TicketType FROM ___nu165defff4e0fbd___ group by Service,TicketType
===BACK TRACE====
/var/www/forte/core/nurunpdf.php - line 1084 (nuRunQuery)
/var/www/forte/core/nurunpdf.php - line 226 (nuMakeSummaryTable)
/var/www/forte/core/nurunpdf.php - line 68 (nuBuildReport)
/var/www/forte/core/nurunpdf.php - line 23 (nuRunReportId)
Code: Select all
select '' as Tags, '2023-01-01 to 2023-06-30' as Period, Service, TicketType, sum(Scanned), sum(Manual), sum(Cash), sum(Card), sum(Transactions), sum(Passengers), sum(Revenue), sum(Shadow) from (select Service, TicketType, sum(if(SmartCard regexp '^[[:alnum:]]+$',PassengerCount/abs(PassengerCount),0)) as Scanned, sum(if(SmartCard regexp '^$|[*]+',PassengerCount/abs(PassengerCount),0)) as Manual, sum(if(Method = 'Cash',Price,0)) as Cash, sum(if(Method = 'Card',Price,0)) as Card, sum(PassengerCount/abs(PassengerCount)) as Transactions, sum(PassengerCount) as Passengers, sum(Price) as Revenue, sum(if(ShadowFare = 0,Price,ShadowFare)) as Shadow from ticket_2023 join ticketer_farestages on (FromStageID = UniqueName) where str_to_date(ScheduledStartDate,'%d/%m/%Y') between '2023-01-01' and '2023-06-30' group by Service, TicketType) as s group by Service, TicketType
Code: Select all
select '' as Tags, '2023-01-01 to 2023-06-30' as Period, Service, sum(Concessions) as Concessions, sum(PassengerCount) as PassengerCount, sum(Price) as Price, sum(ShadowFare) as ShadowFare from (select Service, sum(case when TicketType in ('ENCTS','ENCTS Manual','Companion') then PassengerCount else 0 end) as Concessions, sum(PassengerCount) as PassengerCount, sum(Price) as Price, sum(if(ShadowFare = 0,Price,ShadowFare)) as ShadowFare from ticket_2023 join ticketer_farestages on (FromStageID = UniqueName) where str_to_date(ScheduledStartDate,'%d/%m/%Y') between '2023-01-01' and '2023-06-30' group by Service) as s group by Service
Neil.