У меня есть этот оператор case when
как подзапрос где-то в запросе, который мне нужно исправить, и я признаю, что понятия не имею, что он делает, но он дает мне missing right parentheses
, может ли кто-нибудь помочь мне исправить синтаксис?
(SELECT *
FROM (case when length(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 2 THEN
(SELECT NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
when LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 3 THEN
(SELECT NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 2, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
when LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 4 THEN
(SELECT NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 3, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 2, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
when LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 5 THEN
(SELECT NULL,
NULL,
NULL,
NULL,
NULL,
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 4, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 3, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 2, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
when LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 6 THEN
(SELECT
NULL,
NULL,
NULL,
NULL,
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 5, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 4, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 3, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 2, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
when LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 7 THEN
(SELECT NULL,
NULL,
NULL,
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 6, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 5, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 4, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 3, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 2, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
when LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 8 THEN
(SELECT NULL,
NULL,
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 7, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 6, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 5, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 4, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 3, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 2, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
when LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 9 THEN
(SELECT NULL,
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 8, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 7, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 6, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 5, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 4, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 3, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 2, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
when LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) = 10 THEN
(SELECT SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 9, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 8, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 7, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 6, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 5, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 4, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 3, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 2, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER) - 1, 1),
SUBSTR(TO_CHAR(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), LENGTH(nationalNumber.NATIONAL_IDENTIFIER_NUMBER), 1))
ELSE
(SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
END) NID1
Вы написали весь этот код, не попробовав его ни разу в процессе? Делайте гораздо меньшие шаги и проверяйте каждый шаг, прежде чем переходить к следующему.
я ничего не писал, это была функция pl/sql и мне нужно заставить ее работать без pl/sql
У вас как минимум 2 проблемы:
SELECT
без предложения FROM
.CASE WHEN ... (SELECT multiple, columns ...)
, так как выражение CASE
должно возвращать одно значение.Вероятно, вам не нужно выражение CASE
, и вы можете использовать:
SELECT SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -10, 1) AS chr1,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -9, 1) AS chr2,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -8, 1) AS chr3,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -7, 1) AS chr4,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -6, 1) AS chr5,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -5, 1) AS chr6,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -4, 1) AS chr7,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -3, 1) AS chr8,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -2, 1) AS chr9,
SUBSTR(NATIONAL_IDENTIFIER_NUMBER, -1, 1) AS chr10
FROM table_name
WHERE LENGTH(NATIONAL_IDENTIFIER_NUMBER) BETWEEN 2 AND 10
Что для примера данных:
CREATE TABLE table_name (NATIONAL_IDENTIFIER_NUMBER) AS
SELECT 'a' FROM DUAL UNION ALL
SELECT 'ab' FROM DUAL UNION ALL
SELECT 'abc' FROM DUAL UNION ALL
SELECT 'abcd' FROM DUAL UNION ALL
SELECT 'abcde' FROM DUAL UNION ALL
SELECT 'abcdef' FROM DUAL UNION ALL
SELECT 'abcdefg' FROM DUAL UNION ALL
SELECT 'abcdefgh' FROM DUAL UNION ALL
SELECT 'abcdefghi' FROM DUAL UNION ALL
SELECT 'abcdefghij' FROM DUAL UNION ALL
SELECT 'abcdefghijk' FROM DUAL UNION ALL
SELECT 'abcdefghijki' FROM DUAL
Выходы:
это выбирает каждый случай case when
? я вижу гораздо меньше кода, это в основном функции внутри пакетов, и мне нужно, чтобы они работали без pl/sql
, и после того, как я посмотрел на функцию и требования, они хотят выбрать каждый случай внутри функции pl/sql
в упомянутом case when
как отдельный колонка, ваше решение делает именно это?
@FarisAdnan Даже он отвечает, вы будете безоговорочно доверять ему или все равно проверите? (Короче говоря, вы можете сами ответить на свой вопрос, просто проведя тестирование, которое должны делать всегда.)
я не могу ничего проверить, в том-то и проблема, что нет данных
@FarisAdnan Конечно, вы можете протестировать это, вы создаете образцы данных, которые хотите протестировать (точно так же, как я сделал).
Пожалуйста, изучите/отладьте свой запрос в достаточной степени, чтобы вы могли изолировать фрагмент кода проблемы и сделать минимальный воспроизводимый пример (акцент на минимальный)