Error in query SQL SERVER when trying to fetch a field

0

Good afternoon colleagues at work where I am placed to modify an SQL SERVER query where I must bring a field called ATR_DEPOSIT from a table called ACCOUNTING_TRANSACTION, the PRIMARY KEY field of this table is ATR_IDENTITY_CODE, it sounds very simple, but when trying to bring it I get different errors depending on where I put ACCOUNTING_TRANSACTION.ATR_DEPOSIT Does anyone know how to solve this? Thank you very much in advance.

This is the query that works very well without me trying to bring that field:

      SELECT 0 COMMENTS,'PREVIOUS BALANCE' AS ACO_NAME,1 AS ATR_IDENTITY_CODE, 
  'PREVIOUS BALANCE' AS ATR_DESCRIPTION, '' AS BAN_NAME, '' AS BAC_ACCOUNT_NUMBER,
  9834.0000 AS ATR_DEBIT, 5468.0000 AS ATR_CREDIT,
  CONVERT(DATETIME,'4/6/2018') AS ATR_DATETIME,
  '' AS ATR_DEPOSIT_IMAGE_PATH,
  11 AS ATR_ACCOUNTING_CONCEPT, 4366.0000 AS TOTAL, CONVERT(DATETIME,'4/6/2018') AS ATR_DATE_POSTED,
  'NO' AS AGING_STATUS , '0' as ATR_DEBIT_NATIONAL_CURRENCY , '0' as ATR_CREDIT_NATIONAL_CURRENCY,
  '0' AS BALANCE_NATIONAL_CURRENCY
  UNION SELECT isnull(
      (SELECT COUNT (ATM_ACCOUNTING) FROM ACCOUNTING_TRANSACTION_MESSAGE
      WHERE ATM_ACCOUNTING = ATR_IDENTITY_CODE ) ,0)
      COMMENTS , ACCOUNTING_CONCEPT.ACO_NAME AS ACO_NAME,
      ACCOUNTING_TRANSACTION.ATR_IDENTITY_CODE AS ATR_IDENTITY_CODE,
      ACCOUNTING_TRANSACTION.ATR_DESCRIPTION AS ATR_DESCRIPTION,
      BANK.BAN_NAME AS BAN_NAME,
      BANK_ACCOUNT.BAC_ACCOUNT_NUMBER AS BAC_ACCOUNT_NUMBER,
      ATR_DEBIT, ATR_CREDIT, ATR_DATETIME,ATR_DEPOSIT_IMAGE_PATH, ATR_ACCOUNTING_CONCEPT,
      ISNULL(
        (SELECT SUM(A.ATR_DEBIT - A.ATR_CREDIT)
        FROM ACCOUNTING_TRANSACTION A 
        WHERE A.ATR_ACCOUNTING_GROUP = 'O461' AND A.ATR_DATETIME <= ACCOUNTING_TRANSACTION.ATR_DATETIME
        AND A.ATR_ACCOUNTING_CONCEPT <> 11 AND ATR_STATUS = 'A'),0) AS TOTAL, 
        ATR_DATE_POSTED,
        IIF(ATR_CREDIT_STATUS IS NULL AND ATR_DEBIT_STATUS IS NULL, 'NO', 'YES') AS AGING_STATUS, 
        ATR_DEBIT_NATIONAL_CURRENCY, ATR_CREDIT_NATIONAL_CURRENCY ,
        (ATR_DEBIT_NATIONAL_CURRENCY - ATR_CREDIT_NATIONAL_CURRENCY) AS BALANCE_NATIONAL_CURRENCY
        FROM ACCOUNTING_TRANSACTION
          INNER JOIN ACCOUNTING_CONCEPT
            ON ACCOUNTING_TRANSACTION.ATR_ACCOUNTING_CONCEPT = ACCOUNTING_CONCEPT.ACO_IDENTITY_CODE
          LEFT OUTER JOIN BANK
            ON ACCOUNTING_TRANSACTION.ATR_BANK = BANK.BAN_CODE
          LEFT OUTER JOIN BANK_ACCOUNT
            ON ACCOUNTING_TRANSACTION.ATR_BANK_ACCOUNT = BANK_ACCOUNT.BAC_CODE
        WHERE ATR_ACCOUNTING_GROUP = 'O461' AND ATR_STATUS = 'A'
        AND ATR_DATETIME >= CAST('4/6/2018 00:00:00.001' AS DATETIME)
        AND ATR_DATETIME <= CAST('4/13/2018 23:59:59.999' AS DATETIME)ORDER BY ATR_DATETIME
    
asked by Cristian D. 14.04.2018 в 21:22
source

1 answer

0

Cordial greeting Colleagues, I already solved the error, thanks to all those who commented and tried to help me, the solution was very easy, but because of my ignorance I did not know well how a UNION works, when there is a UNION there must be the same number of fields , in the same order and of the same type on both sides of the UNION (I know that many already knew but others did not know):

The query has a first SELECT ............ and a UNION SELECT. I just had to add the field ATR_DEPOSIT in the first SELECT and in the second SELECT that goes after the UNION, both in the same position of each SELECT ..

    
answered by 18.04.2018 / 16:00
source