A variável de coluna SQL AS/400 (ecrã Verde) não foi encontrada

Question

A variável de coluna SQL AS/400 (ecrã Verde) não foi encontrada

Bom dia.

estou a executar uma consulta SQL dentro do AS/400 (ecrã verde) e tenho um problema com a obtenção de um nome de coluna alias.

Aqui está o meu guião.

  /* Join forecast info with item warehouse info */
  SELECT item, itmdsc, 
         today,
         October, QSMOIB,
         (QSMOIB - October) AS Sales_less_October      

    FROM
         /* Summarizing forecast data in a SQL query */
         (SELECT  item, itmdsc, whse,
                 today, range,
                 (wk01 + wk02 + wk03 + wk04) AS October 
            FROM FCSTCUR) AS f      
    JOIN     
         /* Select "Qty sold this period" */
         (SELECT ITNOIB, WHIDIB, QSMOIB
            FROM ITEMBLL0) AS iw

      ON f.item = iw.ITNOIB AND
         f.whse = iw.WHIDIB 
   WHERE Sales_less_October <> 0
ORDER BY Sales_less_October ASC

que quando eu o executar eu recebo o erro:

Column or global variable SALES_LESS_OCTOBER not found.

No entanto, se mudar o guião para o seguinte, funciona como esperado:

    /* Join forecast info with item warehouse info */
  SELECT item, itmdsc, 
         today,
         October, QSMOIB,
         (QSMOIB - October) /* AS Sales_less_October */   

    FROM
         /* Summarizing forecast data in a SQL query */
         (SELECT  item, itmdsc, whse,
                 today, range,
                 (wk01 + wk02 + wk03 + wk04) AS October 
            FROM FCSTCUR) AS f      
    JOIN     
         /* Select "Qty sold this period" */
         (SELECT ITNOIB, WHIDIB, QSMOIB
            FROM ITEMBLL0) AS iw

      ON f.item = iw.ITNOIB AND
         f.whse = iw.WHIDIB 
   WHERE (QSMOIB - October) <> 0
ORDER BY (QSMOIB - October) ASC

eu simplesmente substituí "Sales_less_October" por (QSMOIB-outubro).

Gostaria muito de poder usar o pseudónimo como é muito mais claro do que a apresentação "expressão numérica":

Muito obrigado, lamento não poder fazer o upload data...it está no nosso servidor.

0

sql-server sql db2-400

Author: Sescopeland, 2017-10-20

Source

1 answers

score 2 · Accepted Answer

Se quiser usar o seu nome falso com as suas cláusulas WHERE e ORDER BY, pode fazê-lo se aninhar o resto da consulta num WITH ():

WITH ONE AS (
   SELECT   item, itmdsc, 
            today,
            October, QSMOIB,
            (QSMOIB - October) AS Sales_less_October      

   FROM (
      SELECT   item, itmdsc, whse,
               today, range,
               (wk01 + wk02 + wk03 + wk04) AS October 
      FROM FCSTCUR
      ) AS f      
   JOIN (
      SELECT ITNOIB, WHIDIB, QSMOIB
      FROM ITEMBLL0
      ) AS iw
   ON f.item = iw.ITNOIB
   AND f.whse = iw.WHIDIB
   )
SELECT *
FROM ONE
WHERE Sales_less_October <> 0
ORDER BY Sales_less_October ASC