Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 скрипт в функцию  [new]
Дмитрий-ВРН
Member

Откуда: Воронеж
Сообщений: 17
Добрый день

возникла необходимость в связи с расширением информативности отчетов, перегнать запрос ("Ранее отчет"), в функцию
т.к. теперь необходима задача следующего задания

получить из сложного запроса некую таблицу (с этим нет проблем)

таблица:

A B C D ......
-----------------------------------------------------------
per.000000145034 1300145034 07 TS016162742 ..................
per.000000148484 1300148484 07 TS016161924 ..................
zm.ЗяКбАЦ000130 g302z00130 500 TS016162115 ..................
zm.ЗяКбАЦ000130 g310800130 500 TS016161371 ..................

и в момент формирования выполнять функцию по полю D - GETINFO(D)

необходима именно функция т.к. её код будет выполняться для многих других отчетов

код функции: Код оптимизирован (ветвление задействовано в связи с особенностью хранения данных по неким зонам) за исключением последнего select - но с ним сам разберусь. Мои попытки перегона скрипта в функцию не дали положительного результата а на работе советоваться нес кем, помогите пожалуйста !!!

----------------------------
declare @ORDKEY varchar(20),@QTY numeric (18,3),@placecnt int,@CONTAINER int,@caseid int,@TS varchar(20)
Create table #REZ (ORDERKEY varchar(20),QTY numeric (18,3),placecnt int ,CONTAINER int, caseid int,STOK varchar(10))
----------------------- получаем все заказы на ТСке
set @TS='ts016156434'
select distinct orderkey into #Kash from wh1.pickdetail where dropid = @TS

while exists(select top 1 orderkey from #Kash)
BEGIN
   set @ORDKEY = (select top 1 orderkey from #Kash)
   set @QTY = (SELECT SUM(p.qty*sk.stdnetwgt)FROM wh1.pickdetail AS p
             LEFT JOIN wh1.sku sk ON p.sku=sk.sku AND p.storerkey=sk.storerkey
             WHERE p.dropid = @TS and p.orderkey = @ORDKEY
             GROUP BY p.orderkey, p.dropid)
   ------------------------------------------------
   -- >>>>>>>
    Create table #ITG (placecnt int ,CONTAINER int, caseid varchar (20))
   ---
    IF ((select  Count( distinct D.dropid) from  wh1.pickdetail as P LEFT JOIN wh1.dropiddetail as DR on P.caseid = DR.childid right join wh1.dropid as D on DR.Dropid = D.dropid where P.orderkey =@ORDKEY
         )=1 and
        (select  Count( distinct P.caseid) from  wh1.pickdetail as P LEFT JOIN wh1.dropiddetail as DR on P.caseid = DR.childid right join wh1.dropid as D on DR.Dropid = D.dropid where P.orderkey =@ORDKEY
         )>1) 
    BEGIN
   -----
     insert #ITG
     select  D.placecnt as SummMEST, D.containercnt as SummCONTR, count( distinct P.caseid) as CountCASE
     from  wh1.pickdetail as P inner join wh1.dropiddetail as DR
                         on P.caseid = DR.childid
                            right join wh1.dropid as D
                         on DR.Dropid = D.dropid

     where P.orderkey =@ORDKEY and p.dropid =  @TS
     group by D.placecnt,D.containercnt
     ---- ПЕРЕМЕННЫЕ
     set @placecnt = (Select placecnt from #ITG)
     set @CONTAINER = (Select CONTAINER from #ITG)
     set @caseid = (Select caseid from #ITG) 
     
     insert into #REZ (ORDERKEY,QTY,placecnt,CONTAINER,caseid,STOK)
            values (@ORDKEY,@QTY,@placecnt,@CONTAINER,@caseid,'DEKOR зона')
   -----
   END
   ELSE BEGIN 
   -----
     insert #ITG
     SELECT distinct   (CASE WHEN ISNULL(d2.placecnt,'0')<>'0'    THEN d2.placecnt                       
                             WHEN ISNULL(d1.placecnt,'0')<>'0'    THEN d1.placecnt                       
                             WHEN ISNULL(d3.placecnt,'0')<>'0'    THEN d3.placecnt     ELSE '' END) as placecnt,                      
                       (CASE WHEN ISNULL(d2.containercnt,'0')<>'0'THEN d2.containercnt                      
                             WHEN ISNULL(d1.containercnt,'0')<>'0'THEN d1.containercnt                      
                             WHEN ISNULL(d3.containercnt,'0')<>'0'THEN d3.containercnt ELSE '' END) as containercnt,
                       p.caseid as SummCase
     FROM wh1.pickdetail p  LEFT JOIN wh1.dropiddetail d    ON p.caseid=d.childid                         
                            LEFT JOIN wh1.dropiddetail dd   ON d.dropid=dd.childid                         
                            LEFT JOIN wh1.dropiddetail ddd  ON dd.dropid=ddd.childid 
                            LEFT JOIN wh1.dropid d1         ON d1.dropid=dd.dropid 
                            LEFT JOIN wh1.dropid d2         ON d2.dropid=ddd.dropid
                            LEFT JOIN wh1.dropid d3         ON d3.dropid=d.dropid 
     where  orderkey = @ORDKEY and p.dropid =  @TS
     -------------------------------------
     set @placecnt = (Select sum(placecnt) from #ITG)
     set @CONTAINER = (Select sum(CONTAINER) from #ITG)
     set @caseid = (Select count(caseid) from #ITG) 
     
     insert into #REZ (ORDERKEY,QTY,placecnt,CONTAINER,caseid,STOK)
            values (@ORDKEY,@QTY,@placecnt,@CONTAINER,@caseid,'')

   END
   drop table #ITG
   -- <<<<<<<
   ---------------- чистим для перехода
   delete from #Kash where orderkey = @ORDKEY 
END
select sum(R.QTY),sum(R.placecnt),sum(R.CONTAINER),sum(R.caseid) from #REZ as R
---
drop table #REZ,#KASH
15 июл 13, 15:50    [14568467]     Ответить | Цитировать Сообщить модератору
 Re: скрипт в функцию  [new]
Дмитрий-ВРН
Member

Откуда: Воронеж
Сообщений: 17
Дмитрий-ВРН
Добрый день

возникла необходимость в связи с расширением информативности отчетов, перегнать запрос ("Ранее отчет"), в функцию
т.к. теперь необходима задача следующего задания

получить из сложного запроса некую таблицу (с этим нет проблем)

таблица:

A B C D ......
-----------------------------------------------------------
per.000000145034 1300145034 07 TS016162742 ..................
per.000000148484 1300148484 07 TS016161924 ..................
zm.ЗяКбАЦ000130 g302z00130 500 TS016162115 ..................
zm.ЗяКбАЦ000130 g310800130 500 TS016161371 ..................

и в момент формирования выполнять функцию по полю D - GETINFO(D)

необходима именно функция т.к. её код будет выполняться для многих других отчетов

код функции: Код оптимизирован (ветвление задействовано в связи с особенностью хранения данных по неким зонам) за исключением последнего select - но с ним сам разберусь. Мои попытки перегона скрипта в функцию не дали положительного результата а на работе советоваться нес кем, помогите пожалуйста !!!

----------------------------
declare @ORDKEY varchar(20),@QTY numeric (18,3),@placecnt int,@CONTAINER int,@caseid int,@TS varchar(20)
Create table #REZ (ORDERKEY varchar(20),QTY numeric (18,3),placecnt int ,CONTAINER int, caseid int,STOK varchar(10))
----------------------- получаем все заказы на ТСке
set @TS='ts016156434'
select distinct orderkey into #Kash from wh1.pickdetail where dropid = @TS

while exists(select top 1 orderkey from #Kash)
BEGIN
   set @ORDKEY = (select top 1 orderkey from #Kash)
   set @QTY = (SELECT SUM(p.qty*sk.stdnetwgt)FROM wh1.pickdetail AS p
             LEFT JOIN wh1.sku sk ON p.sku=sk.sku AND p.storerkey=sk.storerkey
             WHERE p.dropid = @TS and p.orderkey = @ORDKEY
             GROUP BY p.orderkey, p.dropid)
   ------------------------------------------------
   -- >>>>>>>
    Create table #ITG (placecnt int ,CONTAINER int, caseid varchar (20))
   ---
    IF ((select  Count( distinct D.dropid) from  wh1.pickdetail as P LEFT JOIN wh1.dropiddetail as DR on P.caseid = DR.childid right join wh1.dropid as D on DR.Dropid = D.dropid where P.orderkey =@ORDKEY
         )=1 and
        (select  Count( distinct P.caseid) from  wh1.pickdetail as P LEFT JOIN wh1.dropiddetail as DR on P.caseid = DR.childid right join wh1.dropid as D on DR.Dropid = D.dropid where P.orderkey =@ORDKEY
         )>1) 
    BEGIN
   -----
     insert #ITG
     select  D.placecnt as SummMEST, D.containercnt as SummCONTR, count( distinct P.caseid) as CountCASE
     from  wh1.pickdetail as P inner join wh1.dropiddetail as DR
                         on P.caseid = DR.childid
                            right join wh1.dropid as D
                         on DR.Dropid = D.dropid

     where P.orderkey =@ORDKEY and p.dropid =  @TS
     group by D.placecnt,D.containercnt
     ---- ПЕРЕМЕННЫЕ
     set @placecnt = (Select placecnt from #ITG)
     set @CONTAINER = (Select CONTAINER from #ITG)
     set @caseid = (Select caseid from #ITG) 
     
     insert into #REZ (ORDERKEY,QTY,placecnt,CONTAINER,caseid,STOK)
            values (@ORDKEY,@QTY,@placecnt,@CONTAINER,@caseid,'DEKOR зона')
   -----
   END
   ELSE BEGIN 
   -----
     insert #ITG
     SELECT distinct   (CASE WHEN ISNULL(d2.placecnt,'0')<>'0'    THEN d2.placecnt                       
                             WHEN ISNULL(d1.placecnt,'0')<>'0'    THEN d1.placecnt                       
                             WHEN ISNULL(d3.placecnt,'0')<>'0'    THEN d3.placecnt     ELSE '' END) as placecnt,                      
                       (CASE WHEN ISNULL(d2.containercnt,'0')<>'0'THEN d2.containercnt                      
                             WHEN ISNULL(d1.containercnt,'0')<>'0'THEN d1.containercnt                      
                             WHEN ISNULL(d3.containercnt,'0')<>'0'THEN d3.containercnt ELSE '' END) as containercnt,
                       p.caseid as SummCase
     FROM wh1.pickdetail p  LEFT JOIN wh1.dropiddetail d    ON p.caseid=d.childid                         
                            LEFT JOIN wh1.dropiddetail dd   ON d.dropid=dd.childid                         
                            LEFT JOIN wh1.dropiddetail ddd  ON dd.dropid=ddd.childid 
                            LEFT JOIN wh1.dropid d1         ON d1.dropid=dd.dropid 
                            LEFT JOIN wh1.dropid d2         ON d2.dropid=ddd.dropid
                            LEFT JOIN wh1.dropid d3         ON d3.dropid=d.dropid 
     where  orderkey = @ORDKEY and p.dropid =  @TS
     -------------------------------------
     set @placecnt = (Select sum(placecnt) from #ITG)
     set @CONTAINER = (Select sum(CONTAINER) from #ITG)
     set @caseid = (Select count(caseid) from #ITG) 
     
     insert into #REZ (ORDERKEY,QTY,placecnt,CONTAINER,caseid,STOK)
            values (@ORDKEY,@QTY,@placecnt,@CONTAINER,@caseid,'')

   END
   drop table #ITG
   -- <<<<<<<
   ---------------- чистим для перехода
   delete from #Kash where orderkey = @ORDKEY 
END
select sum(R.QTY),sum(R.placecnt),sum(R.CONTAINER),sum(R.caseid) from #REZ as R
---
drop table #REZ,#KASH
. sql server 2005
15 июл 13, 16:05    [14568581]     Ответить | Цитировать Сообщить модератору
 Re: скрипт в функцию  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Дмитрий-ВРН,

Смотрим документацию по функциям:
BOL -> CREATE FUNCTION
The following statements are valid in a function:
  • Assignment statements.
  • Control-of-Flow statements except TRY...CATCH statements.
  • DECLARE statements defining local data variables and local cursors.
  • SELECT statements that contain select lists with expressions that assign values to local variables.
  • Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
  • INSERT, UPDATE, and DELETE statements modifying local table variables.
  • EXECUTE statements calling extended stored procedures.

  • Видим, что конструкции CREATE TABLE, DROP TABLE в функциях не допускаются.

    Посему — замените временные таблицы на табличные переменные, и вы сможете поместить ваш код в тело функции.
    15 июл 13, 16:24    [14568754]     Ответить | Цитировать Сообщить модератору
     Re: скрипт в функцию  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Дмитрий-ВРН
    sql server 2005

    Вызов функции — через оператор CROSS/OUTER APPLY.
    15 июл 13, 16:26    [14568765]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить