Вход | Регистрация
 
1С:Предприятие :: 1С:Предприятие 7.7 и ранее

v7: странное поведение SQL запроса

v7: странное поведение SQL запроса
Я
   Владимир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'.
   trad
 
2 - 14.03.19 - 15:24
я текст не осилил, но если это делается из семерки, то не хватает в начале всего это действа
set nocount on
   Владимир1С
 
3 - 14.03.19 - 15:26
(2) Подавление вывода сообщений о изменённых строках?
   Владимир1С
 
4 - 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)
   trad
 
5 - 14.03.19 - 15:30
ну так не понятно affected или не affected
   Владимир1С
 
6 - 14.03.19 - 15:32
ругается на временную таблицу 

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

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

Список тем форума
Рекламное место пустует  Рекламное место пустует
ВНИМАНИЕ! Если вы потеряли окно ввода сообщения, нажмите Ctrl-F5 или Ctrl-R или кнопку "Обновить" в браузере.
Тема не обновлялась длительное время, и была помечена как архивная. Добавление сообщений невозможно.
Но вы можете создать новую ветку и вам обязательно ответят!
Каждый час на Волшебном форуме бывает более 2000 человек.