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

v7: Нужна помощь с прямым запросом

v7: Нужна помощь с прямым запросом
Я
   zenon46
 
19.02.20 - 16:24
Доброго всем дня!
Нужна помощь с запросом, главная проблема состоит в том, что на вход в запрос подается список из 3500 элементов номенклатуры, а на выходе получаем 412, где-то часть теряется, что не есть хорошо(
Суть самого запроса такая, по списку номенклатуры
1. нужно найти партию сформированную определенным типом документа
2. по этому же списку нужно получить остаток на дату
3. по этому же списку нужно посчитать усредненную себестоимость
Вот текст запроса, подзапросы идут в том же порядке как и перечислены тут

    |SELECT
    |    temp.val,
    |    Спр.Code                                         AS Код,    
    |    subsel.Номенклатура                             AS [Номенклатура $Справочник.Номенклатура],
    |    $Спр.БазоваяЕдиница                             AS [БазоваяЕдиница $Справочник.Единицы],
    |   Isnull(Рег.Date_Time_IDDOC,'-')                    [ДатаПрих],
    |    $Рег.СуммаРуб                                     AS [СуммаРуб $Число],
    |    $Рег.Количество                                 AS [Количество $Число],    
    |    ($Рег.СуммаРуб/$Рег.Количество)                 AS [ЦенаПослПрих $Число],
    |    $Рег.Партия                                     AS [Партия $Справочник.Партии],
    |    CAST(LEFT(Рег.Date_Time_IDDoc, 8)                 AS DateTime) as ДатаПослПрих,
    |   $Спр.МинОстаток                                 AS [МинОстаток $Число],
    |   $Спр.КолЗаказа                                     AS [КолЗаказа $Число],
    |    balance.Количество                                 AS [Остаток $Число],
    |   CASE WHEN ($Рег.СуммаРуб IS NULL) THEN 0 ELSE (sebestoimost.СуммаРуб/sebestoimost.Количество) END AS [Себестоимость $Число],
    |   CASE WHEN ($Рег.СуммаРуб IS NULL) THEN 0 ELSE 1 END AS [Признак $Число]    
    |FROM
    |    #TempTable
    |    temp left join
    |(
    |SELECT
    |    $Рег.Номенклатура AS [Номенклатура],
    |    MAX(Рег.Date_Time_IDDOC) AS ДатаПослПрихода
    |FROM
    |    $Регистр.ПартииНаличие as Рег
    |INNER JOIN
    |    _1Sjourn as ЖурналДок ON ЖурналДок.IDDoc = Рег.IDDoc    
    |INNER JOIN
    |    $Справочник.Партии AS СпрП ON СпрП.ID = $Рег.Партия
    |INNER JOIN
    |    $Справочник.Контрагенты AS СпрК ON СпрК.ID = $СпрП.Поставщик
    |WHERE
    |   ЖурналДок.iddocdef = $ВидДокумента.ПоступлениеТМЦ_Склад AND    
    |    $Рег.Номенклатура IN (SELECT val FROM #TempTable) AND    
    |    $Рег.Фирма = :ВбрФирма AND    
    |    $СпрП.Поставщик NOT IN (SELECT val FROM #TableKontr)    
    |GROUP BY
    |    $Рег.Номенклатура
    |) subsel ON temp.val = subsel.Номенклатура        
    |LEFT JOIN
    |(
    |SELECT
    |    Рег.Номенклатура AS [Номенклатура],    
    |    Рег.КоличествоОстаток AS Количество
    |FROM
    |    $РегистрОстатки.ОстаткиТМЦ(:ВыбДата~,,Фирма = :ВбрФирма AND Номенклатура IN (SELECT val FROM #TempTable),(Номенклатура),(Количество)) AS Рег
    |) balance ON temp.val = balance.Номенклатура
    |LEFT JOIN
    |(
    |SELECT
    |    Рег.Номенклатура AS [Номенклатура],    
    |    Рег.КоличествоОстаток AS Количество,
    |    Рег.СуммаРубОстаток AS СуммаРуб
    |FROM
    |   $РегистрОстатки.ПартииНаличие(:ВыбДата~,,Фирма = :ВбрФирма AND Номенклатура IN (SELECT val FROM #TempTable),(Номенклатура),(Количество,СуммаРуб)) AS Рег
    |) sebestoimost ON temp.val = sebestoimost.Номенклатура    
    |INNER JOIN
    |    $Справочник.Номенклатура AS Спр ON Спр.ID = temp.val     
    |LEFT JOIN
    |    $Регистр.ПартииНаличие AS Рег ON $Рег.Номенклатура = subsel.Номенклатура AND Рег.Date_time_iddoc=subsel.ДатаПослПрихода    
    |ORDER BY
    |    Спр.Code
    |";
   mikecool
 
1 - 19.02.20 - 16:25
кастую в соединения
   mikecool
 
2 - 19.02.20 - 16:25
ON Спр.ID = temp.val  - вот здесь скорее всего
   Ёпрст
 
3 - 19.02.20 - 16:34
(0)вот это поменяй на  left и увидишь, где Спр.ID<> temp.val
  |INNER JOIN 
    |    $Справочник.Номенклатура AS Спр ON Спр.ID = temp.val
   zenon46
 
4 - 19.02.20 - 16:37
(3) поменял..результат тот же
   Ёпрст
 
5 - 19.02.20 - 16:39
(4) чудес не бывает или одна из черепашек врёт

  |SELECT 
    |    temp.val
    |FROM 
    |    #TempTable 

так, сколько видишь элементов в результате ?
   zenon46
 
6 - 19.02.20 - 16:42
(5) Так :
SELECT
val
FROM
#TempTable

Все 3500
   Ёпрст
 
7 - 19.02.20 - 16:44
(6)
а так ?
|SELECT
    |    temp.val
    |FROM 
    |    #TempTable 

 |INNER JOIN 
    |    $Справочник.Номенклатура AS Спр ON Спр.ID = temp.val
   zenon46
 
8 - 19.02.20 - 16:47
(7) Так :
SELECT
    val
FROM
    #TempTable 

INNER JOIN 
    $Справочник.Номенклатура AS Спр ON Спр.ID = val

Все 3500
   Ёпрст
 
9 - 19.02.20 - 16:58
(9) ну и добавляй всё остальное
   zenon46
 
10 - 19.02.20 - 16:59
Урезал запрос до такого : в резултатх стало еще больше чем в списке....странно

SELECT
 temp.val, 
 Спр.Code           AS Код, 
 subsel.Номенклатура        AS [Номенклатура $Справочник.Номенклатура], 
 $Спр.БазоваяЕдиница        AS [БазоваяЕдиница $Справочник.Единицы],
   Isnull(Рег.Date_Time_IDDOC,'-')        [ДатаПрих],
 $Рег.СуммаРуб          AS [СуммаРуб $Число],
 $Рег.Количество            AS [Количество $Число], 
 ($Рег.СуммаРуб/$Рег.Количество)        AS [ЦенаПослПрих $Число],
 $Рег.Партия             AS [Партия $Справочник.Партии],
 CAST(LEFT(Рег.Date_Time_IDDoc, 8)     AS DateTime) as ДатаПослПрих,
   $Спр.МинОстаток            AS [МинОстаток $Число],
   $Спр.КолЗаказа          AS [КолЗаказа $Число],

   CASE WHEN ($Рег.СуммаРуб IS NULL) THEN 0 ELSE 1 END AS [Признак $Число]
FROM
#TempTable
temp left join
(
SELECT
 $Рег.Номенклатура AS [Номенклатура],
 MAX(Рег.Date_Time_IDDOC) AS ДатаПослПрихода
FROM
 $Регистр.ПартииНаличие as Рег
INNER JOIN
 _1Sjourn as ЖурналДок ON ЖурналДок.IDDoc = Рег.IDDoc 
INNER JOIN
 $Справочник.Партии AS СпрП ON СпрП.ID = $Рег.Партия
INNER JOIN
 $Справочник.Контрагенты AS СпрК ON СпрК.ID = $СпрП.Поставщик
WHERE
   ЖурналДок.iddocdef = $ВидДокумента.ПоступлениеТМЦ_Склад AND 
$Рег.Номенклатура IN (SELECT val FROM #TempTable) AND
$Рег.Фирма = :ВбрФирма AND
$СпрП.Поставщик NOT IN (SELECT val FROM #TableKontr)
GROUP BY
$Рег.Номенклатура
) subsel ON temp.val = subsel.Номенклатура  
INNER JOIN
 $Справочник.Номенклатура AS Спр ON Спр.ID = temp.val  
LEFT JOIN
 $Регистр.ПартииНаличие AS Рег ON $Рег.Номенклатура = subsel.Номенклатура AND  Рег.Date_time_iddoc=subsel.ДатаПослПрихода
ORDER BY
 Спр.Code
   Ёпрст
 
11 - 19.02.20 - 17:00
(10) че го странного то ? left join же
   Ёпрст
 
12 - 19.02.20 - 17:01
у тя в temp Номенклатура Валенки

а в подзапросе или в регистре

Номенклатура Валенки Количество 10
Номенклатура Валенки Количество 20
Номенклатура Валенки Количество 30

вот и будет 3 строки
   zenon46
 
13 - 19.02.20 - 17:05
(12) ды в подзапросе должна быть одна запись по каждой номенклатуре за счет : MAX(Рег.Date_Time_IDDOC) AS ДатаПослПрихода И GROUP BY
$Рег.Номенклатура
   zenon46
 
14 - 19.02.20 - 17:08
Вот : https://prnt.sc/r4mumc  видим одну запись на один товар
   Sserj
 
15 - 19.02.20 - 17:08
(10) 1. Собери "INNER JOIN"-ы вверху а left-ы внизу, планировщику будет намного легче и меньше вероятности
ошибки что inner-ом сложишь не то что хотел.
FROM
#TempTable as temp
INNER JOIN
 $Справочник.Номенклатура AS Спр ON Спр.ID = temp.val 
left join ...

2. Переименуй все алиасы в уникальные. У тебя несколько раз используется Рег, в доке к 1С++ сказано:
Замечание! На текст запроса накладывается дополнительное ограничение: псевдонимы таблиц, имена которых транслируются из метаимен, не должны повторяться, иначе трансляция метаимени полей через псевдоним таблицы может быть неверной.

Пару раз напарывался, хоть все верно написано, хоть тресни, а выдается ерунда.
   Sserj
 
16 - 19.02.20 - 17:14
(10) Увеличение строк явно дает
..
LEFT JOIN
 $Регистр.ПартииНаличие AS Рег ON $Рег.Номенклатура = subsel.Номенклатура AND  Рег.Date_time_iddoc=subsel.ДатаПослПрихода
ORDER BY
 Спр.Code

Видимо в каком то поступлении товар создает несколько партий, возможно по разной цене.
   zenon46
 
17 - 19.02.20 - 17:16
(16) да, такое вполне возмжно....буду искать в этом направлении
   zenon46
 
18 - 20.02.20 - 09:03
Что удалось выяснить, собирал попорядку все подзапросы, и как только в SELECT лист я добавляю, в выборке остается хрен пойми что от всего списка :

CASE WHEN ($Рег.СуммаРуб IS NULL) THEN 0 ELSE (sebestoimost.СуммаРуб/sebestoimost.Количество) END AS [Себестоимость $Число]
   zenon46
 
19 - 20.02.20 - 10:16
Все дело в операции деления...как-то не понятно оно рабоатет, проверил так :
   zenon46
 
20 - 20.02.20 - 10:16
Вот запрос :
SELECT
    Спр.Code AS Код,
        РегП3.КоличествоОстаток AS Количество,
        РегП3.СуммаРубОстаток AS СуммаРуб,
       РегП3.СуммаРубОстаток/РегП3.КоличествоОстаток AS Себестоимость
    FROM
       $РегистрОстатки.ПартииНаличие(:ВыбДата~,,Фирма = :ВбрФирма AND Номенклатура IN (SELECT val FROM #TempTable),(Номенклатура),(Количество,СуммаРуб)) AS РегП3
    LEFT JOIN
        $Справочник.Номенклатура AS Спр ON Спр.ID = РегП3.Номенклатура
    ORDER BY
        Спр.Code

С делением 25 строк в выборке, убираем деление 1184
   trad
 
21 - 20.02.20 - 10:25
деление на 0
оно выдается сервером не ошибкой а предупреждением
в 1с++, обдцрекордсет это не понимает и получает только часть результата, при этом никак не ругаясь
получить текст запроса Отладка(1)
полученный текст выполни QA или SMS - все увидишь сам
   trad
 
22 - 20.02.20 - 10:26
РегП3.СуммаРубОстаток / nullif(РегП3.КоличествоОстаток, 0)
   zenon46
 
23 - 20.02.20 - 10:27
(22) я пробовал так CASE WHEN (Рег.КоличествоОстаток IS NULL) THEN 0 ELSE (Рег.СуммаРубОстаток/Рег.КоличествоОстаток) END AS Себестоиомсть, один хрен
вот что в трансляции запроса :
{call sp_executesql(N'SELECT
 Рег.Номенклатура AS [Номенклатура],  
 Рег.КоличествоОстаток AS Количество,
 Рег.СуммаРубОстаток AS СуммаРуб,
CASE WHEN (Рег.КоличествоОстаток IS NULL) THEN 0 ELSE (Рег.СуммаРубОстаток/Рег.КоличествоОстаток) END AS Себестоиомсть
FROM
   (
select
rg328_vt.sp331 as Номенклатура,
sum(rg328_vt.sp342) as КоличествоОстаток,
sum(rg328_vt.sp343) as СуммаРубОстаток
from rg328 as rg328_vt (nolock)
where rg328_vt.period=@_CP_2
and (rg328_vt.sp4061 = @ВбрФирма0 AND rg328_vt.sp331 IN (SELECT val FROM #TempTable))
group by
rg328_vt.sp331
having
sum(rg328_vt.sp342) <> 0 or
sum(rg328_vt.sp343) <> 0
) as Рег', N'@ВбрФирма0 char(9),@_CP_2 smalldatetime'
,'     1   ',{d '2020-02-01'})}
   trad
 
24 - 20.02.20 - 10:28
Рег.КоличествоОстаток у тебя не null, а 0
   zenon46
 
25 - 20.02.20 - 10:28
(22) фак, сработало....можно объяснить почему ?
   trad
 
26 - 20.02.20 - 10:30
на ноль делить нельзя...
на нул - можно

(с)Мюллер )))
   zenon46
 
27 - 20.02.20 - 10:32
(26) да, но ОНО ошибки то не выдавало, когда пробовал принудительно select 1/0 - была ошибка
   trad
 
28 - 20.02.20 - 10:34
повторю, проведи эти эксперименты в QA или SMS - все увидишь сам
   zenon46
 
29 - 20.02.20 - 10:34
(28) спасибо, поправил верхний запрос из (1) и все заработало как надо....


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