El problema es que no consigo pasarlo a string. En otros campos lo he conseguido con un "cast", pero en éste no hay manera, me dá error.
Puede alguien echarme una mano? Os incluyo la query:
Cita:
¿no sería sustituirselect
a.iataCompanyCode as company,
a.flightNumber as flightNumber,
a.scheduledTime as scheduledTime,
if (a.statusCode is null, '', a.statusCode) as statusCode,
'L' as flightType,
if (a.tailNumber is null, '', (select aa.iataCode from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)) as aircraftType,
if ((select cast(aa.SeatNumber as char) from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber) is null, '-',(select cast(aa.SeatNumber as char) from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)) as seats,
if ((select cast(aaa.pax as char) from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight) is null, '-', (select cast(aaa.pax as char) from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight)) as passengers,
if ((select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight) is null or (select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber and a.tailnumber is not null) is null,0,(100 - ((select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)-(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight))/(select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber) * 100)) as ocupation
from `aodb`.`arrivalFlight` a where a.scheduledTime >= $P{fechaInformeDesde} and a.scheduledTime < $P{fechaInformeHasta}
union all
(select
b.iataCompanyCode as company,
b.flightNumber as flightNumber,
b.scheduledTime as scheduledTime,
if (b.statusCode is null, '', b.statusCode) as statusCode,
'S' as flightType,
if ((select bb.iataCode from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber) is null, '',(select bb.iataCode from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber)) as aircraftType,
if ((select cast(bb.SeatNumber as char) from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber) is null, '-',(select cast(bb.SeatNumber as char) from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber)) as seats,
if((select cast(bbb.pax as char) from `aodb`.`paxmerchandisepost` bbb where bbb.idDepartureFlight = b.idDepartureFlight) is null, '-',(select cast(bbb.pax as char) from `aodb`.`paxmerchandisepost` bbb where bbb.idDepartureFlight = b.idDepartureFlight)) as passengers,
if ((select bbb.pax from `aodb`.`paxmerchandisepost` bbb where bbb.idDepartureFlight = b.idDepartureFlight) is null or (select bb.SeatNumber from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber and b.tailnumber is not null) is null,0,(100 - ((select bb.SeatNumber from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber)-(select bbb.pax from `aodb`.`paxmerchandisepost` bbb where bbb.idDepartureFlight = b.idDepartureFlight))/(select bb.SeatNumber from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber) * 100)) as ocupation
from `aodb`.`departureFlight` b where b.scheduledTime >= $P{fechaInformeDesde} and b.scheduledTime < $P{fechaInformeHasta}
)
order by scheduledTime;
a.iataCompanyCode as company,
a.flightNumber as flightNumber,
a.scheduledTime as scheduledTime,
if (a.statusCode is null, '', a.statusCode) as statusCode,
'L' as flightType,
if (a.tailNumber is null, '', (select aa.iataCode from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)) as aircraftType,
if ((select cast(aa.SeatNumber as char) from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber) is null, '-',(select cast(aa.SeatNumber as char) from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)) as seats,
if ((select cast(aaa.pax as char) from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight) is null, '-', (select cast(aaa.pax as char) from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight)) as passengers,
if ((select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight) is null or (select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber and a.tailnumber is not null) is null,0,(100 - ((select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)-(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight))/(select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber) * 100)) as ocupation
from `aodb`.`arrivalFlight` a where a.scheduledTime >= $P{fechaInformeDesde} and a.scheduledTime < $P{fechaInformeHasta}
union all
(select
b.iataCompanyCode as company,
b.flightNumber as flightNumber,
b.scheduledTime as scheduledTime,
if (b.statusCode is null, '', b.statusCode) as statusCode,
'S' as flightType,
if ((select bb.iataCode from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber) is null, '',(select bb.iataCode from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber)) as aircraftType,
if ((select cast(bb.SeatNumber as char) from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber) is null, '-',(select cast(bb.SeatNumber as char) from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber)) as seats,
if((select cast(bbb.pax as char) from `aodb`.`paxmerchandisepost` bbb where bbb.idDepartureFlight = b.idDepartureFlight) is null, '-',(select cast(bbb.pax as char) from `aodb`.`paxmerchandisepost` bbb where bbb.idDepartureFlight = b.idDepartureFlight)) as passengers,
if ((select bbb.pax from `aodb`.`paxmerchandisepost` bbb where bbb.idDepartureFlight = b.idDepartureFlight) is null or (select bb.SeatNumber from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber and b.tailnumber is not null) is null,0,(100 - ((select bb.SeatNumber from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber)-(select bbb.pax from `aodb`.`paxmerchandisepost` bbb where bbb.idDepartureFlight = b.idDepartureFlight))/(select bb.SeatNumber from `aodb`.`aircraft` bb where bb.tailNumber = b.tailNumber) * 100)) as ocupation
from `aodb`.`departureFlight` b where b.scheduledTime >= $P{fechaInformeDesde} and b.scheduledTime < $P{fechaInformeHasta}
)
order by scheduledTime;
Cita:
porif (
(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight) is null or (select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber and a.tailnumber is not null) is null,
0,
(100 - ((select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)-(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight))/(select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber) * 100)) as ocupation
(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight) is null or (select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber and a.tailnumber is not null) is null,
0,
(100 - ((select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)-(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight))/(select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber) * 100)) as ocupation
Cita:
?? Pero me dá error, me sobra o me falta un paréntesis, y no encuentro el fallo.if (
(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight) is null or (select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber and a.tailnumber is not null) is null,
'-',
cast((100 - ((select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)-(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight))/(select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber) * 100) as char)) as ocupation
(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight) is null or (select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber and a.tailnumber is not null) is null,
'-',
cast((100 - ((select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber)-(select aaa.pax from `aodb`.`paxmerchandisepost` aaa where aaa.idArrivalFlight = a.idArrivalFlight))/(select aa.SeatNumber from `aodb`.`aircraft` aa where aa.tailNumber = a.tailNumber) * 100) as char)) as ocupation
Gracias de antemano, un saludo