ORACLE-选择对子查询计数

Question

我有一个Oracle表，其中包含一组范围（RangeA和RangeB）。这些列是varchar，因为它们可以同时容纳数字和字母数字值，如以下示例所示：

ID|RangeA|RangeB
1 |   10 |   20
2 |   21 |   30
3 | AB50 | AB70
4 | AB80 | AB90

我需要执行一个查询，该查询仅返回具有数值的记录，并对该查询执行Count。到目前为止，我已经尝试过使用两个不同的查询来做到这一点，但没有任何运气：

查询1：

SELECT COUNT(*) FROM (
SELECT RangeA, RangeB FROM table R
WHERE upper(R.RangeA) = lower(R.RangeA)
) A
WHERE TO_NUMBER(A.RangeA) <= 10

查询2：

WITH A(RangeA,RangeB) AS(
SELECT RangeA, RangeB FROM table 
WHERE upper(RangeA) = lower(RangeA)
)
SELECT COUNT(*) FROM A WHERE TO_NUMBER(A.RangeA) <= 10

子查询工作正常，因为我得到的两个记录都只有数字值，但是查询的COUNT部分失败了。我应该只得到1，但是我得到了以下错误：

ORA-01722: invalid number
01722. 00000 -  "invalid number"

我究竟做错了什么？任何帮助深表感谢。

Answer 1

您可以使用正则表达式测试每一列，以确定其是否为有效数字：

SELECT COUNT(1)
FROM   table_of_ranges
WHERE  CASE WHEN REGEXP_LIKE( RangeA, '^-?\d+(\.\d*)?$' )
            THEN TO_NUMBER( RangeA )
            ELSE NULL END
          < 10
AND    REGEXP_LIKE( RangeB, '^-?\d+(\.\d*)?$' );

另一种选择是使用用户定义的函数：

CREATE OR REPLACE FUNCTION test_Number (
  str VARCHAR2
) RETURN NUMBER DETERMINISTIC
AS
  invalid_number EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_number, -6502);
BEGIN
  RETURN TO_NUMBER( str );
EXCEPTION
  WHEN invalid_number THEN
    RETURN NULL;
END test_Number;
/

然后，您可以执行以下操作：

SELECT COUNT(*)
FROM   table_of_ranges
WHERE  test_number( RangeA ) <= 10
AND    test_number( RangeB ) IS NOT NULL;