ORA-01789:查询块的结果列数不正确
ORA-01789:查询块的结果列数不正确
提问于 2020-09-10 06:18:11
我正试图解决这个问题，但不知怎么我做不到。我犯了错误。
ORA-01789: query block has incorrect number of result columns据我所见，问题是工会。
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不知何故，我试图将UNION更改为UNION ALL，但仍然存在相同的问题。谁能指引我，告诉我这里出了什么问题？我在哪里犯了错？
回答 1
Database Administration用户
回答已采纳
发布于 2020-09-10 11:13:57
这是错误的：
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>应该是
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>请注意，它们不仅必须在数字上匹配，而且在数据类型上也必须匹配：
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>现在你知道该看什么了，继续看吧。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/275272
复制相关文章
相似问题
腾讯云开发者
