Сравнение чисел со столбцами varchar в запросе объединения

#sql #oracle

#sql #Oracle

Вопрос:

У меня есть таблица, скажем, table_Test, которая содержит некоторые тестовые значения, и в ней есть столбец с типом varchar с именем say Value. Некоторые примерные значения в столбце value похожи ‘5.5’, ‘>7.2’, ‘>3.6’ и т.д. Единственными возможными нечисловыми символами в значениях являются ‘<‘ и ‘>’.

Теперь требуется извлекать данные из этой таблицы со значением, превышающим, скажем, 5. Я попытался преобразовать значение в числовое перед сравнением с приведенным ниже запросом:

 SELECT *
FROM table_test
WHERE to_number(TRIM(translate(value,'<>',' ') ) ) >= 5

Этот запрос работает абсолютно нормально. Однако, когда я выполняю самосоединение для включения других условий в мое требование, приведенный ниже запрос завершается ошибкой.

 SELECT res1.client_id,
       res1.value,
       res1.test_date
FROM table_test res1,
     table_test res2
WHERE res1.client_id = res2.client_id 
AND res1.lab_sample_source_id = 1 
AND res2.lab_sample_source_id = 2 AND res2.lab_method_id = 52 
AND to_number(TRIM(translate(res1.value,'<>',' ') ) ) >= 5 
AND to_number(TRIM(translate(res1.value,'<>',' ') ) ) < 10

Запрос завершается ошибкой:

ORA-01722: недопустимый номер 01722. 00000 — «недопустимый номер» * Причина: указанный номер был недопустимым. * Действие: Укажите допустимое число.

1. ИМО, вам следует прокомментировать and to_number(...) <10 условие и снова выполнить свой запрос. Это должно снова вызвать ту же ошибку.

2. Это означает, что в некоторых строках есть другие символы, кроме >< . Первый запрос работает только для первых нескольких строк, которые отображаются на экране.

3. Пожалуйста, предоставьте примеры данных и желаемые результаты. Мне не ясно, что вы хотите вернуть.

4. @Kaushik Там нет других символов, кроме >< . иначе первый запрос не сработал бы правильно?

5. @Gordon, я предоставил примеры значений для столбца value. Первый запрос работает, однако то же условие при самостоятельном соединении не работает.

Ответ №1:

Здесь помогло бы регулярное выражение. Используйте для этого функцию REGEXP_REPLACE.

 create table table_test
(
    value varchar2(20)
);

insert into table_test values ('>7.2');
insert into table_test values ('5.5');
insert into table_test values ('>3.6');

commit;


select
value,
regexp_replace(value,'[0-9.]') as characters,
regexp_replace(value,'[^0-9.]') as numbers
from table_test
where to_number(regexp_replace(value,'[^0-9.]')) >= 5;

ДЕМОНСТРАЦИЯ

 | VALUE | CHARACTERS | NUMBERS |
|-------|------------|---------|
|  >7.2 |          > |     7.2 |
|   5.5 |     (null) |     5.5 |

1. будет ли это работать для самостоятельного объединения? для одного запроса мой первый запрос работает.

2. Пока вы соответствуете выражению типов данных (например, число X = число Y), все должно быть в порядке. Всегда указывайте типы данных явно. Поэтому используйте to_number , to_char или to_date вместо неявного преобразования типов данных.