子选择问题
我正在尝试在 AS400 上的 SQL 中进行子选择，并收到“数据转换或数据映射错误” - 我很确定它与 SQL 处理子选择中的日期的方式有关（具体来说，它通过以下方式更改格式）在小数字段中添加逗号，当它进行下一个选择时，它会变得混乱）-有人可以为我确认这一点吗？ 也许建议我如何解决这个问题？
基本上，我有类似下面的内容，日期为十进制，格式如下：CCYYMMDD（即，如果您只是选择日期，则它们会显示为 CC、YYM、MDD）。 日期来自表3
SELECT *
FROM TABLE1 A
CROSS JOIN TABLE2 B
LEFT OUTER JOIN (SELECT *
FROM TABLE3 C
LEFT OUTER JOIN TABLE4 D ON (blah)
INNER JOIN TABLE5 E ON (blah)
WHERE DATE >= 20080101
AND DATE <= 20090101
) AS C ON (blah AND blah)
I'm trying to do a subselect in SQL on an AS400 and getting a "Data conversion or data mapping error" - I'm pretty sure its to do with the way SQL is handling dates in the subselect (specifically it's changing the format by adding commas into a decimal field and it's getting confused when it does the next select) - could someone confirm this for me?? maybe suggest how I need to get round this problem??
Basically, I have something like below, with dates as decimal and in this format: CCYYMMDD (ie if you just do a select on the dates they come out as CC,YYM,MDD). The date is coming from table3
SELECT *
FROM TABLE1 A
CROSS JOIN TABLE2 B
LEFT OUTER JOIN (SELECT *
FROM TABLE3 C
LEFT OUTER JOIN TABLE4 D ON (blah)
INNER JOIN TABLE5 E ON (blah)
WHERE DATE >= 20080101
AND DATE <= 20090101
) AS C ON (blah AND blah)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我的 AS/400 经验很少且过时，但您的问题是经典的分而治之。
隔离子查询 - 它本身运行正常吗？
然后从表1开始并确保交叉查询有效
然后添加子查询。
我不知道 AS/400 是否支持它，但是 SQL Server 的公用表表达式非常有用 - 基本上是本地范围的视图。 我之所以提到它，是因为您可以创建一个视图作为子查询，以便更好地理解。
总而言之，我怀疑你的问题出在“废话”中:)
I have little and dated AS/400 experience, but your problem is classic divide and conquer.
Isolate the sub-query - does it run ok by itself?
Then start with table 1 and make sure the cross-query works
Then add in the sub-query.
I don't know if AS/400 supports it, but SQL Server's common table expressions are very helpful - basically locally-scoped views. I only mention it because you could create a view that was your sub-query for better understanding.
All in all, I suspect your problem is within the 'blah and blah' :)
如果您正在使用本机 AS400 db，其风格是： DB2 for iSeries（不要与 DB2 for Linux 和其他平台混淆）
如果是这样，并且表 3 中的 DATE 字段是 CCYYMMDD 格式的十进制数字，正如您所说，您的比较一下就好了。 逗号是应用于小数显示的格式，不与值一起存储。
我同意 n8wrl 的观点，尝试一个简单的“从 Table3 中选择，其中 DATE >= 20080101”，看看它是否运行，然后从那里开始工作。
废话，废话非常敏感:)
If you are working with the native AS400 db its flavor is: DB2 for iSeries (not to be confused with DB2 for Linux and other platforms)
If so, and the DATE fields in table 3 are decimal numeric in CCYYMMDD format as you say, your comparison is just fine. The commas are a format applied to decimals for the display and are not stored with the values.
I agree with n8wrl, try a simple "select from Table3 Where DATE >= 20080101" and see if that runs, and work your way out from there.
blah, blahs are very touchy :)
要正确回答这个问题，了解 AS400 使用什么类型的“SQL”将有所帮助。 AS400 本身只是一台服务器。 AS400 可以与许多数据库风格一起工作，例如 DB2、MS SQL Server、Oracle 等...
要在不知道哪种 SQL 风格的情况下快速尝试，我会说您需要在日期值周围添加 ''，这样它们就不会不被视为数值。
其中日期 >= '20080101'
和日期 <= '20090101'
To answer this question properly it would help to know what flavor of "SQL" the AS400 is working with. The AS400 by its self is just a server. The AS400 can work with many database flavors such as DB2, MS SQL Server, Oracle, etc...
To take a quick stab at this without knowing which SQL flavor I would say you need to put '' around your date values so they don't get treated as numeric values.
WHERE DATE >= '20080101'
AND DATE <= '20090101'