erro em consulta sql

[marcioe]

SELECT a_ctrc.*, IF(a_ctrc.FRETE_POR_CONTA='0', a_ctrc.CODIGO_REMETENTE,a_ctrc.CODIGO_DESTINATARIO) AS QUEM_PAGA_FRETE,a_destintarios.CODIGO_DESTINATARIO as A_CODIGO, a_destintarios.NOME_DESTINATARIO AS A_NOME, a_destintarios.CNPJ_DESTINATARIO AS A_CNPJ, a_destintarios.ENDERECO_DESTINATARIO AS A_END,  a_destintarios.E_MAIL_DESTINATARIO AS A_MAIL,b_destintarios.CODIGO_DESTINATARIO as B_CODIGO, b_destintarios.NOME_DESTINATARIO AS B_NOME, b_destintarios.CNPJ_DESTINATARIO AS B_CNPJ, b_destintarios.ENDERECO_DESTINATARIO AS B_END,  b_destintarios.E_MAIL_DESTINATARIO AS B_MAIL,a_cargas.CODIGO_CARGA, a_cargas.NOME_CARGAFROM ctrc as a_ctrcLEFT JOIN destinatarios AS a_destintarios ON a_ctrc.CODIGO_REMETENTE = a_destintarios.CODIGO_DESTINATARIOLEFT JOIN destinatarios AS b_destintarios ON a_ctrc.CODIGO_DESTINATARIO = b_destintarios.CODIGO_DESTINATARIOLEFT JOIN cargas AS a_cargas ON a_ctrc.CODIGO_CARGA = a_cargas.CODIGO_CARGAWHERE  a_ctrc.NUMERO != 0AND QUEM_PAGA_FRETE = 3606LIMIT 100

SE EU OMITIR A LINHA ( AND QUEM_PAGA_FRETE = 3606) FUNCIONA dá erro

(SQL Error (1054): Unknown column 'QUEM_PAGA_FRETE' in 'where clause' */)

mas a coluna está lá....

O RESULTADO

[evandro]

Olá,

Where só para nome real do campo. Para um alias, use o having.

WHERE a_ctrc.NUMERO != 0
having QUEM_PAGA_FRETE = 3606

[Yury]

Olá

vc pode usar o having como disse o Evandro ou fazer sub select como disse o Eric

para sub select veja o exemplo abaixo:

SELECT * FROM (
SELECT CLNOME AS NOME_CLIENTE,CLCDCL AS COD_CLIENTE
FROM ERCL01MS
ORDER BY CLNOME) AS T1
WHERE T1.NOME_CLIENTE LIKE '%IND%'

Abraço

[marcioe]

usi a dica do evendro deu certo...