How to add values of others in a new column? Ora-00904

Question

I created the columns A, B, C e D. When trying an E Column, which is the sum of the previous ones gives this error ORA-00904, have tried some examples I found in pequisa, gives in the same..

SELECT
  (SELECT Nvl(Sum(e1_saldo), 0) A
   FROM protheus.se1010
   INNER JOIN protheus.sed010 ON ed_codigo = e1_naturez
   WHERE se1010.d_e_l_e_t_ <> '*'
     AND sed010.d_e_l_e_t_ <> '*'
     AND e1_saldo <> '0'
     AND (To_date(e1_vencrea, 'YYYYMMDD')) = sysdate) A,

  (SELECT Nvl(Sum(e2_saldo), 0) B
   FROM protheus.se2010
   INNER JOIN protheus.sed010 ON ed_codigo = e2_naturez
   WHERE se2010.d_e_l_e_t_ <> '*'
     AND sed010.d_e_l_e_t_ <> '*'
     AND e2_saldo <> '0'
     AND (To_date(e2_vencrea, 'YYYYMMDD')) = sysdate) B,

  (SELECT SUM (saldo)
   FROM
     (SELECT Sum((e2_saldo *- 1)) SALDO
      FROM protheus.se2010 E2
      LEFT JOIN protheus.sa2010 ON a2_cod = e2_fornece
      AND a2_loja = e2_loja
      AND sa2010.d_e_l_e_t_ <> '*'
      LEFT JOIN protheus.sed010 ON ed_codigo = e2_naturez
      AND sed010.d_e_l_e_t_ <> '*'
      WHERE E2.d_e_l_e_t_ <> '*'
        AND e2_tipo <> 'PA'
        AND e2_saldo <> 0
        AND (To_date(e2_vencrea, 'YYYYMMDD')) < sysdate
      UNION ALL SELECT Sum(e1_saldo) C
      FROM protheus.se1010 E1
      LEFT JOIN protheus.sa1010 ON a1_cod = e1_cliente
      AND a1_loja = e1_loja
      AND sa1010.d_e_l_e_t_ <> '*'
      LEFT JOIN protheus.sed010 ON ed_codigo = e1_naturez
      AND sed010.d_e_l_e_t_ <> '*'
      WHERE E1.d_e_l_e_t_ <> '*'
        AND e1_tipo <> 'RA'
        AND e1_saldo <> 0
        AND (To_date(e1_vencrea, 'YYYYMMDD')) < sysdate)) C,

  (SELECT Sum(a6_salatu) D
   FROM protheus.sa6010 A6
   WHERE A6.d_e_l_e_t_ <> '*'
     AND ((a6_cod = '341'
           AND a6_agencia = '4807'
           AND a6_numcon = '12485')
          OR (a6_cod = '707'
              AND a6_agencia = '0001'
              AND a6_numcon = '000733063')
          OR (a6_cod = '033'
              AND a6_agencia = '4742'
              AND a6_numcon = '130033788'))) D,
       Sum(a + b + c + d) E
FROM dual

Error: ORA-00904: "D": invalid identifier 00904. 00000 - "%s: invalid Dentifier" *Cause:
*Action: Error on line: 27 Column: 14

If I take the D out of the sum, the error indicates the C.

Answer 1

The context that this being referenced is still with the data, one way to solve this would be by creating sub queries, from A,B,C and D and then using to create column E. Something like:

SELECT
       Sum(a.valor + b.valor + c.valor + d.valor) E
FROM 

(SELECT Nvl(Sum(e1_saldo), 0) valor
   FROM protheus.se1010
   INNER JOIN protheus.sed010 ON ed_codigo = e1_naturez
   WHERE se1010.d_e_l_e_t_ <> '*'
     AND sed010.d_e_l_e_t_ <> '*'
     AND e1_saldo <> '0'
     AND (To_date(e1_vencrea, 'YYYYMMDD')) = sysdate) A,

  (SELECT Nvl(Sum(e2_saldo), 0)  valor
   FROM protheus.se2010
   INNER JOIN protheus.sed010 ON ed_codigo = e2_naturez
   WHERE se2010.d_e_l_e_t_ <> '*'
     AND sed010.d_e_l_e_t_ <> '*'
     AND e2_saldo <> '0'
     AND (To_date(e2_vencrea, 'YYYYMMDD')) = sysdate) B,

  (SELECT SUM (saldo) valor
   FROM
     (SELECT Sum((e2_saldo *- 1)) SALDO
      FROM protheus.se2010 E2
      LEFT JOIN protheus.sa2010 ON a2_cod = e2_fornece
      AND a2_loja = e2_loja
      AND sa2010.d_e_l_e_t_ <> '*'
      LEFT JOIN protheus.sed010 ON ed_codigo = e2_naturez
      AND sed010.d_e_l_e_t_ <> '*'
      WHERE E2.d_e_l_e_t_ <> '*'
        AND e2_tipo <> 'PA'
        AND e2_saldo <> 0
        AND (To_date(e2_vencrea, 'YYYYMMDD')) < sysdate
      UNION ALL SELECT Sum(e1_saldo) C
      FROM protheus.se1010 E1
      LEFT JOIN protheus.sa1010 ON a1_cod = e1_cliente
      AND a1_loja = e1_loja
      AND sa1010.d_e_l_e_t_ <> '*'
      LEFT JOIN protheus.sed010 ON ed_codigo = e1_naturez
      AND sed010.d_e_l_e_t_ <> '*'
      WHERE E1.d_e_l_e_t_ <> '*'
        AND e1_tipo <> 'RA'
        AND e1_saldo <> 0
        AND (To_date(e1_vencrea, 'YYYYMMDD')) < sysdate)) C,

  (SELECT Sum(a6_salatu) valor
   FROM protheus.sa6010 A6
   WHERE A6.d_e_l_e_t_ <> '*'
     AND ((a6_cod = '341'
           AND a6_agencia = '4807'
           AND a6_numcon = '12485')
          OR (a6_cod = '707'
              AND a6_agencia = '0001'
              AND a6_numcon = '000733063')
          OR (a6_cod = '033'
              AND a6_agencia = '4742'
              AND a6_numcon = '130033788'))) D,

The query is not correct, just to help you create the solution, I hope it helps.