Oracle 子句中的无效数字

本文介绍了Oracle 子句中的无效数字的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在努力让查询工作，我真的可以使用一些帮助.我们有一个内部应用程序，用于构建我工作的小型网络应用程序.它基本上是一个拖放式 GUI.内置了使用键访问查询字符串值的功能.

I'm struggling with getting a query to work, and I could really use some help. We have an in house app that we use for building small web apps where I work. It's basically a drag and drop GUI. There's functionality built in to access query string values using the key.

我通过查询字符串将逗号分隔的值列表传递到页面中.然后，我尝试将值列表用作查询中 in 子句的一部分.

I'm passing a comma separated list of values into a page through the query string. I'm then trying to use the list of values as part of an in clause in a query.

我可以看到查询字符串中的值是正确的.

I can see that the value is correct in the query string.

订单=1,2,3

这里是查询的具体部分

"AND OrderNumber IN(这是从查询字符串映射的位置)

"AND OrderNumber IN (this is where it maps from the query string)

我尝试在 Toad 中运行类似的查询，我想我已经找到了问题所在.它给出了无效数字错误，我认为它将查询字符串值包装在单引号中.当我在 Toad 中执行AND OrderNumber IN ('1,2,3')"时，我可以复制错误.

I've tried running similar queries in Toad, and I think I've found the issue. It's giving an invalid number error, and I think it's wrapping the query string value in single quotes. I can replicate the error when I do "AND OrderNumber IN ('1,2,3')" in Toad.

这就是我真正感到困惑的地方.以下在 Toad 中有效."AND OrderNumber IN ('1','2','3')"

Here's where I get really confused. The following works in Toad."AND OrderNumber IN ('1','2','3')"

所以我尝试通过这样做来重新创建它select replace('1,2,3', ',', chr(39)||','||chr(39)) from dual;

So I tried recreating that by doingselect replace('1,2,3', ',', chr(39)||','||chr(39)) from dual;

我已确认在 Toad 中返回1"、2"、3".但是，当我在 Toad 中运行以下命令时，我仍然收到无效数字错误.

I have confirmed that returns '1','2','3' in Toad.However, I still get an Invalid Number error when I run the following in Toad.

AND OrderNumber IN (replace('1,2,3', ',', chr(39)||','||chr(39))

我一直在绞尽脑汁，但我想不通.在我看来，如果AND OrderNumber IN ('1','2','3')"有效，并且 replace('1,2,3', ',', chr(39)||','||chr(39)) 返回 '1','2','3'，即 "AND OrderNumber IN (replace('1,2,3', ',', chr(39)||','||chr(39))" 应该可以工作.

I've been racking my brain over this, and I can't figure it out. It seems to me that if "AND OrderNumber IN ('1','2','3')" works, and replace('1,2,3', ',', chr(39)||','||chr(39)) returns '1','2','3', that "AND OrderNumber IN (replace('1,2,3', ',', chr(39)||','||chr(39))" should work.

如果您能就此提供任何帮助，我们将不胜感激.我知道查询的其余部分有效.这就是为什么我没有发布它.我一直在努力让这个 IN 子句发挥作用.

Any help you might be able to offer on this would be greatly appreciated. I know the rest of the query works. That's why I didn't post it. I'm just stuck on trying to get this IN clause working.

推荐答案

你可以试试下面的查询吗.

Can you try the following query.

SELECT * FROM orders
WHERE orderno IN
    (
    SELECT TRIM(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) str
    FROM ( SELECT '1,2,3,4' str FROM dual )
    CONNECT BY INSTR(str, ',', 1, LEVEL - 1) > 0
    )

将字符串拆分为不同行的内联查询.因此，在执行它时，您将得到以下结果.

The inline query splitting the string in different rows. So, on executing it you will get the following result.

SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
    FROM ( SELECT '1,2,3,4' str FROM dual )
    CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0

1
2
3
4

现在，将此结果传递给主查询 IN 子句应该可以工作了.

Now, passing this result to the main query IN clause should work.

这篇关于Oracle 子句中的无效数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！