Query with calculation returning error in condition

Asked

Viewed 75 times

2

I have the following select:

SELECT  
  imp_loja       AS LOJA,
  imp_item       AS ITEM,
  imp_desc       AS DESCRICAO,
  imp_dias_giro  AS DIAS_DE_GIRO,
  imp_nec        AS NECESSIDADE,
  imp_pedido     AS PEDIDO,
  imp_bancao     AS BANCAO,
  imp_romaneio   AS ROMANEIO, 
  imp_transito   AS TRANSITO,
  imp_gondula    AS GONDULA,
  imp_fator      AS FATOR,
  imp_reposicao  AS REPOSICAO,
  imp_estoque    AS ESTOQUE,
  (imp_nec - imp_fator) AS NEC_FAT,
  (imp_estoque / imp_fator) AS EXT_X_FATOR,
  (imp_estoque + imp_transito)ESTTRAN,
  imp_data       AS DATA
  FROM importacao WHERE ESTTRAN < 0 ORDER BY imp_desc

I want to show only those with the negative balance, only it is giving error in the condition, what could be ?

SQL Execution error #1054. Resonse from the database: Unknown column 'ESTTRAN' in 'Where cluase'

mysql
  • 2

    you cannot use alias, in which case you need to do the calculation again.

2 answers

4


If you want to use the ALIAS you can use the HAVING. His logic is the same as WHERE.

Thus remaining:

SELECT  
  imp_loja       AS LOJA,
  imp_item       AS ITEM,
  imp_desc       AS DESCRICAO,
  imp_dias_giro  AS DIAS_DE_GIRO,
  imp_nec        AS NECESSIDADE,
  imp_pedido     AS PEDIDO,
  imp_bancao     AS BANCAO,
  imp_romaneio   AS ROMANEIO, 
  imp_transito   AS TRANSITO,
  imp_gondula    AS GONDULA,
  imp_fator      AS FATOR,
  imp_reposicao  AS REPOSICAO,
  imp_estoque    AS ESTOQUE,
  (imp_nec - imp_fator) AS NEC_FAT,
  (imp_estoque / imp_fator) AS EXT_X_FATOR,
  (imp_estoque + imp_transito) AS ESTTRAN,
  imp_data       AS DATA
FROM importacao HAVING ESTTRAN < 0 ORDER BY imp_desc
  • 1

    perfect,was that right.

3

Are you using the alias of the column in the WHERE. The bank does not recognise this column in the case ESTTRAN. Alter your query for that reason:

SELECT  
  imp_loja       AS LOJA,
  imp_item       AS ITEM,
  imp_desc       AS DESCRICAO,
  imp_dias_giro  AS DIAS_DE_GIRO,
  imp_nec        AS NECESSIDADE,
  imp_pedido     AS PEDIDO,
  imp_bancao     AS BANCAO,
  imp_romaneio   AS ROMANEIO, 
  imp_transito   AS TRANSITO,
  imp_gondula    AS GONDULA,
  imp_fator      AS FATOR,
  imp_reposicao  AS REPOSICAO,
  imp_estoque    AS ESTOQUE,
  (imp_nec - imp_fator) AS NEC_FAT,
  (imp_estoque / imp_fator) AS EXT_X_FATOR,
  (imp_estoque + imp_transito) AS ESTTRAN,
  imp_data       AS DATA
FROM importacao WHERE (imp_estoque + imp_transito) < 0 ORDER BY imp_desc

See that in the WHERE to formula of its column is used, and no longer the alias.

Browser other questions tagged mysql

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