英文:
DB2 SQL: How do i add a sub select statement to the where clause?
问题
以下是翻译好的内容：
所以，我有一个如下的查询：
SELECT a.abc, b.xyz, c.yup
from coin a, true b, yes c
where a.id = b.id
and b.id = c.id
and a.access_code in
(select ax.acess_code from coin ax, Parameter b
where ax.flow_id = b.flow_id
and b.start_date = '2022-06-21'
and b.result = 'B')
然而，我遇到了这个错误：
*SQL错误[42601]：找到了一个意外的标记""在""之后。 预期的标记可能包括："
WITH ur".. SQLCODE=-104，SQLSTATE=42601，DRIVER=4.21.29*
有什么想法吗？
我试图将这两个查询关联在一起，但没有成功。请注意，表'coin'在主查询和子查询中是相同的表。
英文:
So I have a query as follows :
SELECT a.abc, b.xyz, c.yup
from coin a, true b, yes c
where a.id = b.id
and b.id = c.id
and a.access_code in
(select ax.acess_code from coin ax, Parameter b
where ax.flow_id = b.flow_id
and b.start_date = '2022-06-21'
and b.result = 'B')
However, I get this error:
*SQL Error [42601]: An unexpected token "" was found following "". Expected tokens may include: "
WITH ur".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.21.29*
Any ideas?
I am trying to link the two queries together but have been unsuccessful. Note that the table 'coin' is the same table in the main query as in the sub query.
答案1
得分: 0
你应该使用不同的别名，并开始使用JOIN。另外，你也可以使用INNER JOIN替代IN子句。
SELECT a.abc, b.xyz, c.yup
FROM coin a
INNER JOIN true b ON a.id = b.id
INNER JOIN yes c ON b.id = c.id
INNER JOIN
(SELECT ax.access_code
FROM coin ax
INNER JOIN Parameter bx
ON ax.flow_id = bx.flow_id
WHERE bx.start_date = '2022-06-21'
AND bx.result = 'B') d ON a.access_code = d.access_code
英文:
You should use different aliases and start using JOIN
Also you can use also an INNER JOIN instead of an IN clause
SELECT a.abc, b.xyz, c.yup
from coin a
INNER JOIN true b ON a.id = b.id
INNER JOIN yes c ON b.id = c.id
INNER JOIN
(select ax.acess_code
from coin ax
INNEr JOIN Parameter bx
ON ax.flow_id = bx.flow_id
WHERE bx.start_date = '2022-06-21'
and bx.result = 'B') d ON a.access_code = d.acess_code
答案2
得分: 0
If b.start_date is of type date, try and b.start_date = date('2022-06-21')
英文:
If b.start_date is of type date, try
and b.start_date = date('2022-06-21')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论