#oracle #stored-procedures #oracle11g #group-by
#Oracle #хранимые процедуры #oracle11g #группировка по
Вопрос:
Привет, я новичок в базе данных oracle.В приведенном ниже запросе ZERO_BAL_CODE varchar2 указан тип данных. Я попытался написать оператор case, но он выдает ошибку с указанием этого "ORA-01722: invalid number" . Помогите мне решить эту проблему. Заранее спасибо.
SELECT id_loan,
MAX (TO_NUMBER (delinq_status)) AS delinq_status,
MAX (loan_age) AS loan_age,
MAX (zero_bal_code) AS zero_bal_code,
MAX (vintage) AS vintage,
MIN (actual_loss) AS actual_loss,
MIN (NULLIF (current_upb, 0)) AS current_upb,
loan_type
FROM (SELECT master_copy.id_loan,
loan_age,
master_copy.vintage,
delinq_status,
zero_bal_code,
master_copy.actual_loss,
current_upb,
CASE
WHEN (zero_bal_code IN (3)) THEN 'default_foreclosure'
WHEN (zero_bal_code IN (1)) THEN 'prepaid'
WHEN (zero_bal_code IN (6)) THEN 'default_reo'
WHEN (zero_bal_code IN (9)) THEN 'active'
END
AS loan_type
FROM master_copy
INNER JOIN (SELECT id_loan FROM master_copy) a
ON master_copy.id_loan = a.id_loan) b
GROUP BY id_loan, loan_type;
Комментарии:
1. Попробуйте добавить кавычки и проверить пример:(‘1’)
2. Итак, общий ответ таков: это то, что происходит, когда у нас есть модель данных, которая хранит числовые значения в строковых столбцах. Независимо от того, насколько пуленепробиваемой мы можем считать проверку приложения, уроки истории заключаются в том, что наши данные неизбежно будут повреждены.
3. @Raghavi — это почти всегда проблема с данными . Есть некоторые записи, которые имеют нечисловые значения в столбцах, где вы ожидаете, что они будут иметь только числовые значения. Если вы оспариваете это, пожалуйста, опубликуйте структуру таблицы и некоторые примеры записей, которые создают эту проблему.
Ответ №1:
Вы не опубликовали описание таблицы, поэтому — для меня — это выглядит подозрительно:
MAX (TO_NUMBER (delinq_status)) AS delinq_status,
Если DELINQ_STATUS бы тип данных столбца был NUMBER , вам бы это не TO_NUMBER понадобилось. Если это так VARCHAR2 , то кто-то, возможно, ввел в него ‘ABC1234’, что привело бы TO_NUMBER к сбою функции.
Итак, если вы запустите это, каков результат?
select max(to_number(delinq_status)) from master_copy
По состоянию на отсутствие одинарных кавычек в CASE : id зависит от содержимого столбца. Если это «число», одинарные кавычки не нужны, хотя тип данных столбца есть VARCHAR2 (но это было бы хорошей практикой; с другой стороны, если столбец содержит только числа, почему не его тип NUMBER данных ?):
SQL> create table test (zero_bal_code varchar2(1));
Table created.
SQL> insert into test values ('1');
1 row created.
SQL> select case when zero_bal_code in (3) then 'default_foreclosure'
2 else 'unknown'
3 end loan_type
4 from test;
LOAN_TYPE
-------------------
unknown
Однако, если есть что-то отличное от «чисел», тогда имеют значение одинарные кавычки:
SQL> insert into test values ('B');
1 row created.
SQL> select case when zero_bal_code in (3) then 'default_foreclosure'
2 else 'unknown'
3 end loan_type
4 from test;
ERROR:
ORA-01722: invalid number
no rows selected
SQL> select case when zero_bal_code in ('3') then 'default_foreclosure'
2 else 'unknown'
3 end loan_type
4 from test;
LOAN_TYPE
-------------------
unknown
unknown
SQL>
Кстати, то, что вы написали, не имеет особого смысла; действительно ли коды для prepault_reo, default_reo и active 1 ? Все они? Как вы будете различать, что есть что?
CASE
WHEN (zero_bal_code IN (3)) THEN 'default_foreclosure'
WHEN (zero_bal_code IN (1)) THEN 'prepaid'
WHEN (zero_bal_code IN (1)) THEN 'default_reo'
WHEN (zero_bal_code IN (1)) THEN 'active'
END
Комментарии:
1. Извините.. значение default_reo равно 6, а значение active равно 9. выбор max(to_number(delinq_status)) из master_copy возвращает недопустимое число.
2. Это означает, что сначала вы должны исправить значения столбцов DELINQ_STATUS. Он не должен содержать ничего, кроме чисел (если вы хотите применить к нему TO_NUMBER).
3. ВЫБЕРИТЕ ID_LOAN,max(to_number(DELINQ_STATUS)) как DELINQ_STATUS,max(loan_age) КАК LOAN_AGE,max(ZERO_BAL_CODE) как ZERO_BAL_CODE,max(vintage) как vintage, min(ACTUAL_LOSS) как actual_loss,MIN(NULLIF(current_upb,0)) как current_upb,’prepaid’ как loan_type ИЗ (ВЫБЕРИТЕ m.ID_LOAN,LOAN_AGE, m.vintage,DELINQ_STATUS,ZERO_BAL_CODE,m.ACTUAL_LOSS,current_upb ИЗ master_copy m INNER JOIN (ВЫБЕРИТЕ ID_LOAN ИЗ master_copy, ГДЕ ZERO_BAL_CODE В (1))A НА m.ID_LOAN= A.ID_LOAN) B группа по ID_LOAN)); но это работает нормально. здесь я пытаюсь объединить в качестве оператора case, который выдает ошибку.
4. Сколько строк вернул этот запрос? Если вы используете какой-либо графический интерфейс, они обычно возвращают пару сотен строк — не все из них. Итак, если в этом наборе данных нет ошибки, запрос будет выполнен «успешно» (скрыв ошибку). Если вы перейдете к концу строк, вы, вероятно, попадете в него. В любом случае: DELINQ_STATUS не должен содержать ничего, кроме чисел. Заключите значения ZERO_BAL_CODE в одинарные кавычки.
5. Он также пробовал использовать одинарные кавычки … все та же ошибка.
Ответ №2:
Попробуйте это с помощью ‘:
SELECT id_loan,
MAX (TO_NUMBER (delinq_status)) AS delinq_status,
MAX (loan_age) AS loan_age,
MAX (zero_bal_code) AS zero_bal_code,
MAX (vintage) AS vintage,
MIN (actual_loss) AS actual_loss,
MIN (NULLIF (current_upb, 0)) AS current_upb,
loan_type
FROM (SELECT master_copy.id_loan,
loan_age,
master_copy.vintage,
delinq_status,
zero_bal_code,
master_copy.actual_loss,
current_upb,
CASE
WHEN (zero_bal_code IN ('3')) THEN 'default_foreclosure'
WHEN (zero_bal_code IN ('1')) THEN 'prepaid'
WHEN (zero_bal_code IN ('1')) THEN 'default_reo'
WHEN (zero_bal_code IN ('1')) THEN 'active'
END
AS loan_type
FROM master_copy
INNER JOIN (SELECT id_loan FROM master_copy) a
ON master_copy.id_loan = a.id_loan) b
GROUP BY id_loan, loan_type;