SQL Error in Report Topic is solved

[vario]

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.

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)

The query causing the fail is:

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

Whilst the successful query is:

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

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.

[vario]

OK, I have just realised my error!

In the working report I have aliased the sums in the query i.e sum(Concessions) as Concessions...

Sometimes it helps when it's all laid out clearly to see!