Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Web программирование > SQL, базы данных
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 10.11.2020, 00:59   #1
KBO
Форумчанин
 
Регистрация: 11.06.2010
Сообщений: 525
По умолчанию Не получается вывести строки, которых нет в одной из таблиц

Доброе время суток уважаемые

У меня есть 2 БД, первая 'BD0' в которой хранится оборудование с таблицей «EQUIPMENT», и 'BD1' в которой хранятся сертификаты к этому оборудованию (может быть несколько сертификатов к одному оборудованию с разными датами). в таблице «CERTIFICATE».

В DataSet подключенной к BD0, я пишу запрос (см. ниже), которым хочу выбрать все оборудование, с просроченными сертификатами на текущую дату, а также оборудование у которого нет межкалибровочного интервала (eq.CALIBRATION_INTERVAL). Т.е. в таблице «CERTIFICATE» в поле DATE_OUT – это выдача сертификата, я в запросе прибавляю например 12 месяцев (eq.CALIBRATION_INTERVAL = 12, которые хранятся) в таблице «EQUIPMENT» и сравниваю с текущей датой. Сертификаты я достаю с помощью «procedure LAST_CERT»

Все работало нормально, но захотелось при этом выводить оборудование у которого вообще в таблице «CERTIFICATE» нет данных, т.е. нет сертификатов и тут начались проблемы с JOIN я не могу присоеденить к «EQUIPMENT» строки которых не выдает LAST_CERT. Конечно оно по логике должно соединятся и выводить. Но мешает условие «and lc.LAST_DATE is not null». А без него выводятся вообще все оборудование
Код:
EXECUTE BLOCK RETURNS
….
AS  declare variable ID_EQUIP integer; 
declare variable LAB_ID integer;
declare variable COUNT_REC integer;
declare variable CALIBR_INT integer;


declare procedure LAST_CERT(ID_EQUIP integer, CALIBR_INT integer) 
   returns(NUMBER_CERT varchar(25), LAST_DATE date, DEVICE_ID_CL integer, COUNT_REC integer) 
as  begin
  if (:CALIBR_INT is null) 
    then :CALIBR_INT = -1;
   
    for execute statement
      'SELECT ce.NUMBER_CERT, ce.DATE_OUT, ce.DEVICE_ID_CL FROM CERTIFICATE ce  left join CERTIFICATE b on ce.DEVICE_ID_CL = b.DEVICE_ID_CL and ce.DATE_OUT < b.DATE_OUT where b.DEVICE_ID_CL is NULL and ce.DEVICE_ID_CL in ('||ID_EQUIP||') and (DATEADD(month, '||CALIBR_INT||', ce.DATE_OUT)<current_date)'
     on external '…\BD1.fdb'  as user 'SYSDBA' password 'masterkey'
        into :NUMBER_CERT, :LAST_DATE, :DEVICE_ID_CL  do suspend;

end


     begin 
        for select eq.ID, eq.HOST_LABORATORY, eq.CALIBRATION_INTERVAL
           from EQUIPMENT eq where ARCHIVE in (1) and PASSPORT = 1
               order by eq.NAME, eq."TYPE", eq.NUMBER, eq.NEMBER_INVENTORY
                   into :ID_EQUIP, :LAB_ID, :CALIBR_INT

      do for 
         select eq.ID, eq.NAME, eq."TYPE", eq.NUMBER, eq.NEMBER_INVENTORY, eq.PRODUCER, eq.YEAR_BEGIN, eq.CALIBRATION_INTERVAL, eq.NAME_TEST, eq.REMARK,
             eq.USER_ID, eq.DEPARTMENT_ID, eq.TIME_EDIT, eq.ARCHIVE, eq.SPECIFICATION, eq.DEVICE_DEPARTMENT_ID, eq.TYPE_SERIES, eq.DISLOCATION,                eq.TYPE_EQUIP, eq.PASSPORT, eq.HOST_LABORATORY, eq.HOST_USER, eq.COUNT_CERT, rl.NAME_SHORT_LAB, lc.NUMBER_CERT, lc.LAST_DATE 
           from EQUIPMENT eq
             left join RECIEVE_LAB(:LAB_ID) rl on rl.id = eq.HOST_LABORATORY
             left join LAST_CERT(:ID_EQUIP, :CALIBR_INT) lc on eq.ID = lc.DEVICE_ID_CL
               where eq.ID = :ID_EQUIP
 and lc.LAST_DATE is not null            //помогает избавится от записей не удовлетворяющих условию даты сертификата
  or lc.DEVICE_ID_CL is null   //по идее должно выводить те строки "EQUIPMENT", которых нет в табл. «CERTIFICATE»

                   into ID, NAME, "TYPE", NUMBER, NEMBER_INVENTORY, PRODUCER, YEAR_BEGIN, CALIBRATION_INTERVAL, NAME_TEST, REMARK, USER_ID, DEPARTMENT_ID, TIME_EDIT, ARCHIVE,
                     SPECIFICATION, DEVICE_DEPARTMENT_ID, TYPE_SERIES, DISLOCATION, TYPE_EQUIP, PASSPORT, HOST_LABORATORY, HOST_USER, COUNT_CERT, NAME_SHORT_LAB, NUMBER_CERT, LAST_DATE do suspend; END

Но новые записи, т.е. то оборудование у которого нет сертификатов к существующему результату не добавилось.

Как бы мне модернизировать запрос?
Заранее спасибо за ответ
KBO вне форума Ответить с цитированием
Старый 10.11.2020, 10:09   #2
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

вар 1. перенести условие даты сертификата в join
Код:
 left join LAST_CERT(:ID_EQUIP, :CALIBR_INT) lc on eq.ID = lc.DEVICE_ID_CL and lc.LAST_DATE is not null
тогда и
Код:
or lc.DEVICE_ID_CL is null
не потребуется.

НО не окажутся ли какие-либо записи базовой таблицы лишними ?..

вар 2. поставить скобки явно?..
Код:
 and ( lc.LAST_DATE is not null 
  or lc.DEVICE_ID_CL is null )
программа — запись алгоритма на языке понятном транслятору
evg_m вне форума Ответить с цитированием
Старый 10.11.2020, 22:45   #3
KBO
Форумчанин
 
Регистрация: 11.06.2010
Сообщений: 525
По умолчанию

Все предложеное вами попробовал... - не хочет правильно работать.

Но еще один вариант есть
Код:
      

 select eq.ID, eq.NAME, eq."TYPE", eq.NUMBER, eq.NEMBER_INVENTORY, eq.PRODUCER, eq.YEAR_BEGIN, eq.CALIBRATION_INTERVAL, eq.NAME_TEST, eq.REMARK,
             eq.USER_ID, eq.DEPARTMENT_ID, eq.TIME_EDIT, eq.ARCHIVE, eq.SPECIFICATION, eq.DEVICE_DEPARTMENT_ID, eq.TYPE_SERIES, eq.DISLOCATION,                eq.TYPE_EQUIP, eq.PASSPORT, eq.HOST_LABORATORY, eq.HOST_USER, eq.COUNT_CERT, rl.NAME_SHORT_LAB, lc.NUMBER_CERT, lc.LAST_DATE 
           from EQUIPMENT eq
             left join RECIEVE_LAB(:LAB_ID) rl on rl.id = eq.HOST_LABORATORY
             INNER join LAST_CERT(:ID_EQUIP, :CALIBR_INT) lc on eq.ID = lc.DEVICE_ID_CL
                and lc.LAST_DATE is not null            
               where eq.ID = :ID_EQUIP  

UNION ALL

         select eq.ID, eq.NAME, eq."TYPE", eq.NUMBER, eq.NEMBER_INVENTORY, eq.PRODUCER, eq.YEAR_BEGIN, eq.CALIBRATION_INTERVAL, eq.NAME_TEST, eq.REMARK,
             eq.USER_ID, eq.DEPARTMENT_ID, eq.TIME_EDIT, eq.ARCHIVE, eq.SPECIFICATION, eq.DEVICE_DEPARTMENT_ID, eq.TYPE_SERIES, eq.DISLOCATION,                eq.TYPE_EQUIP, eq.PASSPORT, eq.HOST_LABORATORY, eq.HOST_USER, eq.COUNT_CERT,
             rl.NAME_SHORT_LAB,
             lc.NUMBER_CERT, lc.LAST_DATE
           from EQUIPMENT eq
             left join RECIEVE_LAB(:LAB_ID) rl on rl.id = eq.HOST_LABORATORY
             left join LAST_CERT(:ID_EQUIP, :CALIBR_INT) lc on eq.ID = lc.DEVICE_ID_CL
               where eq.ID = :ID_EQUIP
  AND lc.DEVICE_ID_CL is null
т.е. первый select выбирает сертификаты с датами,
а второй - выбирает устройства у которого вообще нет сертификатов, нет строк в таблице CERTIFICATE.

И что оказалось: если их запускать по отдельности, то
- первый select - работает на ура;
- второй select - ни фига НЕ работает, точнее работает но не выбирает то что нужно,
т.е. не правильно обрабатывает результат процедуры LAST_CERT, когда ничего не выдает, т.е. ПУСТОЙ результат

Последний раз редактировалось KBO; 10.11.2020 в 23:13.
KBO вне форума Ответить с цитированием
Старый 11.11.2020, 14:04   #4
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

вар. 3.
Код:
select from equipment eq ....
where not exists ( select * from last_cert(...) lc where lc.device_id_cl =eq.id
вар 4. "неверно"(?) работает last_cert
выдает и записи c отсутствующими сeртификатами. (и last_date у них =null )
программа — запись алгоритма на языке понятном транслятору
evg_m вне форума Ответить с цитированием
Старый 12.11.2020, 18:01   #5
KBO
Форумчанин
 
Регистрация: 11.06.2010
Сообщений: 525
По умолчанию

Всетаки решил переделать немного процедуру LAST_CERT, в самом скрипте, раньше я пробовал создать 2 запроса в процедуре, но у меня не получилось...

т.е. первым скриптом я считаю строки которые подходят условию
и если подходят (т.е. количество строк <> 0), то выбираю данные из CERTIFICATE - вторым скриптом
но если результат первого скрипта = 0,
я присваиваю
Код:
:NUMBER_CERT = -1;
:LAST_DATE = null;
:DEVICE_ID_CL = -1;
я создал внутреннюю отдельную процедуру и она работает.
но когда я ее вставил в скрипт, выкидывает синтаксическую ошибку:
Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
Execute statement error at isc_dsql_prepare :
335544569 : Dynamic SQL Error
335544436 : SQL error code = -104
335544634 : Token unknown - line 3, column 52
335544382 : into
Statement : SELECT count(*) FROM CERTIFICATE ce left join CERTIFICATE b on ce.DEVICE_ID_CL = b.DEVICE_ID_CL and ce.DATE_OUT < b.DATE_OUT
where b.DEVICE_ID_CL is NULL and ce.DEVICE_ID_CL in (433)
and (DATEADD(month, 12, ce.DATE_OUT)<current_date) into :COUNT_REC;
Data source : Firebird::D:\Database\Plan.fdb.
At sub procedure 'LAST_CERT' line: 20, col: 4
At block line: 48, col: 10.


сам скрипт:
Код:
   declare procedure LAST_CERT(ID_EQUIP integer, CALIBR_INT integer)
     returns(NUMBER_CERT varchar(25), LAST_DATE date, DEVICE_ID_CL integer)
      as begin

if (:CALIBR_INT is null) then :CALIBR_INT = -1;
   for execute statement

   'SELECT count(*) FROM CERTIFICATE ce left join CERTIFICATE b on ce.DEVICE_ID_CL = b.DEVICE_ID_CL and ce.DATE_OUT < b.DATE_OUT
   where b.DEVICE_ID_CL is NULL and ce.DEVICE_ID_CL in ('||ID_EQUIP||')
and (DATEADD(month, '||CALIBR_INT||', ce.DATE_OUT)<current_date) into :COUNT_REC;

   if (:COUNT_REC <> 0) then
     SELECT ce.NUMBER_CERT, ce.DATE_OUT, ce.DEVICE_ID_CL FROM CERTIFICATE ce
        left join CERTIFICATE b on ce.DEVICE_ID_CL = b.DEVICE_ID_CL and ce.DATE_OUT < b.DATE_OUT
      where b.DEVICE_ID_CL is NULL and ce.DEVICE_ID_CL in ('||ID_EQUIP||')
             and (DATEADD(month, '||CALIBR_INT||', ce.DATE_OUT)<current_date)
               into :NUMBER_CERT, LAST_DATE, :DEVICE_ID_CL;
   else
      begin
    :NUMBER_CERT = -1;
    :LAST_DATE = null;
    :DEVICE_ID_CL = -1;
   end'
  on external 'D:\Database\Plan.fdb'  as user 'SYSDBA' password 'masterkey'
  into :NUMBER_CERT, :LAST_DATE, :DEVICE_ID_CL
       do suspend;
     end
Не подскажите где, тут ошибка?

Последний раз редактировалось KBO; 12.11.2020 в 18:08.
KBO вне форума Ответить с цитированием
Старый 13.11.2020, 12:16   #6
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

0. C FireBird не работал.

1.
Цитата:
into :COUNT_REC;
мне кажется что было бы не плохо объявить данную переменную.

2.
Цитата:
Некоторые клиенты, особенно те, что позволяет пользователю отослать
несколько операторов сразу, могут потребовать от вас, окружить оператор
EXECUTE BLOCK строками SET TERM, как в этом примере:
Код:
set term #;
execute block (...)
as
begin
statement1;
statement2;
end
#
set term ;#
В качестве примера, в клиенте isql СУБД Firebird необходимо установить
терминатор отличный от «;», прежде чем ввести оператор EXECUTE BLOCK.
Если этого не сделать, ISQL попытается выполнить часть которая была
напечатана до того момента, как вы нажали клавишу Enter после строки с
точкой запятой «;».
программа — запись алгоритма на языке понятном транслятору
evg_m вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Как в одной форме вывести данные из двух таблиц по связующему полю. Znaharkms Microsoft Office Access 8 17.05.2018 00:06
Дан двумерный массив.Вывести строки,в которых нет нулевых элементов sasha1212 Общие вопросы C/C++ 7 01.06.2014 22:10
Как в report.rdlc в таблице вывести значения не одной таблицы,а нескольких таблиц Babur4iK C# (си шарп) 0 30.03.2014 20:31
Дана последовательность символов, состоящая из слов. Вывести на экран слова, в которых все символы повторяющиеся (строки на C) Роман64 Помощь студентам 0 22.12.2013 15:00
Паскаль(файлы). Дан текстовый файл f. Вывести все строки файла длина которых больше 30 символов. Артемdgty Помощь студентам 0 09.10.2013 00:37