MIN/MAX символьного поля

[adi_den2013]

Есть такая таблица (см. скрин)
Пытаюсь запросом выбрать минимум и максимум по полю SCHET

SELECT MIN( "SCHET" ) AS "С", MAX( "SCHET" ) AS "ПО", "GR1" FROM "СПРАВОЧНИК СЧЕТОВ" GROUP BY "GR1" HAVING ( ( "GR1" IS NOT NULL ) )

минимум   - 811.10.10
максимум - 811.6.6

это из-за того, что поле SCHET символьное.

Как преобразовать в числовое, чтобы выборка делалась правильно?


[вложение удалено Администратором]

[adi_den2013]

Нужно преобразовать их к одному шаблону ХХХ.ХХ.ХХ я так понимаю 

[JohnSUN]

...или разделить на три числовых столбца - счет-субсчет-субсубсчет

Как насчет создать представление приблизительно с таким текстом

Код Выделить Развернуть

SELECT "СПРАВОЧНИК СЧЕТОВ".*,
  ( CAST( LEFT( "SCHET", LOCATE( '.', "SCHET" ) - 1 ) AS INTEGER ) * 1000
    + CAST( SUBSTR( "SCHET", LOCATE( '.', "SCHET" ) + 1, LOCATE( '.', "SCHET", LOCATE( '.', "SCHET" ) + 1 )
    - ( LOCATE( '.', "SCHET" ) + 1 ) ) AS INTEGER ) ) * 1000 + CAST( RIGHT( "SCHET", LENGTH( "SCHET" )
    - LOCATE( '.', "SCHET", LOCATE( '.', "SCHET" ) + 1 ) ) AS INTEGER ) AS "DIGIT_SCHET"
FROM "СПРАВОЧНИК СЧЕТОВ" и уже из него дергать данные, ориентируясь на значение в DIGIT_SCHET?

[adi_den2013]

Как насчет создать представление приблизительно с таким текстом....

Я пошла более длинным путем, отталкиваясь от длины счета и прибавляя "0" в определенных местах после точки. Ваш вариант намного компактнее. Можно вопрос?

AS INTEGER - это преобразование найденного текста в число  ?

Не то я не нашла как это сделать. Только с ним SQL "ругается" -  requires FROM in statement

[JohnSUN]

Я пошла более длинным путем, отталкиваясь от длины счета и прибавляя "0" в определенных местах после точки.
Ваш вариант намного компактнее.

Увы, и более длинный, и более компактный варианты обречены на неудачу, если внося новый счет бухгалтер стукнет не точку, а запятую... Или создаст четырехзначный субсчет... Ну вот захотелось им иметь 811,1006,112 и плевать, что база перестала работать...

Можно вопрос?
AS INTEGER - это преобразование найденного текста в число  ?
Не то я не нашла как это сделать.

Вся конструкция CAST( <чего-то-там-символьного-формата> AS INTEGER ) - преобразование первого операнда к типу, который указан после AS.

Только с ним SQL "ругается" -  requires FROM in statement

Как это "нет FROM"?!! Вот же он, последняя строка - FROM "СПРАВОЧНИК СЧЕТОВ"

[adi_den2013]

Теперь другое сообщение об ошибке. Чего-то не хватает в АОО.BASE или что-то с типом данных?

[вложение удалено Администратором]

[JohnSUN]

А у тебя все-все счета по одной маске сделаны? Цифры-точка-цифры-точка-цифры? А то, похоже, SELECT где-то не смог вырезать цифры до-после-между точками.
Покажь-ка таблицу с данными... И, наверное, не скриншотом, а хотя бы скопипасти значения... А то так обломно с твоих картинок тестовые данные руками перебивать...

[adi_den2013]

А у тебя все-все счета по одной маске сделаны? Цифры-точка-цифры-точка-цифры? А то, похоже, SELECT где-то не смог вырезать цифры до-после-между точками.

Ooooops  

Нет, конечно. Таблица на 396 записей. Даю базу.

Вот как выборку писала я:

Код Выделить Развернуть

SELECT VALUE,"SCHET", SUBSTRING("SCHET",1,4)||'00'||SUBSTRING("SCHET",5,1) AS S,LENGTH(RTRIM("SCHET")) AS "DLINA", gr1 FROM "СПРАВОЧНИК СЧЕТОВ" WHERE DLINA IN (5)
UNION
SELECT VALUE,"SCHET", SUBSTRING("SCHET",1,4)||'00'||SUBSTRING("SCHET",5,2)||'0'||SUBSTRING("SCHET",7,1) AS S,LENGTH(RTRIM("SCHET")) AS "DLINA", gr1 FROM "СПРАВОЧНИК СЧЕТОВ" WHERE DLINA IN (7) AND SUBSTRING("SCHET",6,1)='.'
UNION
SELECT VALUE,"SCHET", "SCHET" AS S,LENGTH(RTRIM("SCHET")) AS "DLINA", gr1 FROM "СПРАВОЧНИК СЧЕТОВ" WHERE DLINA IN (7) AND SUBSTRING("SCHET",6,1)<>'.'
UNION
SELECT VALUE,"SCHET", "SCHET" AS S,LENGTH(RTRIM("SCHET")) AS "DLINA", gr1 FROM "СПРАВОЧНИК СЧЕТОВ" WHERE DLINA IN (1,2,3,4)
union
SELECT VALUE,"SCHET", SUBSTRING("SCHET",1,4)||'0'||SUBSTRING("SCHET",5,2)||'.0'||SUBSTRING("SCHET",8,1) AS S,LENGTH(RTRIM("SCHET")) AS "DLINA", gr1 FROM "СПРАВОЧНИК СЧЕТОВ" WHERE DLINA IN (8) and SUBSTRING("SCHET",7,1)='.'
union
SELECT VALUE,"SCHET", SUBSTRING("SCHET",1,4)||'00'||SUBSTRING("SCHET",5,2)||'0'||SUBSTRING("SCHET",8,1) AS S,LENGTH(RTRIM("SCHET")) AS "DLINA", gr1 FROM "СПРАВОЧНИК СЧЕТОВ" WHERE DLINA IN (8) and SUBSTRING("SCHET",7,1)<>'.'
UNION
SELECT VALUE,"SCHET", SUBSTRING("SCHET",1,4)||'0'||SUBSTRING("SCHET",5,2)||SUBSTRING("SCHET",7,3) AS S,LENGTH(RTRIM("SCHET")) AS "DLINA",GR1 FROM "СПРАВОЧНИК СЧЕТОВ" WHERE DLINA IN (9)
UNION
SELECT VALUE,"SCHET", SUBSTRING("SCHET",1,4)||'0'||SUBSTRING("SCHET",5,2) AS S,LENGTH(RTRIM("SCHET")) AS "DLINA",GR1 FROM "СПРАВОЧНИК СЧЕТОВ" WHERE DLINA IN (6)

Во всяком случае результат визуально удобоваримый. Только теперь при выводе на печать мне нужны нормальные счета - не преобразованные  

[вложение удалено Администратором]

[JohnSUN]

Попробуй Сервис-SQL, в окно "Выполняемая команда" вставь вот это:

Код Выделить Развернуть

CREATE VIEW "СЧЕТА" AS SELECT "СПРАВОЧНИК СЧЕТОВ".*,
( CAST( LEFT( "SCHET", LOCATE( '.', "SCHET" || '.' ) - 1 ) AS INTEGER ) * 1000
+ CAST( SUBSTR( "SCHET" || '.0', LOCATE( '.', "SCHET" || '.0' ) + 1,
LOCATE( '.', "SCHET" || '.0.', LOCATE( '.', "SCHET" || '.0.' ) + 1 )
- ( LOCATE( '.', "SCHET" || '.' ) + 1 ) ) AS INTEGER ) ) * 1000
+ CAST( SUBSTR( "SCHET" || '.0.0', LOCATE( '.', "SCHET" || '.0.0', LOCATE( '.', "SCHET" || '.0.0' ) + 1 ) + 1,
( LOCATE( '.', "SCHET" || '.0.0.', LOCATE( '.', "SCHET" || '.0.0', LOCATE( '.', "SCHET" || '.0.0',
LOCATE( '.', "SCHET" || '.0.', LOCATE( '.', "SCHET" || '.' ) + 1 ) ) ) + 1 ) - 1 )
- LOCATE( '.', "SCHET" || '.0.0', LOCATE( '.', "SCHET" || '.0.0',
LOCATE( '.', "SCHET" || '.0.', LOCATE( '.', "SCHET" || '.' ) + 1 ) ) ) ) AS INTEGER )
AS "DIGIT_SCHET" FROM "СПРАВОЧНИК СЧЕТОВ"
После "Выполнить" в окошке Состояние должно выскочить "Команда выполнена успешно."
Теперь переключаешься на Таблицы, из меню выбираешь Вид-Обновить таблицы.
Среди таблиц должна появиться вьюха с именем СЧЕТА. Последняя колонка в ней - DIGIT_SCHET - должна сортироваться нормально, минимумы-максимумы выдавать и всё такое... А для отчетов, само собой, нужно использовать значение "SCHET" из той же строки.

[adi_den2013]

Вьюха создалась успешно. Спасибо!

[JohnSUN]

Тебе спасибо! За образец данных. 

[adi_den2013]

Особо не за что.
Бум стремиться к совершенству  .

[adi_den2013]

Образовалась маленькая проблемка: пары субсчетов 801.01 и 801.001, 811.01 и 811.001 преобразовались одинаково 801001000  . Наверное, нужно предусмотреть условие ХХХ.ХХХ.ХХ?

Что-то не выходит у меня  


*********************
П.С.

Пока вышла из положения не преобразованием к шаблону, а заменой '.' на '0'
CREATE VIEW "SCHETA"  AS SELECT "СПРАВОЧНИК СЧЕТОВ".*,  CAST(REPLACE(SCHET,'.','0') AS INTEGER) AS S FROM "СПРАВОЧНИК СЧЕТОВ"

[JohnSUN]

Блин! Надеялся пронесёт, ан нет - не пронесло... Подводные камни учуял сразу, как только увидел в твоей таблице субсчета .01, .02 и т.д.
Вот ты мне скажи, а какой такой тайный смысл в этих нулях? Чем счет 811.1 будет отличаться от 811.01 и уж тем более от 811.001?
Я в своё время пытался у бухгалтеров выяснить эту страшную тайну, но в ответ обычно слышал какой-то околонаучный бред типа, "Ну вот объясню на примере... Допустим нам на 63-ий счет приходит 100... Нет, лучше 2000... Вот... А потом часть из этих денег должна уйти на 73-ий, а остальное разделиться между ...-надцатым и ...-ым счетами и параллельно зафиксироваться на забалансовом ...-ом счете..." И такие "объяснения" могли продолжаться часами и так никогда и не добраться до сути вопроса - нужен ли этот ноль в номере субсчета и какую роль он играет?!!

П.С. Прямая замена точки на ноль - плохая идея... Порядок сортировки будет перевран. То есть, нарвёшься на ту же самую ерунду при выборе МИН и МАКС, с которой эта тема начиналась 

[adi_den2013]

Потому, что есть группы счетов
801.01-801.04.2

801.001-811.007 (с 3-мя нулями только эта группа) и на ней висят немалые суммы

Как мне их выделить?

А если потом дополнить нулями в конце REPLACE(SCHET,'.','0') до необходимой длины->RPAD(SCHET,LENGTH(SCHET),'0')? Только ошибка опять