Имя: Пароль:
1C
1C 7.7
v7: странное поведение SQL запроса
, ,
0 Владимир1С
 
14.03.19
15:14
Собственно, запрос не представляет из себя чего то зубодробительного, Цикл, каждый раз новый текст, текст определяется номером итерации через SELECT.

Запрос:
-- Это даты последних покупок по всем базам.
-- данная таблица присоединяется к таблице, даты которой вычисляются в текущей базе по документам.
-- В основном запросе подтягиваются основные реквизиты клиента - телефоны, адреса, даты рождения.
-- ЭТО ТОЛЬКО ДАТЫ, ЕЩЁ ЕСТЬ   СУММЫ  НАКОПЛЕНИЯ, ДЛЯ НИХ ЕСТЬ ОТДЕЛЬНЫЙ ЗАПРОС В 1С , ЕГО ТОЖЕ НАДО ПЕРЕНЕСТИ В SQL
declare @countBZ int , @cou int , @zaprosText Char(7999) , @Строка_ГодТекущейБазы char(4) , @НачалоГодаИстории int
-- @countBZ  счётчик циклов блоков запроса , которые должны выполняться в определённой последовательности.
set @countBZ = 10 -- минимальное количество циклов, служебных, для формирования фильтра -- количество циклов будем считать в 1С, ##КоличествоЦиклов## только , по моему, это не нужно.

set @cou = 0;

set @Строка_ГодТекущейБазы = 2019;--'2019';-- этот год будем вписывать в текст запроса как заменяемую комбинацию символов 2019

set @НачалоГодаИстории = 2015;

While @cou<=@countBZ
    Begin
            
        --Print @cou


            set @zaprosText =
                (Case @cou


             when 1  then  'if object_id(''tempdb..#last_buy_temp'') is not null drop table #last_buy_temp'
            
             when 2  then  'create table #last_buy_temp (CODE char(9), Last_buy_date datetime)'

             when 3  then  'use rozn_2019
                            insert into #last_buy_temp -- ПОЛУЧЕНИЕ ДАТ ПОКУПОК ИЗ ТЕКУЩЕЙ БАЗЫ
                            select
                                SC46.CODE,
                                  SC46.SP3075 as Last_buy_date
                            from --$Справочник.Клиенты as СпрКлн
                                SC46
                            where --$СпрКлн.ДатаВыдачи>=:НачалоГодаИстории
                                Year(sc46.sp3075)>=2015--@НачалоГодаИстории
                                and LTrim(RTrim(SP4486))<>'''' '  
             --when 3  then  'use rozn_2015 -- 3 и 2015 установим из 1С
            when 4  then  'use rozn_2015 -- 3 и 2015 установим из 1С
        
                            insert into #last_buy_temp
                                select
                                    SC46.CODE,
                                    --max(convert(datetime,LEFT(РегДск.DATE_TIME_IDDOC,8),102)) as Last_buy_date
                                    max(convert(datetime,LEFT( РегДск.DATE_TIME_IDDOC , 8) , 102 )) as Last_buy_date
                               from
                                   RA4580 as РегДск
                                        -- inner join $Справочник.Клиенты as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                        inner join SC46 as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                   where
                                       РегДск.IDDocDef<>4666 --$ВидДокумента.ВводОстаткаНакопленнойСуммы
                                       and РегДск.DebKred=0
                               group by CODE '



             --when 3  then  'use rozn_2015 -- 4 и 2016 установим из 1С
            when 5  then  'use rozn_2016 -- 4 и 2016 установим из 1С
        
                            insert into #last_buy_temp
                                select
                                    SC46.CODE,
                                    --max(convert(datetime,LEFT(РегДск.DATE_TIME_IDDOC,8),102)) as Last_buy_date
                                    max(convert(datetime,LEFT( РегДск.DATE_TIME_IDDOC , 8) , 102 )) as Last_buy_date
                               from
                                   RA4580 as РегДск
                                        -- inner join $Справочник.Клиенты as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                        inner join SC46 as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                   where
                                       РегДск.IDDocDef<>4666 --$ВидДокумента.ВводОстаткаНакопленнойСуммы
                                       and РегДск.DebKred=0
                               group by CODE '



             --when 3  then  'use rozn_2015 -- 5 и 2017 установим из 1С
            when 6  then  'use rozn_2017 -- 5 и 2017 установим из 1С
        
                            insert into #last_buy_temp
                                select
                                    SC46.CODE,
                                    --max(convert(datetime,LEFT(РегДск.DATE_TIME_IDDOC,8),102)) as Last_buy_date
                                    max(convert(datetime,LEFT( РегДск.DATE_TIME_IDDOC , 8) , 102 )) as Last_buy_date
                               from
                                   RA4580 as РегДск
                                        -- inner join $Справочник.Клиенты as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                        inner join SC46 as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                   where
                                       РегДск.IDDocDef<>4666 --$ВидДокумента.ВводОстаткаНакопленнойСуммы
                                       and РегДск.DebKred=0
                               group by CODE '



             --when 3  then  'use rozn_2015 -- 6 и 2018 установим из 1С
            when 7  then  'use rozn_2018 -- 6 и 2018 установим из 1С
        
                            insert into #last_buy_temp
                                select
                                    SC46.CODE,
                                    --max(convert(datetime,LEFT(РегДск.DATE_TIME_IDDOC,8),102)) as Last_buy_date
                                    max(convert(datetime,LEFT( РегДск.DATE_TIME_IDDOC , 8) , 102 )) as Last_buy_date
                               from
                                   RA4580 as РегДск
                                        -- inner join $Справочник.Клиенты as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                        inner join SC46 as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                   where
                                       РегДск.IDDocDef<>4666 --$ВидДокумента.ВводОстаткаНакопленнойСуммы
                                       and РегДск.DebKred=0
                               group by CODE '



             --when 3  then  'use rozn_2015 -- 7 и 2019 установим из 1С
            when 8  then  'use rozn_2019 -- 7 и 2019 установим из 1С
        
                            insert into #last_buy_temp
                                select
                                    SC46.CODE,
                                    --max(convert(datetime,LEFT(РегДск.DATE_TIME_IDDOC,8),102)) as Last_buy_date
                                    max(convert(datetime,LEFT( РегДск.DATE_TIME_IDDOC , 8) , 102 )) as Last_buy_date
                               from
                                   RA4580 as РегДск
                                        -- inner join $Справочник.Клиенты as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                        inner join SC46 as СпрКлн on СпрКлн.ID=РегДск.Клиент
                                   where
                                       РегДск.IDDocDef<>4666 --$ВидДокумента.ВводОстаткаНакопленнойСуммы
                                       and РегДск.DebKred=0
                               group by CODE '

--


         --when 7  then  'use rozn_'+@Строка_ГодТекущейБазы+
         when 9  then  'use rozn_'+@Строка_ГодТекущейБазы+     -- ##счётчикЦиклов ## формируется в 1С
                           ' if object_id(''tempdb..#LastBuyDateTempTable'') is not null
                            DROP TABLE #LastBuyDateTempTable
                            create table #LastBuyDateTempTable (CODE char(9), Last_buy_date datetime)'

         --when 8  then  'insert into #LastBuyDateTempTable
        when 10  then  'insert into #LastBuyDateTempTable
                                select
                                    CODE as КодКлиента,
                                    max(Last_buy_date) as ДатаПоследнейПокупки
                                from
                                    #last_buy_temp
                                where
                                    Last_buy_date>=:НачалоГодаИстории
                                group by
                                    CODE'
        end)
        
        Print @zaprosText
        Print @cou
        Execute(@zaprosText)

        set @cou = @cou+1 --ntBZ = @countBZ-1
    End

А вот результат :

0
if object_id('tempdb..#last_buy_temp') is not null drop table #last_buy_temp                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
1
create table #last_buy_temp (CODE char(9), Last_buy_date datetime)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
2
use rozn_2019
                            insert into #last_buy_temp -- ПОЛУЧЕНИЕ ДАТ ПОКУПОК ИЗ ТЕКУЩЕЙ БАЗЫ
                            select
                                SC46.CODE,
                                  SC46.SP3075 as Last_buy_date
                            from --$Справочник.Клиенты as СпрКлн
                                SC46
                            where --$СпрКлн.ДатаВыдачи>=:НачалоГодаИстории
                                Year(sc46.sp3075)>=2015--@НачалоГодаИстории
                                and LTrim(RTrim(SP4486))<>''
1 Владимир1С
 
14.03.19
15:16
0
if object_id('tempdb..#last_buy_temp') is not null drop table #last_buy_temp                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
1
create table #last_buy_temp (CODE char(9), Last_buy_date datetime)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
2
use rozn_2019
                            insert into #last_buy_temp -- ПОЛУЧЕНИЕ ДАТ ПОКУПОК ИЗ ТЕКУЩЕЙ БАЗЫ
                            select
                                SC46.CODE,
                                  SC46.SP3075 as Last_buy_date
                            from --$Справочник.Клиенты as СпрКлн
                                SC46
                            where --$СпрКлн.ДатаВыдачи>=:НачалоГодаИстории
                                Year(sc46.sp3075)>=2015--@НачалоГодаИстории
                                and LTrim(RTrim(SP4486))<>''                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
3
Msg 208, Level 16, State 0, Line 2
Invalid object name '#last_buy_temp'.
2 trad
 
14.03.19
15:24
я текст не осилил, но если это делается из семерки, то не хватает в начале всего это действа
set nocount on
3 Владимир1С
 
14.03.19
15:26
(2) Подавление вывода сообщений о изменённых строках?
4 Владимир1С
 
14.03.19
15:27
Первые три итерации цикла, объединяю в один запрос, всё отрабатывает:

if object_id('tempdb..#last_buy_temp') is not null drop table #last_buy_temp                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

create table #last_buy_temp (CODE char(9), Last_buy_date datetime)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

use rozn_2019
                            insert into #last_buy_temp -- ПОЛУЧЕНИЕ ДАТ ПОКУПОК ИЗ ТЕКУЩЕЙ БАЗЫ
                            select
                                SC46.CODE,
                                  SC46.SP3075 as Last_buy_date
                            from --$Справочник.Клиенты as СпрКлн
                                SC46
                            where --$СпрКлн.ДатаВыдачи>=:НачалоГодаИстории
                                Year(sc46.sp3075)>=2015--@НачалоГодаИстории
                                and LTrim(RTrim(SP4486))<>''

результат:

(275370 row(s) affected)
5 trad
 
14.03.19
15:30
ну так не понятно affected или не affected
6 Владимир1С
 
14.03.19
15:32
ругается на временную таблицу

Msg 208, Level 16, State 0, Line 3
Invalid object name '#last_buy_temp'.

не видит эту таблицу.
7 Владимир1С
 
14.03.19
15:33
set no count on    не помог.
8 Владимир1С
 
14.03.19
15:42
Первые три итерации :

         when 1  then  'if object_id(''tempdb..#last_buy_temp'') is not null drop table #last_buy_temp'
            
             when 2  then  'create table #last_buy_temp (CODE char(9), Last_buy_date datetime) set nocount on'

             when 3  then  'use rozn_2019
                            insert into #last_buy_temp -- ПОЛУЧЕНИЕ ДАТ ПОКУПОК ИЗ ТЕКУЩЕЙ БАЗЫ
                            select
                                SC46.CODE,
                                  SC46.SP3075 as Last_buy_date
                            from --$Справочник.Клиенты as СпрКлн
                                SC46
                            where --$СпрКлн.ДатаВыдачи>=:НачалоГодаИстории
                                Year(sc46.sp3075)>=2015--@НачалоГодаИстории
                                and LTrim(RTrim(SP4486))<>''''  '


раздельно в третьей итерации  #last_buy_temp не видит, если объединить в один запрос - всё ОК!
9 Владимир1С
 
14.03.19
17:22
Откопал. Временные таблицы видны в операциях Execute только те, которые созданы внутри сессии Execute. И только они. Ничего другого временного внутри Execute не видно.
10 Mikeware
 
14.03.19
17:24
а если таблицы с ## ?
11 Владимир1С
 
14.03.19
17:42
На сегодня мне хватило. в общем, всё временное не заходит и разрушается при окончании сессии.
12 Mikeware
 
14.03.19
17:44
(11) ## должны быть видны в течение жизни подключения
13 Владимир1С
 
14.03.19
17:45
Завтра проверю
14 trad
 
15.03.19
10:44
(12) проблема будет в том, что видны всем, и соседним подключениям тоже, выполняющим такой же запрос
15 dk
 
15.03.19
10:51
(14) @@spid засунуть в название ##
16 Mikeware
 
15.03.19
11:01
(14) (15) или вместо "осмысленных" имен ВТ использовать GUID