ORA-01789: query block has incorrect number of result columns

Question

I am trying to solve this problem but somehow I couldn't. I get error

ORA-01789: query block has incorrect number of result columns

As I see the problem is UNION.

  SELECT * FROM(
          SELECT
               ia.ATAID, 
               ia.ataComment,
               ia.Type,
               ia.AtaNumber, 
               ia.DeviationNumber, 
               ia.ProjectID, 
               ia.Name, 
               ia.StartDate, 
               ia.DueDate, 
               ia.RevisionDate, 
               ia.Status, 
               ia.Ata, 
               ia.Deviation, 
               ia.Locked, 
               ia.PaymentType, 
               ia.BecomeExternalAtaFromInternal, 
               ia.Author, 
               ia.AtaType, 
               ia.DeviationType, 
               ia.PDFUrl, 
               ia.street, 
               ia.city, 
               ia.zip, 
               ia.briefDescription, 
               ia.ConfirmStatus, 
               ia.Token, 
               ia.ParentAta, 
               ia.EmailSent, 
               ia.ClientComment,
               ia.AnswerEmail, 
               ia.AnswerTime, 
               ia.UserID, 
               ia.FromFortnox,
               ia.ForFortnox, 
               ia.client_answer_attachment, 
               debitform.Name as paymentTypeName,
               CONCAT(CONCAT(users.Surname, ' '),users.Lastname) AS AuthorName,
               ia.Description, 
               ia.Reason, 
               ia.Suggestion, 
               ia.State, 
               'ata_become_external' as AtaTable,
               ia.WeeklyReports, 
               ia.Documents AS Attachment, 
               ia.articlesAdditionalWork AS articlesAdditionalWorkFromia, 
               ia.enabledAccounts, 
               ia.DefaultWeeklyReports, 
               ia.articlesMaterial AS articlesMaterialFromia, 
               ia.articlesOther AS articlesOtherFromia, 
               ia.WeeklyReportsMomentsPerWeek,
                NULL AS becomeFastFromOtherType
            FROM 
               internal_atas  ia
            LEFT JOIN
                users
            ON
                ia.Author = users.UserID
            LEFT JOIN
                 debitform
             ON
                 debitform.Id = ia.PaymentType
             WHERE 
                  ia.ATAID = 1189 
                        OR 
                            ia.ParentAta = 0

                    UNION 
                    
                       SELECT a.ATAID, 
                              a.ataComment, 
                              a.Type, 
                              a.AtaNumber, 
                              a.DeviationNumber, 
                              a.ProjectID, 
                              a.Name, 
                              a.StartDate, 
                              a.DueDate, 
                              a.RevisionDate, 
                              a.Status, 
                              a.Ata, 
                              a.Deviation, 
                              a.Locked, 
                              a.PaymentType, 
                              a.BecomeExternalAtaFromInternal, 
                              a.Author, 
                              a.AtaType, 
                              a.DeviationType, 
                              a.PDFUrl, 
                              a.street, 
                              a.city, 
                              a.zip, 
                              a.briefDescription, 
                              a.ConfirmStatus, 
                              a.Token, 
                              a.ParentAta,
                              a.EmailSent, 
                              a.ClientComment,
                              a.AnswerEmail, 
                              a.AnswerTime, 
                              a.UserID, 
                              a.FromFortnox, 
                              a.ForFortnox, 
                              a.client_answer_attachment,
                              debitform.Name as paymentTypeName, 
                              CONCAT(CONCAT(users.Surname, ' '),users.Lastname) AS AuthorName,
                              NULL AS Description, 
                              NULL AS Reason, 
                              NULL AS Suggestion,
                              NULL AS State, 
                              'ata' as AtaTable , 
                              NULL AS WeeklyReports, 
                              NULL AS Attachment,
                              NULL AS articlesAdditionalWorkFromia, 
                              NULL AS enabledAccounts, 
                              NULL AS DefaultWeeklyReports, 
                              NULL AS articlesMaterialFromia, 
                              NULL AS articlesOtherFromia, 
                              NULL AS WeeklyReportsMomentsPerWeek, 
                              a.becomeFastFromOtherType
                        FROM 
                            ata  a
                        LEFT JOIN
                            users
                        ON
                            a.Author = users.UserID
                        LEFT JOIN
                            debitform
                        ON
                            debitform.Id = a.PaymentType
                        WHERE 
                            a.ATAID = 1889
                        OR 
                            a.ParentAta = 0
                        )  a
                    ORDER BY a.ATAID

Somehow I try to change UNION to UNION ALL but still have same problem. Anyone how can guide me and tell me what is wrong here ? Where I made mistake ?

Answer 1

This is wrong:

SQL> select deptno           --> only 1 column here
  2  from dept
  3  union
  4  select empno, ename     --> 2 columns here
  5  from emp;
select deptno
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns


SQL>

Should be

SQL> select deptno, dname         --> 2 columns here
  2  from dept
  3  union
  4  select empno , ename         --> 2 columns here as well
  5  from emp;

    DEPTNO DNAME
---------- --------------------
        10 ACCOUNTING
        20 RESEARCH
<snip>

---

Note that they must match not only in number, but also in datatype:

SQL> select deptno, dname
  2  from dept
  3  union
  4  select empno , hiredate
  5  from emp;
select deptno, dname
               *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


SQL>

---

Now that you know what to look at, go on and look at it.