ORACLE-选择对子查询计数 [英] ORACLE - Select Count on a Subquery
问题描述
我有一个Oracle表,该表包含一组范围(RangeA和RangeB).这些列是varchar,因为它们可以同时容纳数字和字母数字值,例如以下示例:
I've got an Oracle table that holds a set of ranges (RangeA and RangeB). These columns are varchar as they can hold both numeric and alphanumeric values, like the following example:
ID|RangeA|RangeB
1 | 10 | 20
2 | 21 | 30
3 | AB50 | AB70
4 | AB80 | AB90
我需要执行一个查询,该查询仅返回具有数字值的记录,并对该查询执行Count.到目前为止,我已经尝试过使用两个不同的查询来做到这一点,但是没有任何运气:
I need to to do a query that returns only the records that have numeric values, and perform a Count on that query. So far I've tried doing this with two different queries without any luck:
查询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,但是我得到了以下错误:
The subquery is working fine as I'm getting the two records that have only numeric values, but the COUNT part of the query is failing. I should be getting only 1 on the count, but instead I'm getting the following error:
ORA-01722: invalid number
01722. 00000 - "invalid number"
我做错了什么?非常感谢您的帮助.
What am I doing wrong? Any help is much appreciated.
推荐答案
您可以使用正则表达式测试每一列,以确定其是否为有效数字:
You can test each column with a regular expression to determine if it is a valid number:
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*)?$' );
另一种替代方法是使用用户定义的函数:
Another alternative is to use a user-defined function:
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;
/
然后您可以执行以下操作:
Then you can do:
SELECT COUNT(*)
FROM table_of_ranges
WHERE test_number( RangeA ) <= 10
AND test_number( RangeB ) IS NOT NULL;
这篇关于ORACLE-选择对子查询计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!