Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

cast a una cuenta, campo bigDecimal

Estas en el tema de cast a una cuenta, campo bigDecimal en el foro de Mysql en Foros del Web. Hola, tengo una consulta que utilizo en los informes con ireport (de jasperReport), y quiero que determinado valor llamado "ocupation" tome valor '-' si alguno ...
  #1 (permalink)  
Antiguo 07/09/2011, 09:49
 
Fecha de Ingreso: diciembre-2008
Mensajes: 233
Antigüedad: 16 años
Puntos: 1
cast a una cuenta, campo bigDecimal

Hola, tengo una consulta que utilizo en los informes con ireport (de jasperReport), y quiero que determinado valor llamado "ocupation" tome valor '-' si alguno de los valores que utiliza para calcular es nulo, o un valor decimal si ninguno lo es. Como no puedo meter valores string y BigDecimal en el mismo campo, lo que quiero es pasar el resultado a string, de manera que si alguno de los operandos es nulo me muestre '-' y si ninguno es nulo el valor con decimales.
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:
select
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;
¿no sería sustituir

Cita:
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
por

Cita:
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
?? Pero me dá error, me sobra o me falta un paréntesis, y no encuentro el fallo.

Gracias de antemano, un saludo

Etiquetas: campos, cast, query, select
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 02:11.