Add two "alias" columns in a new alias column

Asked

Viewed 363 times

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;

inserir a descrição da imagem aqui

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.

  • 1

    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 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

  • @bfavaretto just to give a return, with your tip worked! It was a little extensive the query, but it worked! ;-)

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.