Query with calculation returning error in condition

Question

Query with calculation returning error in condition

Navigation

#1 by (4 votes)
#2 by (3 votes)

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 who have the negative balance, but this is giving the condition error, what could it be?

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

mysql

asked by anonymous 22.06.2016 / 20:19

2 answers

3

You are using the alias of the column in WHERE . The bank does not recognize this column, in the ESTTRAN case. Change your query to this:

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 WHERE for the formula of your column that is used instead of alias .

22.06.2016 / 20:26

select dynamic Get difference between defined / current hours

score 4 · Accepted Answer

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

Looking like this:

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