1
I have the following in my consultation:
SUM(vopValorTotal + vopTaxaComissao) as vopValorComComissao
Both vopValorTotal and vopTaxaCommission are previously created alias columns. Both are not null. However, I need a new column to add these two items into a new one, and the error returned is:
Unknown vopValorTotal column in field list'
How can I solve?
PS: The original query is a little big, but if it serves as a basis to help me, follows:
SELECT
vop.*,
ope.*,
opc.*,
ocv.ocvId,
ocv.ocvValor,
voo.*,
tiv.*,
aer.*,
tia.*,
treo.trtId as trtIdOrigem,
treo.treNome as treNomeOrigem,
treo.treICAO as treICAOOrigem,
treo.treIATA as treIATAOrigem,
tred.trtId as trtIdDestino,
tred.treNome as treNomeDestino,
tred.treICAO as treICAODestino,
tred.treIATA as treIATADestino,
cido.cidNome as treEnderecoCidadeOrigem,
cido.cidAlias as treAliasCidadeOrigem,
esto.estUF as treEnderecoEstadoOrigem,
cidd.cidNome as treEnderecoCidadeDestino,
cidd.cidAlias as treAliasCidadeDestino,
estd.estUF as treEnderecoEstadoDestino,
(
CASE WHEN opcTipo = 'fixed'
THEN
CASE WHEN opcValorUnico = 'S'
THEN opcValor
ELSE ocvValor
END
WHEN opcTipo = 'fixed-by-pax'
THEN
CASE WHEN opcValorUnico = 'S'
THEN opcValor
ELSE ocvValor
END
WHEN opcTipo = 'percent'
THEN
CASE WHEN opcValorUnico = 'S'
THEN
CASE WHEN vopValorFechado = 'S'
THEN (opcValor/100 * vopTaxaValorFechado)
ELSE
(opcValor/100 * (vopTaxaPousoOrigem + vopTaxaPousoDestino + vopTaxaTrecho))
END
ELSE
CASE WHEN vopValorFechado = 'S'
THEN (ocvValor/100 * vopTaxaValorFechado)
ELSE
(ocvValor/100 * (vopTaxaPousoOrigem + vopTaxaPousoDestino + vopTaxaTrecho))
END
END
END
)
as vopTaxaComissao,
(
CASE
WHEN vopValorFechado = 'S'
THEN (vop.vopTaxaValorFechado)
ELSE (vopTaxaPousoOrigem + vopTaxaPousoDestino + vopTaxaTrecho)
END
)
as vopValorTotal,
## AQUI QUE DÁ O ERRO - SE EU TIRO, A CONSULTA RETORNA OK
SUM(vopValorTotal + vopTaxaComissao) as vopValorComComissao
##
FROM sistema_voo_operadora vop
LEFT JOIN sistema_operadora ope ON vop.opeId = ope.opeId
LEFT JOIN sistema_voo voo ON vop.vooId = voo.vooId
LEFT JOIN sistema_tipo_voo tiv ON voo.tivId = tiv.tivId
LEFT JOIN sistema_aeronave aer ON vop.aerId = aer.aerId
LEFT JOIN sistema_tipo_aeronave tia ON aer.tiaId = tia.tiaId
LEFT JOIN sistema_trecho treo ON treo.treId = voo.vooOrigemId
LEFT JOIN sistema_trecho tred ON tred.treId = voo.vooDestinoId
LEFT JOIN sistema_cidade cido ON cido.cidId = treo.cidId
LEFT JOIN sistema_cidade cidd ON cidd.cidId = tred.cidId
LEFT JOIN sistema_estado esto ON esto.estId = cido.estId
LEFT JOIN sistema_estado estd ON estd.estId = cidd.estId
LEFT JOIN sistema_operadora_comissao opc ON opc.opeId = ope.opeId AND opc.tivId = tiv.tivId
LEFT JOIN sistema_operadora_comissao_voo ocv ON ocv.vopId = vop.vopId AND opc.opcId = ocv.opcId
WHERE tiv.tivId = 2
AND vooAtivo = 'S'
AND vooExcluido = 'N'
AND opeAtivo = 'S'
AND opeExcluido = 'N'
AND vopAtivo = 'S'
AND vopExcluido = 'N'
AND aerAtivo = 'S'
AND aerExcluido = 'N'
ORDER BY RAND()
LIMIT 0,4;
The expected result would be another column called vopValorCompled with the sum of the previous two. Ah, I also tried without SUM, just with (vopValorTotal + vopTaxaCommunity) vopValorCommunity.
You cannot use the alias in this situation, you need to repeat the whole expression again. Maybe it is the case to create some functions.
– bfavaretto
@bfavaretto I do not master creating functions in Mysql. I don’t even know how to proceed. In that case, I will try to replicate the expressions and return
– Maykel Esser
@bfavaretto just to give a return, with your tip worked! It was a little extensive the query, but it worked! ;-)
– Maykel Esser