MySQL Forums
Forum List  »  Optimizer & Parser

Re: Field calculated using the WHERE
Posted by: Rick James
Date: January 15, 2011 12:42AM

AS Viagem_Situacao is an alias.
(SELECT Viagem_Situacao) is meaningless. If you were trying to get what that alias referred to, then leave off 'SELECT'. But then you will hit a different issue -- You cannot use computed information in a WHERE clause. However, you can do the equivalent in a HAVING clause, since that is evaluated after the expression is computed.

I suspect the new version is catching what should have been an error all along. Rewrite your queries. Perhaps like

SELECT  Viagem.Id, Viagem.DataSaida, Viagem.HoraSaida,
      ( SELECT  Situacao
            FROM  ViagemSituacao
            WHERE  Viagem_Id = Viagem.Id
            ORDER BY  Id DESC
            LIMIT  1) AS Viagem_Situacao
    FROM  Viagem
    WHERE  Viagem.Confirmado
      AND  Viagem.DataSaida = @DataViagem
           (@IncluirBloqueado OR Viagem_Situacao <> "Bloqueada")
      AND  (@IncluirCancelado OR Viagem_Situacao <> "Cancelada")
      AND  (@IncluirConcluido OR Viagem_Situacao <> "ConcluĂ­da") 

Options: ReplyQuote

Written By
January 13, 2011 03:22PM
Re: Field calculated using the WHERE
January 15, 2011 12:42AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.