Блог

Главная - Вопросы по программированию - Запрос работает в SQL Server, пытаясь реплицироваться в Oracle

Запрос работает в SQL Server, пытаясь реплицироваться в Oracle

#sql #oracle #case #wildcard

Вопрос:

У меня есть запрос, который работает так, как задумано в SQL Server, но когда я пытаюсь воспроизвести его в Oracle, он не работает.

  • В SQL Server он возвращается Column1 с 5 символами.
  • В Oracle он возвращает исходные значения.

Синтаксис Oracle не является моей сильной стороной, как я могу заставить это работать, любые предложения будут оценены

 SELECT
    CASE
        WHEN Column1 LIKE '__-____' 
            THEN CONCAT('000', (SUBSTR(LTRIM(RTRIM(Column1)), 1, 2)))
        WHEN Column1 LIKE '___-____' 
            THEN CONCAT('00', (SUBSTR(LTRIM(RTRIM(Column1)), 1, 3)))
        WHEN Column1 LIKE '_______-_____' 
            THEN (SUBSTR(LTRIM(RTRIM(Column1)), 3, 5))
        ELSE Column1 
    END AS NewColumn
FROM 
    schema.table1

Вот он снова с CTE, чтобы вы могли видеть, что он работает так, как задумано в SQL Server:

 WITH cte_test AS 
(
    SELECT '12-3456' AS Column1
    UNION ALL
    SELECT '78-9101'
    UNION ALL
    SELECT '1234567-89101'
    UNION ALL
    SELECT '123-4321'
)
SELECT 
    CASE
        WHEN Column1 LIKE '__-____' 
            THEN CONCAT('000', (SUBSTRING(LTRIM(RTRIM(Column1)), 1, 2)))
        WHEN Column1 LIKE '___-____' 
            THEN CONCAT('00', (SUBSTRING(LTRIM(RTRIM(Column1)), 1, 3)))
        WHEN Column1 LIKE '_______-_____' 
            THEN (SUBSTRING(LTRIM(RTRIM(Column1)), 3, 5))
        ELSE Column1
    END AS NewColumn
FROM 
    cte_test

Комментарии:

1. Примеры данных, желаемые результаты и четкое объяснение того, что должен делать запрос, помогут. Что такое тип данных column1 ?

2. Если я использую CTE из вашего теста SQL Server и вашего Oracle select , он, похоже, работает для меня-вот моя скрипка, показывающая, что он делает то, что вы, похоже, хотите dbfiddle.uk/… У Oracle есть trim функция, поэтому в данном случае это не имеет смысла делать ltrim(rtrim()) , но это не вызывает проблем

3. Вы уверены, что данные в фактической таблице-это то, что вы думаете? Если при переносе данных возникла какая-то проблема и в них есть начальные/ конечные пробелы или управляющие символы Column1 , это может объяснить, почему like выражение не подходит для вас.

4. @GordonLinoff Я не могу предоставить примерные данные, помимо тестовых данных в CTE. КОНКАТ дополняет левую сторону цифрами 0, чтобы достичь 5 символов. Тип данных столбца 1-CHAR (20) Все это объясняется в исходном сообщении В SQL Server, оно возвращает столбец 1 с 5 символами. — (предназначенная функция) В Oracle он возвращает исходные значения. — (не предназначенная функция)

5. Ты смотришь на скрипку, которую я выложил? Возвращает ли скрипка нужные вам результаты? Вы не обрезаете результат перед выполнением like , поэтому, если есть начальные или конечные пробелы (в версии Oracle или SQL Server), like шаблоны не будут совпадать, и вы просто вернете Column1 значение.

Ответ №1:

Проблема в том, что вы объявили столбец в таблице Oracle как char(20) . char(20) это тип данных фиксированной ширины, поэтому он всегда будет заполнен пробелом до 20 символов. Учитывая ваши примерные данные, это отнимает место и означает, что ни одно из ваших like предложений не будет соответствовать данным образца из-за пробелов в конце. Вы действительно хотите объявить столбец в таблице varchar2(20) таким, чтобы он не был пустым.

Если вы просто возьмете CTE из примера SQL Server и используете его с кодом Oracle, запрос вернет то, что вы хотите, потому что Oracle рассматривает столбец в CTE как varchar2 

 WITH cte_test AS 
(
    SELECT '12-3456' AS Column1 from dual
    UNION ALL
    SELECT '78-9101' from dual
    UNION ALL
    SELECT '1234567-89101' from dual
    UNION ALL
    SELECT '123-4321' from dual
)
SELECT
    CASE
        WHEN Column1 LIKE '__-____' 
            THEN CONCAT('000', (SUBSTR(LTRIM(RTRIM(Column1)), 1, 2)))
        WHEN Column1 LIKE '___-____' 
            THEN CONCAT('00', (SUBSTR(LTRIM(RTRIM(Column1)), 1, 3)))
        WHEN Column1 LIKE '_______-_____' 
            THEN (SUBSTR(LTRIM(RTRIM(Column1)), 3, 5))
        ELSE Column1 
    END AS NewColumn
FROM cte_test

Если вы создадите таблицу как a char(20) и вставите данные, в ней появится пробел, поэтому like операторы не будут делать то, что вы хотите

 create table char_test( column1 char(20) );

insert into char_test
WITH cte_test AS 
(
    SELECT '12-3456' AS Column1 from dual
    UNION ALL
    SELECT '78-9101' from dual
    UNION ALL
    SELECT '1234567-89101' from dual
    UNION ALL
    SELECT '123-4321' from dual
    )
    select * from cte_test;

SELECT
    CASE
        WHEN Column1 LIKE '__-____' 
            THEN CONCAT('000', (SUBSTR(LTRIM(RTRIM(Column1)), 1, 2)))
        WHEN Column1 LIKE '___-____' 
            THEN CONCAT('00', (SUBSTR(LTRIM(RTRIM(Column1)), 1, 3)))
        WHEN Column1 LIKE '_______-_____' 
            THEN (SUBSTR(LTRIM(RTRIM(Column1)), 3, 5))
        ELSE Column1 
    END AS NewColumn
FROM char_test

Вы можете обойти это, обрезав Column1 перед выполнением like 

 SELECT
    CASE
        WHEN trim(Column1) LIKE '__-____' 
            THEN CONCAT('000', (SUBSTR(Column1, 1, 2)))
        WHEN trim(Column1) LIKE '___-____' 
            THEN CONCAT('00', (SUBSTR(Column1, 1, 3)))
        WHEN trim(Column1) LIKE '_______-_____' 
            THEN (SUBSTR(Column1, 3, 5))
        ELSE Column1 
    END AS NewColumn
FROM char_test

Но лучшим решением было бы объявить столбец как varchar2 в первую очередь, чтобы у вас не было пробелов для обхода

 create table varchar_test( column1 varchar2(20) );

insert into varchar_test
WITH cte_test AS 
(
    SELECT '12-3456' AS Column1 from dual
    UNION ALL
    SELECT '78-9101' from dual
    UNION ALL
    SELECT '1234567-89101' from dual
    UNION ALL
    SELECT '123-4321' from dual
    )
    select * from cte_test;

SELECT
    CASE
        WHEN Column1 LIKE '__-____' 
            THEN CONCAT('000', SUBSTR(Column1, 1, 2))
        WHEN Column1 LIKE '___-____' 
            THEN CONCAT('00', SUBSTR(Column1, 1, 3))
        WHEN Column1 LIKE '_______-_____' 
            THEN (SUBSTR(Column1, 3, 5))
        ELSE Column1 
    END AS NewColumn
FROM varchar_test

Вот скрипка, которая показывает различные варианты.

Комментарии:

1. Спасибо, Джастин, из-за внутренней политики у меня нет доступа к базе данных Oracle, поэтому я попрошу своего POC посмотреть, решит ли это проблему, и свяжусь с вами.

2. Я поддержал ваши решения, так как они работали на меня, спасибо! У меня еще нет 15 баллов, так что это не отражено, но я очень ценю вашу помощь.

Ответ №2:

Альтернатива Oracle, которая (в temp CTE) извлекает первую часть строки (до знака минус), а затем — в зависимости от ее длины — заполняет ее нулями длиной до 5 символов или берет последние 5 символов):

 SQL> WITH cte_test AS
  2  (
  3      SELECT '12-3456' AS Column1 from dual
  4      UNION ALL
  5      SELECT '78-9101' from dual
  6      UNION ALL
  7      SELECT '1234567-89101' from dual
  8      UNION ALL
  9      SELECT '123-4321' from dual
 10  ),
 11  temp as
 12    (select column1,
 13            substr(column1, 1, instr(column1, '-') - 1) val
 14     from cte_Test
 15    )
 16  select column1,
 17    lpad(case when length(val) < 5 then val
 18              else substr(val, -5)
 19         end, 5, '0'
 20        ) as result
 21  from temp;

COLUMN1       RESULT
------------- --------------------
12-3456       00012
78-9101       00078
1234567-89101 34567
123-4321      00123

SQL>

Комментарии:

1. Не могу использовать регулярное выражение, у вас есть решение без него?

2. Конечно, просто измените строку #13 на substr(column1, 1, instr(column1, '-') - 1) val

Ответ №3:

Вы хотите что-то вроде:

 SELECT CASE
       WHEN Column1 LIKE '__-____             '
       THEN '000' || SUBSTR(Column1, 1, 2)
       WHEN Column1 LIKE '___-____            '
       THEN '00' || SUBSTR(Column1, 1, 3)
       WHEN Column1 LIKE '_______-_____       '
       THEN SUBSTR(Column1, 3, 5)
       ELSE Column1
       END AS NewColumn
FROM   schema.table1

или:

 SELECT CASE
       WHEN REGEXP_LIKE( Column1, '^d{2}-d{4}s*

Или, проще говоря:


 SELECT SUBSTR(
         '000' || SUBSTR(column1, 1, INSTR(column1, '-') - 1),
         -5
       ) AS newcolumn
FROM   schema.table1
 


Который, для выборочных данных:


 CREATE TABLE schema.table1( column1 CHAR(20) );

INSERT INTO schema.table1(column1)
SELECT '12-3456' FROM DUAL UNION ALL
SELECT '78-9101' FROM DUAL UNION ALL
SELECT '1234567-89101' FROM DUAL UNION ALL
SELECT '123-4321' FROM DUAL;
 


Все выходные данные:










НОВАЯ КОЛОНКА






00012




00078




34567




00123










бд<>скрипка <>здесь

Комментарии:

1. Не могу использовать регулярное выражение, у вас есть решение без него?

2. @NicholasGregory Обновил несколько других решений.

3. Спасибо! У работы сложная система, я могу запрашивать только сторону MSSQL, а не Oracle, поэтому я попрошу своего POC попробовать это завтра :)

)
THEN '000' || SUBSTR(Column1, 1, 2)
WHEN REGEXP_LIKE( Column1, '^d{3}-d{4}s*Или, проще говоря:



Который, для выборочных данных:




Все выходные данные:










НОВАЯ КОЛОНКА






00012




00078




34567




00123










бд<>скрипка <>здесь


Комментарии:


1.  Не могу использовать регулярное выражение, у вас есть решение без него?  


2.  @NicholasGregory Обновил несколько других решений.  


3.  Спасибо!   У работы сложная система, я могу запрашивать только сторону MSSQL, а не Oracle, поэтому я попрошу своего POC попробовать это завтра :)  


)

       THEN '00' || SUBSTR(Column1, 1, 3)

       WHEN REGEXP_LIKE(Column1, '^d{7}-d{5}s*Или, проще говоря:




Который, для выборочных данных:




Все выходные данные:










НОВАЯ КОЛОНКА






00012




00078




34567




00123










бд<>скрипка <>здесь


Комментарии:


1.  Не могу использовать регулярное выражение, у вас есть решение без него?  


2.  @NicholasGregory Обновил несколько других решений.  


3.  Спасибо!   У работы сложная система, я могу запрашивать только сторону MSSQL, а не Oracle, поэтому я попрошу своего POC попробовать это завтра :)  


)

       THEN SUBSTR(Column1, 3, 5)

       ELSE Column1

       END AS NewColumn

FROM   schema.table1

 Или, проще говоря:




Который, для выборочных данных:




Все выходные данные:










НОВАЯ КОЛОНКА






00012




00078




34567




00123










бд<>скрипка <>здесь


Комментарии:


1.  Не могу использовать регулярное выражение, у вас есть решение без него?  


2.  @NicholasGregory Обновил несколько других решений.  


3.  Спасибо!   У работы сложная система, я могу запрашивать только сторону MSSQL, а не Oracle, поэтому я попрошу своего POC попробовать это завтра 🙂  








	Метки: ,