ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Число хранится как текст. Как написать выборку с использованием between?
89 сообщений из 89, показаны все 4 страниц
  все  
Число хранится как текст. Как написать выборку с использованием between?
    #39537181
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
Есть большая таблица MY_TABLE (строк порядка 120 млн.). Один из столбцов по имени RECEIPT хранит в поле числа (номер чека), но тип столбца VARCHAR2(15).
Создал процедуру, внутри которой идёт выборка данных из этой таблицы таким образом:


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE OR REPLACE PROCEDURE MY_PROCEDURE ( список_аргументов ) 
IS 

A1 INTEGER;
A2 INTEGER;

BEGIN

....


SELECT список_столбцов
INTO список_переменных
FROM MY_TABLE
WHERE RECEIPT between A1 and A2;

...

END;



Тимлид сказал, что в этом селекте я допустил грубую ошибку, так как здесь идёт неявное преобразование строки в число (или быть может чисел A1 и A2 в строку!?).

Так ли это и как по вашему нужно писать данный селект?

P.S.
Проектировщик БД не я. Я бы никогда не стал хранить число как строку.
...
Рейтинг: 0 / 0
17.10.2017, 07:52
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537187
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
PL SQL НовичокТимлид сказал, что в этом селекте я допустил грубую ошибку, так как здесь идёт неявное преобразование строки в число (или быть может чисел A1 и A2 в строку!?).

Так ли это и как по вашему нужно писать данный селект?


Проверить то не сложно, чтоб не верить людям на слово

Код: plsql
1.
2.
3.
4.
5.
6.
7.
with t as (select 'a10' RECEIPT from dual)

select *
from t
where RECEIPT between 1 and 2

ORA-01722: invalid number





PL SQL НовичокP.S.
Проектировщик БД не я. Я бы никогда не стал хранить число как строку.

Я бы никогда - Выдыет опытного, матерого архитектора БД
...
Рейтинг: 0 / 0
17.10.2017, 08:14
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537188
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
PL SQL НовичокТимлид сказал, что в этом селекте я допустил грубую ошибку, так как здесь идёт неявное преобразование строки в числоГрубую ошибку сделал архитектор. Само по себе неявное преобразование не так страшно. Но в данном случае RECEIPT преобразуется к числу и как следствие:
1) не может быть использован индекс;
2) если где-то в RECEIPT есть нечисло, то запрос всегда будет падать.
Это всё следствие быдлоархитектуры, но со вторым можно бороться.
...
Рейтинг: 0 / 0
17.10.2017, 08:15
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537190
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
если на поле RECEIPT индекс, лучше так:
Код: plsql
1.
2.
3.
4.
5.
6.
for i in A1 .. A2 loop
SELECT список_столбцов
INTO список_переменных
FROM MY_TABLE
WHERE RECEIPT = to_char(i);
end loop;
...
Рейтинг: 0 / 0
17.10.2017, 08:20
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537195
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Fogelлучше так:Новичок, не смотри тот говнокод, он скрывает PL SQL Новичокгрубую ошибку:PL SQL Новичок
Код: plsql
1.
INTO список_переменных
...
Рейтинг: 0 / 0
17.10.2017, 08:36
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537201
Уникальный мужчина
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Уникальный мужчина
Гость
Fogelесли на поле RECEIPT
Я вдруг стал как все?
...
Рейтинг: 0 / 0
17.10.2017, 08:51
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537206
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
MaximaXXLORA-01722: invalid number
С этим проблем нет, там хранятся только валидные числа.
...
Рейтинг: 0 / 0
17.10.2017, 09:02
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537208
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ElicГрубую ошибку сделал архитектор. Само по себе неявное преобразование не так страшно. Но в данном случае RECEIPT преобразуется к числу и как следствие:
1) не может быть использован индекс;
2) если где-то в RECEIPT есть нечисло, то запрос всегда будет падать.
Это всё следствие быдлоархитектуры, но со вторым можно бороться.
Согласен с вами.
Но пункт 2 исключается, так как там только валидные числа.
Поэтому мой вопрос актуален - как бы вы написали этот селект?
...
Рейтинг: 0 / 0
17.10.2017, 09:03
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537212
jan2ary
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
jan2ary
Участник
PL SQL НовичокНо пункт 2 исключается, так как там только валидные числа.Классика.
Что такое "валидньіе числа"? 4.5 - валидное число? А 4,5? А 1 500 455,15? А е^-1?
...
Рейтинг: 0 / 0
17.10.2017, 09:14
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537218
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
PL SQL Новичоккак бы вы написали этот селект?Проблема не в самом запросе.
...
Рейтинг: 0 / 0
17.10.2017, 09:22
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537221
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
jan2ary,

Валидные целые положительные числа, записанные исключительно с использованием символов из множества
{ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }

По существу вопроса будет что сказать?
...
Рейтинг: 0 / 0
17.10.2017, 09:24
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537226
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ElicPL SQL Новичоккак бы вы написали этот селект?Проблема не в самом запросе.Согласен с вами. Мой тимлид считает, что проблема именно в моём селекте.
Он так и сказал: "грубая" ошибка!

Я сразу понял, что никакой "грубой" ошибки я не совершал, но спорить не стал - это у нас не поощряется.

Мне даже было рекомендовано ознакомиться с ссылкой
http://www.sqlbooks.ru/Articles03/sqloracle02_ru.htm
Но и там я не нашёл ничего, что свидетельствовало бы о "грубой" ошибке.
...
Рейтинг: 0 / 0
17.10.2017, 09:28
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537232
yabs
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
yabs
Участник
ну можешь а1 и а2 в строку конвертировать, если тимлида это устроит
в случае целых числами вроде будет тот же результат
хотя думай сам, это уже посимвольное сравнение строк
Код: sql
1.
WHERE RECEIPT between to_char(A1) and to_char(A2)



православнее было бы явно конвертировать RECEIPT в число, предварительно проверив содержимое
Код: sql
1.
WHERE REGEXP_LIKE(RECEIPT, '^\d+$') and to_number(RECEIPT) between A1 and A2
...
Рейтинг: 0 / 0
17.10.2017, 09:31
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537234
jan2ary
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
jan2ary
Участник
PL SQL Новичокjan2ary,

Валидные целые положительные числа, записанные исключительно с использованием символов из множества
{ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }

По существу вопроса будет что сказать?Тогда обьявить А1 и А2 как varchar2 и дело в шляпе.
...
Рейтинг: 0 / 0
17.10.2017, 09:33
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537235
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
PL SQL Новичоксчитает, что проблема именно в моём селекте.Сколько строк, как предполагается, возвращает запрос?
...
Рейтинг: 0 / 0
17.10.2017, 09:34
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537236
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
yabs,

Я пока сделал так

Код: plsql
1.
WHERE    TO_NUMBER(RECEIPT) BETWEEN A1 AND A2
...
Рейтинг: 0 / 0
17.10.2017, 09:34
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537237
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ElicPL SQL Новичоксчитает, что проблема именно в моём селекте.Сколько строк, как предполагается, возвращает запрос?не более 10000
...
Рейтинг: 0 / 0
17.10.2017, 09:35
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537238
yabs
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
yabs
Участник
PL SQL НовичокМой тимлид считает, что проблема именно в моём селекте.
Он так и сказал: "грубая" ошибка!

и он прав
никто тебе не даст гарантию, что номера чеков не станут вдруг текстовыми
...
Рейтинг: 0 / 0
17.10.2017, 09:36
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537243
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
yabsникто тебе не даст гарантию, что номера чеков не станут вдруг текстовымия уже несколько раз повторял, что в этом поле всегда будут целые положительные числа. Сколько раз мне нужно написать эту фразу? Я уже понял, что архитектор погорячился, но один фиг, будут только числа.
...
Рейтинг: 0 / 0
17.10.2017, 09:40
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537246
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
PL SQL Новичок,

Вы рассуждаете ... странно, то что Вы не знали в какую сторону идет преобразование - я порекомендовал проверить, и получил ответ "С этим проблем нет, там хранятся только валидные числа."

Вы спрашиваете как Вам решить вашу задачу ... а я ее пока не вижу ....
У чисел и строк разные подходы ...
Пример 1:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
with t as (select '10' RECEIPT from dual)

select *
from t
where to_number(RECEIPT) between 1 and 2

no data found



Пример 2:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
with t as (select '10' RECEIPT from dual)

select *
from t
where RECEIPT between '1' and '2'


RECEIPT
10



Я например понятия не имею что Вас попросили найти/сравнить но строки и числа сравниваются по разному
...
Рейтинг: 0 / 0
17.10.2017, 09:41
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537247
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
MaximaXXLстроки и числа сравниваются по разномуя в курсе

MaximaXXLчто Вас попросили найти/сравнитьсравнение должно проходить так, как если бы поле RECEIPT был бы INTEGER
...
Рейтинг: 0 / 0
17.10.2017, 09:43
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537249
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
PL SQL НовичокElicСколько строк, как предполагается, возвращает запрос?не более 10000RTFM too_many_rows
...
Рейтинг: 0 / 0
17.10.2017, 09:44
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537250
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
PL SQL НовичокЯ пока сделал так
Код: plsql
1.
WHERE    TO_NUMBER(RECEIPT) BETWEEN A1 AND A2

А оно именно так и работало.
...
Рейтинг: 0 / 0
17.10.2017, 09:44
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537253
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
Elic,
Сорри, я не так объяснил. Там только агрегатные функции, которые вычислены над подмножеством, состоящим из не более чем 10000 строк.
...
Рейтинг: 0 / 0
17.10.2017, 09:47
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537258
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ElicPL SQL Новичокпропущено...
не более 10000RTFM too_many_rows
типа такого

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select 
    MAX(F1), 
    MIN(F1),
    MAX(F2),
    MIN(F2),
    COUNT(*)   
INTO
    X1,
    X2,
    X3
    X4,
    X5
FROM MY_TABLE
WHERE TO_NUMBER(RECEIPT) BETWEEN A1 AND A2
...
Рейтинг: 0 / 0
17.10.2017, 09:51
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537260
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ElicPL SQL НовичокЯ пока сделал так
Код: plsql
1.
WHERE    TO_NUMBER(RECEIPT) BETWEEN A1 AND A2


А оно именно так и работало.я так и думал с самого начала и гадал, где здесь "грубая" ошибка.
...
Рейтинг: 0 / 0
17.10.2017, 09:52
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537264
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
PL SQL Новичок,

А если не секрет какие A1 и A2 в селекте?
...
Рейтинг: 0 / 0
17.10.2017, 09:54
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537269
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
MaximaXXL,
Это переменные типа INTEGER, которые получают значения из входных параметров процедуры.
...
Рейтинг: 0 / 0
17.10.2017, 10:01
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537278
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
Вариант поменять тип столбца путем add +update + drop + rename еще не предлагали?
...
Рейтинг: 0 / 0
17.10.2017, 10:09
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537280
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
AmKadВариант поменять тип столбца путем add +update + drop + rename еще не предлагали?Нет, этого нельзя делать. Таковы условия задачи. Работать с той таблицей, которая есть.
...
Рейтинг: 0 / 0
17.10.2017, 10:13
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537311
Фотография Viewer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Viewer
Участник
В таком разе, остаётся только рассмотреть функциональный индекс , при необходимости...
Код: plsql
1.
2.
create index number_receipt
on my_table(to_number(receipt))
...
Рейтинг: 0 / 0
17.10.2017, 10:35
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537317
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
Viewer,
Понятно, спасибо.
Хотя сам вопрос звучал несколько иначе -
как правильно составить условие в запросе на выборку.
Я написал как
Код: plsql
1.
WHERE   TO_NUMBER(RECEIPT) between A1 and A2


Если есть какие-то возражения или улучшения, то прошу поделиться.
...
Рейтинг: 0 / 0
17.10.2017, 10:41
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537322
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
PL SQL Новичок,

Грубая ошибка может заключатся именно в сравнении символьных строк и чисел

от постановки зависит (от сути поля), не зря ж оно символьное

напр
для '10001' и '9000' between отработает по разному


зы
"чеки" могут быть напр 07077070, ведущий ноль важен

.....
stax
...
Рейтинг: 0 / 0
17.10.2017, 10:46
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537323
учи тель
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
учи тель
Гость
yabsну можешь а1 и а2 в строку конвертировать, если тимлида это устроит
в случае целых числами вроде будет тот же результат
хотя думай сам, это уже посимвольное сравнение строк
Код: sql
1.
WHERE RECEIPT between to_char(A1) and to_char(A2)



православнее было бы явно конвертировать RECEIPT в число, предварительно проверив содержимое
Код: sql
1.
WHERE REGEXP_LIKE(RECEIPT, '^\d+$') and to_number(RECEIPT) between A1 and A2

С обоими вариантами ты залезаешь в еще большую жопу.
'22' between '1' and '3' - не тот результат, что требуется.
Порядок вычисления предикатов определяется оптимизатором, regexp в таком виде не гарантирует отсутсвие ошибки. Тогда уж вставлял бы regexp, только substr, в качестве аргумента to_number.
...
Рейтинг: 0 / 0
17.10.2017, 10:46
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537331
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
yabsправославнее было бы явно конвертировать RECEIPT в число, предварительно проверив содержимое
Код: sql
1.
WHERE REGEXP_LIKE(RECEIPT, '^\d+$') and to_number(RECEIPT) between A1 and A2

Наивный, это не спасёт.
 
Код: plsql
1.
 regexp_substr(RECEIPT, '^\d+$') between …

...
Рейтинг: 0 / 0
17.10.2017, 10:52
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537332
ORA__SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORA__SQL
Участник
PL SQL НовичокWHERE RECEIPT between A1 and A2;А какой практический смысл отбора номеров у чеков between?
PL SQL НовичокЯ бы никогда не стал хранить число как строку.А кто сказал что номер чека всегда число?
...
Рейтинг: 0 / 0
17.10.2017, 10:57
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537349
Фотография Viewer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Viewer
Участник
ORA__SQLА какой практический смысл отбора номеров у чеков between?
такова бизнес-логика 20874794
ORA__SQLА кто сказал что номер чека всегда число?
PL SQL Новичокя уже несколько раз повторял, что в этом поле всегда будут целые положительные числа. Сколько раз мне нужно написать эту фразу?
...
Рейтинг: 0 / 0
17.10.2017, 11:13
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537360
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
какой-то лютый трэш начался ))
PL SQL Новичок, не трогай вообще поле таблицы, не модифицируй его.

все советы (и мой в том числе) сводятся к: приводи к типу поля таблицы свои переменные, а не наоборот.

а как ты это будешь делать: по заветам Элика или сам по себе, не велика в данном случае разница.
чтобы советы по "канону" давать, надо весь твой код видеть и структуру данных.
и то, этот "канон" вполне может стать неканоничным через 5 лет.
...
Рейтинг: 0 / 0
17.10.2017, 11:24
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537372
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ORA__SQLА какой практический смысл отбора номеров у чеков between?Абсолютно никакого.
Но так надо.
...
Рейтинг: 0 / 0
17.10.2017, 11:34
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537373
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ORA__SQLА кто сказал что номер чека всегда число?Мне что, землю есть вот из этого цветочного горшка, чтобы вы мне поверили? (c)
...
Рейтинг: 0 / 0
17.10.2017, 11:35
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537377
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
Fogelне трогай вообще поле таблицы, не модифицируй его.Да я бы с удовольствием его модифицировал, если бы такие привилегии имел в БД.
...
Рейтинг: 0 / 0
17.10.2017, 11:38
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537378
ORA__SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORA__SQL
Участник
PL SQL НовичокORA__SQLА кто сказал что номер чека всегда число?Мне что, землю есть вот из этого цветочного горшка, чтобы вы мне поверили? (c)Сделать миграцию и отрефакторить не судьба?
...
Рейтинг: 0 / 0
17.10.2017, 11:38
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537398
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ORA__SQL,
не понял вашу мысль. Что именно рефакторить и с какой целью?
Если замерить производительность запроса - то можно и на боевой БД.
...
Рейтинг: 0 / 0
17.10.2017, 11:50
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537418
Фотография Viewer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Viewer
Участник
ORA__SQLСделать миграцию и отрефакторить
может что-нибудь попроще чем:
data migration
перенос (миграция) данных 1) процесс физического перемещения данных из одной среды в другую - на другую аппаратную платформу или в другую , возможно с преобразованием форматов (см. data conversion)
refactoring
(от factor разлагать на множители, составляющие, вычленять) - перестройка [программного кода], перепроектирование , рефакторинг в программировании - переписывание исходных текстов с целью улучшения внутренней структуры программного кода при сохранении его интерфейсов и функциональности. Один из способов борьбы с программной энтропией (software entropy)
20875004 - и проверить можно и гарантировать на будущее...
...
Рейтинг: 0 / 0
17.10.2017, 12:00
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537445
Да ну
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Да ну
Участник
PL SQL Новичоксравнение должно проходить так, как если бы поле RECEIPT был бы INTEGER
Код: plsql
1.
where lpad(receipt,10) between lpad(a1,10) and lpad(a2,10)
...
Рейтинг: 0 / 0
17.10.2017, 12:47
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537446
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
PL SQL НовичокMaximaXXL,
Это переменные типа INTEGER, которые получают значения из входных параметров процедуры.

Если все кроме Вас оперируют varchar-ами для номеров чеков, то может случиться еще 2 неявных преобразования на входных параметрах, что тоже может сыграть не в Вашу пользу

операторы min и max - для поля RECEIPT - тоже могут привести к разным результатам при работе с Number и Varchar ...

Т.о. надо видеть постановку и реализацию, чтоб сказать, как Ваш ТимЛид, грубая ошибка или просто неявное преобразование которого лучше избегать
...
Рейтинг: 0 / 0
17.10.2017, 12:47
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537457
шило на мыло
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
шило на мыло
Гость
Да нуPL SQL Новичоксравнение должно проходить так, как если бы поле RECEIPT был бы INTEGER
Код: plsql
1.
where lpad(receipt,10) between lpad(a1,10) and lpad(a2,10)

Больший размер функционального индекса. В случае появления не integer вместо ошибки возможно неожиданное поведение.
...
Рейтинг: 0 / 0
17.10.2017, 13:07
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537470
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
MaximaXXLоператоры min и max - для поля RECEIPT - тоже могут привести к разным результатам при работе с Number и VarcharЭто не операторы, а агрегатные функции. И для поля RECEIPT их никто не применяет.
...
Рейтинг: 0 / 0
17.10.2017, 13:34
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537493
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Да ну,

особенно если "номер" слева дополняется нулями

зы
не понимаю я таких заносчивых Тимлид-ов, раз сказал что ошибка, тем более грубая, что корона слетит, если обьяснит в чем грубость

......
stax
...
Рейтинг: 0 / 0
17.10.2017, 14:07
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537519
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Staxне понимаю я таких заносчивых Тимлид-ов, раз сказал что ошибка, тем более грубая
А ведь ошибка.
И именно грубая.
ТС видит в данных строки, которые выглядят как целые числа, и уже на этом основании готов "землю есть" что это они самые.
Между тем речь идет об идентификаторах, а не о числах.
И семантика работы с ними, как уже справедливо отметили участники форума, не тождественна семантике работы с целыми.

Конкретно по указанному случаю - целые числа и их представления в виде строк в позиционной системе счисления ведут себя одинаково, если все строки одной длины (или сведены к ней lpad-ом). Возможно, именно эта похожесть и приводит к выводу о том, что "такая бизнес-логика".
Я бы рекомендовал ТС смирить гордыню, подойти к тимлиду и попросить детальных пояснений.

А на будущее можно дать рекомендацию, которая справедлива в большинстве случаев: тип данных в таблице - первичен.
По возможности избегайте применения функций вообще и преобразований типа в частности к полям таблицы в кляузе where - вместо этого преобразуйте параметры.
А также следите за типами и никогда не используйте неявных преобразований.
В справедливости рекомендации сможете неоднократно убедиться в процессе сопровождения и развития программного продукта.
Удачи.
...
Рейтинг: 0 / 0
17.10.2017, 14:41
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537540
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
Staxраз сказал что ошибка, тем более грубая, что корона слетит, если обьяснит в чем грубость
andrey_anonymousподойти к тимлиду и попросить детальных пояснений.
И он таки пояснил. Сказал, что неявное преобразование данных приводит к неоправданному повышению нагрузки на сервер БД и кинул мне ссылку "на почитать" (см. выше).
...
Рейтинг: 0 / 0
17.10.2017, 14:58
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537541
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
Народ, на будущее - в столбце RECEIPT хранятся только строки, составленные из символов:
'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'

Никаких других символов там нет, не было и не будет.
Прошу рассматривать эту задачу именно в рамках этих условий.
...
Рейтинг: 0 / 0
17.10.2017, 15:01
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537545
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
PL SQL Новичокнеявное преобразование данных приводит к неоправданному повышению нагрузки на сервер БДНеявное преобразование не дороже явного.
Он неадекват. Экономит на спичках, когда дом горит.
...
Рейтинг: 0 / 0
17.10.2017, 15:09
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537554
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
ElicНеявное преобразование не дороже явного.Спасибо, именно это я также хотел уточнить.
...
Рейтинг: 0 / 0
17.10.2017, 15:21
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537556
K790
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K790
Участник
То что автор видит там только числа, не говорит о том, что в дальнейшем это не станет действительно строками.
Не знаю, что там в голове у архитектора, но, возможно, закладывались на применение каких-нибудь префиксов или постфиксов.
...
Рейтинг: 0 / 0
17.10.2017, 15:22
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537558
K790
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K790
Участник
IMHO.
...
Рейтинг: 0 / 0
17.10.2017, 15:22
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537563
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Что за тимлид такой, что сам и сразу не поясняет своих мыслей?
И почему бы сразу у него не попросить пояснений?
...
Рейтинг: 0 / 0
17.10.2017, 15:29
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537564
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
K790автор видит там только числа, не говорит о том, что в дальнейшем это не станет действительно строкамину вот опять ...
но даже если вдруг там появятся "нечисловые" строки (а этого не может быть, иначе нарушится бизнес-логика),
то у меня на этот случай предусмотрен блок Exception, и в этом случае процедура выдаст return_code > 0, что соответствует ошибке.
Успокойтесь уже с этими "нечисловыми" строками.
...
Рейтинг: 0 / 0
17.10.2017, 15:29
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537565
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
ElicОн неадекват. Экономит на спичках, когда дом горит.+1
...
Рейтинг: 0 / 0
17.10.2017, 15:29
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537566
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
PL SQL Новичокно даже если вдруг там появятся "нечисловые" строкикакого хрена тогда хранить числа как строки?
...
Рейтинг: 0 / 0
17.10.2017, 15:30
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537567
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
xtenderЧто за тимлид такой, что сам и сразу не поясняет своих мыслей?
И почему бы сразу у него не попросить пояснений?Ну я и спросил, почти что сразу и получил пояснения, которые, однако, меня не удовлетворили, что и побудило меня обратиться на форум профессионалов-ораклоидов.
...
Рейтинг: 0 / 0
17.10.2017, 15:31
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537569
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
xtenderкакого хрена тогда хранить числа как строки?Это вопрос к архитектору БД. Я тоже вот этим обстоятельством как раз недоволен.
...
Рейтинг: 0 / 0
17.10.2017, 15:32
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537570
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
PL SQL НовичокStaxраз сказал что ошибка, тем более грубая, что корона слетит, если обьяснит в чем грубость
andrey_anonymousподойти к тимлиду и попросить детальных пояснений.
И он таки пояснил. Сказал, что неявное преобразование данных приводит к неоправданному повышению нагрузки на сервер БД и кинул мне ссылку "на почитать" (см. выше).

Повышение нагрузки потенциально возможно при наличии индекса по полю RECEIPT,
и тут уже важно как ети цифирки хранятся в поле, выровненные по правому/левому полю, дополненные нулями, как null хранится (встречал что как пробел) и тд

ps
імхо, надо приставать к лидеру, пусть расказывает, или дает права ...

.....
stax
...
Рейтинг: 0 / 0
17.10.2017, 15:33
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537575
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
andrey_anonymousА на будущее можно дать рекомендацию, которая справедлива в большинстве случаев: тип данных в таблице - первичен.
По возможности избегайте применения функций вообще и преобразований типа в частности к полям таблицы в кляузе where - вместо этого преобразуйте параметры.

Иногда так архитекторы настроят, что без преобразования тяжеловато
напр числовые коды в строке выровненные влево ('10','7','72','3','100') и выбрать из диапазона с по

.....
stax
...
Рейтинг: 0 / 0
17.10.2017, 15:38
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537580
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
xtenderкакого хрена тогда хранить числа как строки?
Никто не говорил, что там именно числа. Так считает топикстартер. У вас например БИК банка (это всегда цифры и только цифры) - это число или строка?
...
Рейтинг: 0 / 0
17.10.2017, 15:42
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537585
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
j2kБИК банка (это всегда цифры и только цифры)Зарекаться от IBAN, как минимум, неразумно. А пока рекомендую строку с check-ом.
...
Рейтинг: 0 / 0
17.10.2017, 15:48
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537587
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
xtenderPL SQL Новичокно даже если вдруг там появятся "нечисловые" строкикакого хрена тогда хранить числа как строки?
Саян, не строки это.
Идентификаторы.
ИМХО ТС несколько горяч - это пройдет с опытом.
...
Рейтинг: 0 / 0
17.10.2017, 15:50
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537588
PL SQL Новичок
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PL SQL Новичок
Участник
j2kНикто не говорил, что там именно числа
там именно числа. Такова постановка задачи, а вовсе не потому, что я так посчитал.
...
Рейтинг: 0 / 0
17.10.2017, 15:50
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537592
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Staxandrey_anonymousА на будущее можно дать рекомендацию, которая справедлива в большинстве случаев: тип данных в таблице - первичен.
По возможности избегайте применения функций вообще и преобразований типа в частности к полям таблицы в кляузе where - вместо этого преобразуйте параметры.

Иногда так архитекторы настроят, что без преобразования тяжеловато
напр числовые коды в строке выровненные влево ('10','7','72','3','100') и выбрать из диапазона с по
В телекоме коды ABC/DEF именно таковы.
Я на них в свое время "собаку съел", решая задачу эффективной привязки зоны к номеру по наиболее полному соответствию.
Виталий, кстати, подсказал одно из весьма эффективных решений - для одиночных поисков (для массовых операций есть способы более эффективные, хотя и довольно сложные технически).
Но к преобразованию цифровых кодов в числа для решения задач поиска не прибегал.
...
Рейтинг: 0 / 0
17.10.2017, 16:00
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537673
Архе те, кто р...
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Архе те, кто р...
Гость
xtenderPL SQL Новичокно даже если вдруг там появятся "нечисловые" строкикакого хрена тогда хранить числа как строки?Может архитектор задумывал как внешний номер. Может его пишут от руки на салфетке и не всегда можно разобрать, где рыбу заворачивали. Я бы в таком случае заложил блоб для хранения картинки идентификатора.
...
Рейтинг: 0 / 0
17.10.2017, 18:18
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537678
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
Архе те, кто р...xtenderпропущено...
какого хрена тогда хранить числа как строки?Может архитектор задумывал как внешний номер. Может его пишут от руки на салфетке и не всегда можно разобрать, где рыбу заворачивали. Я бы в таком случае заложил блоб для хранения картинки идентификатора.
картинки мало - нужно видео не менее 30 минут с размышлениями создателя, почему именно такой идентификатор был выбран для данного случая.

я так и не понял, почему до сих пор не выяснили, А1 и А2 - это числа или нет? )))

и мы так и не услышали мнение начальника транспортного цеха сурового тимлида, бьющего линейкой по пальцам за "грубые" ошибки.
...
Рейтинг: 0 / 0
17.10.2017, 18:23
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537780
Фотография Relic Hunter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Relic Hunter
Участник
PL SQL Новичок,

Нормальная практика. В больших, масштабируемых системах всегда закладываются на возможность изменения бизнес правил. Число это - часный случай строки. Архитектор запроектировал, а какие данные туда упали числа или строки ему не известно. А если кто-то по строкам делает "between", то кто ему доктор :)
...
Рейтинг: 0 / 0
18.10.2017, 03:52
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537783
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
andrey_anonymousВ телекоме коды ABC/DEF именно таковы.
Я на них в свое время "собаку съел", решая задачу эффективной привязки зоны к номеру по наиболее полному соответствию.кстати, да, очень интересная и полезная задачка. Я уже тут видел похожие топики и даже что-то решал, но сейчас что-то не осилил поиском найти твои...
Может отдельный топик создашь про нее со своими решениями? Туда и другие варианты накидаем :)

я тут минут 10 подумал и такое наваял:
 
исходные таблички
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/


 
мое решение
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
alter table prefixes add len number generated always as (length(prefix))
/
create unique index ix_prefixes on prefixes(len,prefix,descr);
/
with 
  n as (
    select/*+ inline */ (select max(len) from prefixes)-level+1 N
    from dual 
    connect by level<=(select max(len) from prefixes)
 )
select
  str
  ,(select 
       (select p.descr from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n))
    from N
    where (select p.descr from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n)) is not null
      and rownum=1
   )
from test;

...
Рейтинг: 0 / 0
18.10.2017, 04:52
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537786
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
еще немного подумав, понял что для коротких префиксов оптимальнее будет так:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
alter table prefixes add len number generated always as (length(prefix))
/
create unique index ix_prefixes on prefixes(len,prefix,descr);
/
create or replace function max_prefix_len return int result_cache deterministic as
   res int;
begin
   select max(len) into res from prefixes;
   return res;
end;
/
create or replace function get_prefix(str in varchar2) return rowid result_cache deterministic as
   ml int := max_prefix_len;
   res rowid;
   cursor c is 
     with 
       n as (
       select/*+ inline */ ml-level+1 N
       from dual 
       connect by level<=ml
     )
     select 
       (select p.rowid from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n))
     from N
     where (select p.rowid from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n)) is not null;

begin
   open c;
   fetch c into res;
   close c;
   return res;
end;
/
select
  str,p.*
from test t
    ,prefixes p
where p.rowid=get_prefix(substr(t.str,1,max_prefix_len));

...
Рейтинг: 0 / 0
18.10.2017, 05:28
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537807
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
xtenderно сейчас что-то не осилил поиском найти твоиElicSTFF Как можно выбрать максимум подходящую строку из таблицы?
...
Рейтинг: 0 / 0
18.10.2017, 07:36
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537840
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Elic,

Ага, спасибо, отличное решение, помню его и про NLS-зависимость.
В моих тестах при binary и практическом отсутствии повторов на миллионе, оно самое быстрое:
 
1. Elapsed: 00:00:35.62
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
set echo on timing on;
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/
alter table prefixes add len number generated always as (length(prefix))
/
create unique index ix_prefixes on prefixes(len,prefix,descr)
/
begin
   for r in (
      with 
        n as (
          select/*+ inline */ (select max(len) from prefixes)-level+1 N
          from dual 
          connect by level<=(select max(len) from prefixes)
       )
      select
        str
        ,(select 
             (select p.descr from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n))
          from N
          where (select p.descr from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n)) is not null
            and rownum=1
         )
      from test
   )loop null;end loop;
end;
/
set echo off timing off;

 
2. Elapsed: 00:00:11.29
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
set echo on timing on;
drop table test purge;
drop table prefixes purge;
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/
alter table prefixes add len number generated always as (length(prefix))
/
create unique index ix_prefixes on prefixes(len,prefix,descr)
/
create or replace function max_prefix_len return int result_cache deterministic as
   res int;
begin
   select max(len) into res from prefixes;
   return res;
end;
/
create or replace function get_prefix(str in varchar2) return rowid result_cache deterministic as
   ml int := max_prefix_len;
   res rowid;
   cursor c is 
     with 
       n as (
       select/*+ inline */ ml-level+1 N
       from dual 
       connect by level<=ml
     )
     select 
       (select p.rowid from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n))
     from N
     where (select p.rowid from prefixes p where p.len=n.n and p.prefix=substr(str,1,n.n)) is not null;

begin
   open c;
   fetch c into res;
   close c;
   return res;
end;
/
begin
   for r in (
      select
        str,p.*
      from test t
          ,prefixes p
      where p.rowid=get_prefix(substr(t.str,1,max_prefix_len))
   )loop null; end loop;
end;
/
begin
   for r in (
      select
        str,p.*
      from test t
          ,prefixes p
      where p.rowid=get_prefix(substr(t.str,1,max_prefix_len))
   )loop null; end loop;
end;
/
set echo off timing off;

 
Elic. Elapsed: 00:00:09.67
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
set echo on timing on;
drop table test purge;
drop table prefixes purge;
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/
commit
/
begin for r in (
select 
  t.str, lat.*
from test t
    ,lateral(
       select v.* 
       from (select/*+ index_desc(p (prefix)) */ p.* 
             from prefixes p 
             where p.prefix<=t.str 
             order by p.prefix desc) v 
       where rownum=1
    ) lat
)loop null; end loop;
end;
/
set echo off timing off;



PS. Генератор в моем первом решении съедает практически все время...

Модератор: Вложение удалено.
...
Рейтинг: 0 / 0
18.10.2017, 09:29
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537844
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Fogel
я так и не понял, почему до сих пор не выяснили, А1 и А2 - это числа или нет? )))



Код: plsql
1.
2.
A1 INTEGER;
A2 INTEGER;



.....
stax
...
Рейтинг: 0 / 0
18.10.2017, 09:34
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537889
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
xtender
Код: plsql
1.
2.
drop table test purge;
create table test as

Саян, я на Verification Code собаку съел. Cleanup располагается в конце скрипта, чтобы вернуть всё к состоянию, как будто его (скрипта) здесь не было.
Просьба учесть.
...
Рейтинг: 0 / 0
18.10.2017, 10:39
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39537946
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
StaxFogelя так и не понял, почему до сих пор не выяснили, А1 и А2 - это числа или нет? )))



Код: plsql
1.
2.
A1 INTEGER;
A2 INTEGER;



.....
stax

Stax, это был стёб по наблюдению обсуждения.
...
Рейтинг: 0 / 0
18.10.2017, 11:53
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538166
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
xtenderandrey_anonymousВ телекоме коды ABC/DEF именно таковы.
Я на них в свое время "собаку съел", решая задачу эффективной привязки зоны к номеру по наиболее полному соответствию.кстати, да, очень интересная и полезная задачка. Я уже тут видел похожие топики и даже что-то решал, но сейчас что-то не осилил поиском найти твои...
Может отдельный топик создашь про нее со своими решениями? Туда и другие варианты накидаем :)
Я уже в другой области работаю, искать исходники лень.
Решение Виталия - очень эффективно для одиночных поисков.
Для _массовой привязки (десятки-сотни миллионов звонков) я либо преобразовывал входящую таблицу префиксов (чуть-чуть более толстую, чем в примерах и с учетом истории изменений , что вообще никто не показывает на форумах :) ) для привязки по равенству, либо - через похожее преобразование, но без нормализации длины префикса загонял в индексированную pl/sql таблицу и привязку делал в pipelined (через .prior())
...
Рейтинг: 0 / 0
18.10.2017, 15:47
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538437
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
SQL> begin for r in (
  2  select
  3    t.str, lat.*
  4  from test t
  5      ,lateral(
  6         select v.*
  7         from (select/*+ index_desc(p (prefix)) */ p.*
  8               from prefixes p
  9               where p.prefix<=t.str
 10               order by p.prefix desc) v
 11         where rownum=1
 12      ) lat
 13  )loop null; end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.75
SQL> begin for r in (
  2  select
  3    t.str, phone_pkg.get_code(t.str)
  4  from test t
  5  )loop null; end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.77


Функция phone_pkg.get_code взята у меня из книги.
When PL/SQL is better than vanilla SQL (стр 133)
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
create or replace package phone_pkg is

  type tp_phone_code is table of int index by varchar2(10);
  g_phone_code tp_phone_code;
  function get_code(p_num in varchar2) return varchar2 deterministic;
  l_max_l int;

end phone_pkg;
/
create or replace package body phone_pkg is

  function get_code(p_num in varchar2) return varchar2 deterministic is
    l_num varchar2(10);
    pragma udf;
  begin
    l_num := substr(p_num, 1, l_max_l);
  
    while (l_num is not null) and (not g_phone_code.exists(l_num)) loop
      l_num := substr(l_num, 1, length(l_num) - 1);
    end loop;
  
    return l_num;
  end;

begin
  for cur in (select * from prefixes) loop
    g_phone_code(cur.prefix) := 1;
  end loop;

  select max(length(prefix)) into l_max_l from prefixes;
end phone_pkg;
/

...
Рейтинг: 0 / 0
19.10.2017, 00:36
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538440
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop,

Я попытался сделать как у меня, т.е. чтобы можно было приджойнить поля из PREFIXES.
Но как-то долго вышло... Я что-то сделал не так?
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
create table test as
select  to_char(1000+level) STR
from dual
connect by level<=1e6
/
create table prefixes(prefix varchar2(10) primary key,descr varchar2(100));
insert into prefixes
select column_value prefix 
      ,'descr '||column_value descr
from table(ku$_vcnt('1','2','3','4','5','6','7','8','9'
                  ,'10','100','14','145'
                  ,'22','222'
                  ,'30','300','31','311'
                  ,'44','444'
                  ,'55','555','5555'
                  ,'66','666','6666'
                  ,'77','777','700'
                  ,'80','800','801'
                  ,'90','99','993')) t
/
call dbms_stats.gather_table_stats('','TEST');
call dbms_stats.gather_table_stats('','PREFIXES');
create or replace package phone_pkg is

  type tp_phone_code is table of rowid index by varchar2(10);
  g_phone_code tp_phone_code;
  function get_code(p_num in varchar2) return rowid deterministic;
  l_max_l int;

end phone_pkg;
/
create or replace package body phone_pkg is

  function get_code(p_num in varchar2) return rowid deterministic is
    l_num varchar2(10);
    pragma udf;
  begin
    l_num := substr(p_num, 1, l_max_l);
  
    while (l_num is not null) and (not g_phone_code.exists(l_num)) loop
      l_num := substr(l_num, 1, length(l_num) - 1);
    end loop;
  
    return g_phone_code(l_num);
  end;

begin
  for cur in (select p.prefix,p.rowid rid from prefixes p) loop
    g_phone_code(cur.prefix) := cur.rid;
  end loop;

  select max(length(prefix)) into l_max_l from prefixes;
end phone_pkg;
/
begin
   for r in (
      select
        str,p.*
      from test t
          ,prefixes p
      where p.rowid=phone_pkg.get_code(t.str)
   )loop null; end loop;
end;
/

...
Рейтинг: 0 / 0
19.10.2017, 01:07
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538442
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender,

От соединения надо уходить, descr можно засунуть в ассоциативный массив.
...
Рейтинг: 0 / 0
19.10.2017, 01:11
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538443
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop,

добавил в твой пакет еще функцию с возвратом max_length, чтобы уменшить кол-во вызова за счет кэширования deterministic и получилось дико быстро ~ 3.5 сек:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
create or replace package phone_pkg is

  type tp_phone_code is table of rowid index by varchar2(10);
  g_phone_code tp_phone_code;
  function max_length return int deterministic;
  function get_code(p_num in varchar2) return rowid deterministic;
  l_max_l int;

end phone_pkg;
/
create or replace package body phone_pkg is

  function max_length return int deterministic is
    pragma udf;
  begin
     return l_max_l;
  end;

  function get_code(p_num in varchar2) return rowid deterministic is
    l_num varchar2(10);
    pragma udf;
  begin
    l_num := substr(p_num, 1, l_max_l);
  
    while (l_num is not null) and (not g_phone_code.exists(l_num)) loop
      l_num := substr(l_num, 1, length(l_num) - 1);
    end loop;
  
    return g_phone_code(l_num);
  end;

begin
  for cur in (select p.prefix,p.rowid rid from prefixes p) loop
    g_phone_code(cur.prefix) := cur.rid;
  end loop;

  select max(length(prefix)) into l_max_l from prefixes;
end phone_pkg;
/
begin
   for r in (
      select
        str,p.*
      from test t
          ,prefixes p
      where p.rowid=phone_pkg.get_code(substr(t.str,1,phone_pkg.max_length))
   )loop null; end loop;
end;
/

...
Рейтинг: 0 / 0
19.10.2017, 01:12
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538444
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshopxtender,

От соединения надо уходить, descr можно засунуть в ассоциативный массив.просто как-то неожиданно долго стало, хотя там был HJ:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SQL_ID  4ptv4zztvfwzw, child number 0
-------------------------------------
SELECT STR,P.* FROM TEST T ,PREFIXES P WHERE
P.ROWID=PHONE_PKG.GET_CODE(T.STR)

Plan hash value: 1937684794

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |   468 (100)|          |
|*  1 |  HASH JOIN         |          |   360K|  6679K|   468   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PREFIXES |    36 |   432 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST     |  1000K|  6835K|   462   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("P".ROWID=CHARTOROWID("PHONE_PKG"."GET_CODE"("T"."STR")))
...
Рейтинг: 0 / 0
19.10.2017, 01:14
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538445
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
А вообще по теме кэширования: да, там действительно дикий оверхэд у result_cache - у меня всегда получалось, что ручной кэш в пакетных переменных намного быстрее. В ПСБ когда у меня была проблема еще и с непонятной инвалидацией, я даже сделал замороченный пакет в котором использовал и result_cache, и пакетные переменные:
1) была простая функция с result_cache чисто для инвалидации/сброса пакетных переменных
2) сами кэшируемые данные были в пакетных переменных, при инвалидации result_cache, вызывалась функция пересбора кэша
...
Рейтинг: 0 / 0
19.10.2017, 01:19
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538563
XMLer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
XMLer
Участник
ElicPL SQL НовичокТимлид сказал, что в этом селекте я допустил грубую ошибку, так как здесь идёт неявное преобразование строки в числоГрубую ошибку сделал архитектор. Само по себе неявное преобразование не так страшно. Но в данном случае RECEIPT преобразуется к числу и как следствие:
1) не может быть использован индекс;
2) если где-то в RECEIPT есть нечисло, то запрос всегда будет падать.
Это всё следствие быдлоархитектуры, но со вторым можно бороться.

Не факт что архитектор сделал грубую ошибку. Возможно это сознательное решение, связанное с особенностями генерации номера чека, призванное предотвратить обработку номеров как чисел. Например, он хотел этим сказать следующее: если вам нужно выбрать чеки выданные между двумя другими то даже не думайте искать по соответствию номеров. Ищите только по дате выдачи чека.
...
Рейтинг: 0 / 0
19.10.2017, 11:21
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538568
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
xtenderА вообще по теме кэширования: да, там действительно дикий оверхэд у result_cache - у меня всегда получалось, что ручной кэш в пакетных переменных намного быстрее.
Это до тех пор, пока данные в uga помещаются, дальше сложнее.
Я когда-то из спортивного интереса даже реализовал алгоритм ARC на PL/SQL, но невозможность отделить сам алгоритм от данных делает идею нерентабельной.
...
Рейтинг: 0 / 0
19.10.2017, 11:23
| Ответить | Цитировать | Написать  
Число хранится как текст. Как написать выборку с использованием between?
    #39538612
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
XMLerНапример, он хотел этим сказать следующее:Прибежали гадалки с вокзала?
...
Рейтинг: 0 / 0
19.10.2017, 12:18
| Ответить | Цитировать | Написать  
89 сообщений из 89, показаны все 4 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Число хранится как текст. Как написать выборку с использованием between?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1885079]:
 0ms
get settings:
 9ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 40ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 87ms
get tp. blocked users:
 1ms
others: 197ms
total:  368ms
созд. / загр.: 368ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]