Интересный запрос

1. Карина К (EvaMendes) 04.09.14 16:48 Сейчас в теме
Имеется два источника данных. 1й- вложенный запрос, 2й- РегистрСведений.ЦеныНоменклатурыСрезПоследних.
на вход передается список контрагентов, дата, наименование производителя(Доп.свойство справочника Номенклатура). В случае если во вложенном запросе есть цена по данному контрагенту, берем из вложенного запроса, если нету, берем из 2го источника. На выходе нужно получить дерево, где группировки: контрагент, затем иерархия номенклатур.
Пробовала писать так:
ВЫБРАТЬ
		|	ЗначенияСвойствОбъектов.Значение КАК Производитель,
		|	ВложенныйЗапрос.Валюта,
		|	ВложенныйЗапрос.Контрагент КАК Контрагент,
		|	ВложенныйЗапрос.Номенклатура КАК Номенклатура,
		|	ВложенныйЗапрос.НоменклатураАртикул,
		|	ЗначенияСвойствОбъектов.Значение,
		|	ВЫБОР
		|		КОГДА ВложенныйЗапрос.Цена ЕСТЬ NULL 
		|			ТОГДА ЦеныНоменклатурыСрезПоследних.Цена
		|		ИНАЧЕ ВложенныйЗапрос.Цена
		|	КОНЕЦ КАК Цена
		|ИЗ
		|	РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
		|		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
		|			ЦеныНоменклатурыСрезПоследних.Цена КАК Цена,
		|			ЦеныНоменклатурыСрезПоследних.Валюта КАК Валюта,
		|			ЦеныНоменклатурыСрезПоследних.ТипЦен КАК ТипЦен,
		|			ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.Контрагент КАК Контрагент,
		|			ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
		|			ЦеныНоменклатурыСрезПоследних.Номенклатура.Артикул КАК НоменклатураАртикул
		|		ИЗ
		|			РегистрСведений.ТипыЦенПоГруппамНоменклатурыДляПокупателей.СрезПоследних(&Дата, Контрагент В ИЕРАРХИИ (&Контрагент)) КАК ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних
		|				ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ) КАК ЦеныНоменклатурыСрезПоследних
		|				ПО ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.ТипЦен = ЦеныНоменклатурыСрезПоследних.ТипЦен
		|					И ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.НоменклатурнаяЦеноваяГруппа.Ссылка = ЦеныНоменклатурыСрезПоследних.Номенклатура.ЦеноваяГруппа.Ссылка) КАК ВложенныйЗапрос
		|		ПО ВложенныйЗапрос.Номенклатура.Ссылка = ЗначенияСвойствОбъектов.Объект.Ссылка
		|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
		|		ПО ЦеныНоменклатурыСрезПоследних.Номенклатура = ЗначенияСвойствОбъектов.Объект
		|ГДЕ
		|	ЗначенияСвойствОбъектов.Свойство = &Свойство
		|	И ЗначенияСвойствОбъектов.Значение В(&Значение)
		|
		|УПОРЯДОЧИТЬ ПО
		|	Контрагент
		|ИТОГИ ПО
		|	Контрагент,
		|	Номенклатура ТОЛЬКО ИЕРАРХИЯ
...Показать Скрыть


ошибка в том, что если задать одного контрагента, то дерево разобьется на две группировки: МойКонтрагент и пусто, а нужно чтобы все было под одним контрагентом.
Ответы
2. Xer shi (Xershi) 274 04.09.14 17:05 Сейчас в теме
(1) EvaMendes, я бы порекомендовал воспользоваться консолью запросов, там увидите какие данные попадают и поможет исправить запрос под требуемое условие.
3. Карина К (EvaMendes) 04.09.14 17:44 Сейчас в теме
(2) Xershi, я пользуюсь, и вижу что получается (а получается не то, что нужно), но не могу придумать что сделать чтобы было так как мне нужно. Повторюсь: нужно выгружать цены по всей номенклатуре в некотором фиксированном типе цен (ОПТ) если нет записи в регистре ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних, если запись есть, нужно выгружать из этого регистра.
Пример:
регистр ЦеныНоменклатуры.СрезПоследних:
    ТипЦен Номенклатура цена валюта
    [LIST]1. ОПТ Вал 100 руб

    2. МИН вал 50 руб
    3. Розн Тиски 2000 руб
    4. ОПТ Тиски 1500 руб
    5. Розн Крепеж ж1 200 руб
    6. ОПТ Крепеж ж1 190 руб
    7. ОПТ каска 100 руб

Некоторый вложенный запрос (регистр ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних ОБЪЕДИНЕНИЕ ЦеныНоменклатурыСрезПоследних):
    Контрагент Номенклатура Цена Валюта
    1. Иванов Вал 80 руб
    2. Иванов каска 30 руб
    3. Петров Крепеж ж1 150 руб
    4. Петров Вал 170 руб

В итоге должно получиться(с группировками по контрагенту и номенклатуре):
Иванов
Строительное оборудование
Собственное
Каска 30 руб
Крепеж ж1 190 руб
арендованное
Вал 80 руб
На продажу
Тиски 1500 руб
Петров
Строительное оборудование
Собственное
Каска 100 руб
Крепеж ж1 150 руб
арендованное
Вал 170 руб
На продажу
Тиски 1500 руб
4. Карина К (EvaMendes) 08.09.14 01:41 Сейчас в теме
Ну помогите, пожалуйста!
5. deevil deevil (deevil) 107 08.09.14 02:22 Сейчас в теме
А не легче через СКД сделать?
Там дерево и группировки легче строить...
6. Xer shi (Xershi) 274 08.09.14 10:13 Сейчас в теме
(1) EvaMendes, пусто обычно что-то значит, не факт что это ошибка. И да, в СКД это реализуйте!
7. Карина К (EvaMendes) 09.09.14 12:35 Сейчас в теме
да уж, помогли...(6) Xershi, конечно что-то значит! Явно не ошибка - это значит, что произошло 2 группировки - на контрагента с именем Мойконтрагент и с именем "Пусто". А мне нужно чтоб было все у моего контрагента.
(5) deevil, да можно и скд, конечно! Но я в принципе не понимаю как решить мой вопрос, хоть в скд, хоть в запросе. прошу теоретического ответа.
8. Xer shi (Xershi) 274 09.09.14 12:47 Сейчас в теме
(7) EvaMendes, у вас сложный запрос опыта такого рода нету, чтобы сказать как правильно, все методом тыка. Если нету мыслей попробуйте нарисовать таблицы исходных данных и затем как они в процессе должны модифицироваться возможно это будет ключем к решению.
Желательно в картинках))
9. Rushan Sadretdinov (Rulllan) 3 09.09.14 13:47 Сейчас в теме
Попробуй так :
ВЫБОР
| КОГДА ВложенныйЗапрос.контрагент ЕСТЬ NULL
| ТОГДА ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.Контрагент
| ИНАЧЕ ВложенныйЗапрос.контрагент
| КОНЕЦ КАК Контрагент
10. Карина К (EvaMendes) 26.09.14 09:35 Сейчас в теме
11. Карина К (EvaMendes) 26.09.14 09:36 Сейчас в теме
(9) Rulllan, спасибо. С группировкой действительно помогло, только вот колонка с номенклатурой пустая
12. Yan Tsys (YanTsys) 9 26.09.14 16:32 Сейчас в теме
(1) EvaMendes, в конструкторе запросов внизу-слева есть кнопка запрос, появляется окошко из которого можно запрос скопировать без этих прикольных вертикальных палочек, и туда-же можно загнать запрос без этих символов если включить редактирование запроса...

И ооочень полезный совет, если не хочешь со временем зашиться с логикой запросов, разберись с временными таблицами и размещением нескольких запросов в одном пакете. Когда запрос разбит на отдельные логические части его легче и читать и составлять.

Насколько я понимаю вместо вот этого:
ВЫБОР 
 КОГДА ВложенныйЗапрос.контрагент ЕСТЬ NULL 
 ТОГДА ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.Контрагент 
 ИНАЧЕ ВложенныйЗапрос.контрагент 
 КОНЕЦ КАК Контрагент
...Показать Скрыть


Лучше написать
ЕстьNULL(ВложенныйЗапрос.контрагент,ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.Контрагент) КАК Контрагент


Кстати вместо вашего исходного

ВЫБОР
КОГДА ВложенныйЗапрос.Цена ЕСТЬ NULL 
ТОГДА ЦеныНоменклатурыСрезПоследних.Цена
ИНАЧЕ ВложенныйЗапрос.Цена
КОНЕЦ КАК Цена
...Показать Скрыть


тоже лучше написать
ЕстьNULL(ВложенныйЗапрос.Цена,ЦеныНоменклатурыСрезПоследних.Цена) КАК Цена
13. Yan Tsys (YanTsys) 9 26.09.14 17:24 Сейчас в теме
И кстати если вы уж соединяете по ВложенныйЗапрос.Номенклатура.Ссылка = ЗначенияСвойствОбъектов.Объект.Ссылка то лучше не
ВложенныйЗапрос.Номенклатура КАК Номенклатура
а
ЗначенияСвойствОбъектов.Объект КАК Номенклатура
14. Карина К (EvaMendes) 03.10.14 11:41 Сейчас в теме
15. FallenMAd rus (FallenMad) 8 03.10.14 13:54 Сейчас в теме
Есть несколько вариаций решения. Для выбора необходимо определиться для чего используется связка контрагент-Номенклатура, ведь не каждому контрагенту мы устанавливаем цену на всю номенклатуру(хотя возможен и такой вариант). Правильно построив фразу получаем половину ответа:1) Надо получить установленные для контрагентов цену по типу цены(Либо выбираем либо нет, см ниже пояснение.) для всех номенклатур выбранного производителя. Тогда запрос выглядит так :

ВЫБРАТЬ
	ЗначенияСвойствОбъектов.Значение КАК Производитель,
	ЕСТЬNULL(ВложенныйЗапрос.Валюта, ЦеныНоменклатурыСрезПоследних.Валюта) КАК Валюта,
	ВложенныйЗапрос.Контрагент КАК Контрагент,
	ЕСТЬNULL(ВложенныйЗапрос.Номенклатура, ЗначенияСвойствОбъектов.Объект) КАК Номенклатура,
	ЕСТЬNULL(ВложенныйЗапрос.НоменклатураАртикул, ЦеныНоменклатурыСрезПоследних.Номенклатура.Артикул) КАК Артикул,
	ЕСТЬNULL(ВложенныйЗапрос.Цена, ЦеныНоменклатурыСрезПоследних.Цена) КАК Цена
ИЗ
	РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			ЦеныНоменклатурыСрезПоследних.Цена КАК Цена,
			ЦеныНоменклатурыСрезПоследних.Валюта КАК Валюта,
			ЦеныНоменклатурыСрезПоследних.ТипЦен КАК ТипЦен,
			ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.Контрагент КАК Контрагент,
			ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
			ЦеныНоменклатурыСрезПоследних.Номенклатура.Артикул КАК НоменклатураАртикул
		ИЗ
			РегистрСведений.ТипыЦенПоГруппамНоменклатурыДляПокупателей.СрезПоследних(&Дата, Контрагент В ИЕРАРХИИ (&Контрагент)) КАК ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних
				ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ) КАК ЦеныНоменклатурыСрезПоследних
				ПО ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.ТипЦен = ЦеныНоменклатурыСрезПоследних.ТипЦен
					И ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.НоменклатурнаяЦеноваяГруппа.Ссылка = ЦеныНоменклатурыСрезПоследних.Номенклатура.ЦеноваяГруппа.Ссылка) КАК ВложенныйЗапрос
		ПО ЗначенияСвойствОбъектов.Объект = ВложенныйЗапрос.Номенклатура
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
		ПО ЗначенияСвойствОбъектов.Объект = ЦеныНоменклатурыСрезПоследних.Номенклатура
ГДЕ
	ЗначенияСвойствОбъектов.Свойство = &Свойство
	И ЗначенияСвойствОбъектов.Значение В(&Значение)
        И НЕ ВложенныйЗапрос.Контрагент есть NULL

СГРУППИРОВАТЬ ПО
	ЕСТЬNULL(ВложенныйЗапрос.НоменклатураАртикул, ЦеныНоменклатурыСрезПоследних.Номенклатура.Артикул),
	ЗначенияСвойствОбъектов.Значение,
	ВложенныйЗапрос.Контрагент,
	ЕСТЬNULL(ВложенныйЗапрос.Валюта, ЦеныНоменклатурыСрезПоследних.Валюта),
	ЕСТЬNULL(ВложенныйЗапрос.Номенклатура, ЗначенияСвойствОбъектов.Объект),
	ЕСТЬNULL(ВложенныйЗапрос.Цена, ЦеныНоменклатурыСрезПоследних.Цена)

ИМЕЮЩИЕ
	НЕ ВложенныйЗапрос.Контрагент ЕСТЬ NULL 
ИТОГИ ПО
	Контрагент,
	Номенклатура ТОЛЬКО ИЕРАРХИЯ
...Показать Скрыть



2) Надо получить Все установленные цены по типам цен (Либо выбираем либо нет) для всех номенклатур выбранного производителя с разбивкой Группировкой по Контрагентам для которых установлены соответствия ТипЦены-Ценоваягруппа(есть актуальный документ УстановкаТиповЦенПоГруппамНоменклатурыДляПокупателей) Тогда запрос выглядит так :

ВЫБРАТЬ
	ЕСТЬNULL(ВложенныйЗапрос.Контрагент, "Нет ни одного контрагенту у которого установлена связь(ценовая группа - Тип цены) для : " + ЗначенияСвойствОбъектов.Объект.ЦеноваяГруппа.Наименование + "-" + ЦеныНоменклатурыСрезПоследних.ТипЦен.Наименование) КАК Контрагент,
	ЗначенияСвойствОбъектов.Значение КАК Производитель,
	ЕСТЬNULL(ВложенныйЗапрос.Валюта, ЦеныНоменклатурыСрезПоследних.Валюта) КАК Валюта,
	ЕСТЬNULL(ВложенныйЗапрос.Номенклатура, ЗначенияСвойствОбъектов.Объект) КАК Номенклатура,
	ЕСТЬNULL(ВложенныйЗапрос.НоменклатураАртикул, ЦеныНоменклатурыСрезПоследних.Номенклатура.Артикул) КАК Артикул,
	ЕСТЬNULL(ВложенныйЗапрос.ТипЦен, ЦеныНоменклатурыСрезПоследних.ТипЦен) КАК ТипЦены,
	ЕСТЬNULL(ВложенныйЗапрос.Цена, ЦеныНоменклатурыСрезПоследних.Цена) КАК Цена
ИЗ
	РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			ЦеныНоменклатурыСрезПоследних.Цена КАК Цена,
			ЦеныНоменклатурыСрезПоследних.Валюта КАК Валюта,
			ЦеныНоменклатурыСрезПоследних.ТипЦен КАК ТипЦен,
			ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.Контрагент КАК Контрагент,
			ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
			ЦеныНоменклатурыСрезПоследних.Номенклатура.Артикул КАК НоменклатураАртикул
		ИЗ
			РегистрСведений.ТипыЦенПоГруппамНоменклатурыДляПокупателей.СрезПоследних(&Дата, ) КАК ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних
				ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ) КАК ЦеныНоменклатурыСрезПоследних
				ПО ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.ТипЦен = ЦеныНоменклатурыСрезПоследних.ТипЦен
					И ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.НоменклатурнаяЦеноваяГруппа.Ссылка = ЦеныНоменклатурыСрезПоследних.Номенклатура.ЦеноваяГруппа.Ссылка
		
		СГРУППИРОВАТЬ ПО
			ТипыЦенПоГруппамНоменклатурыДляПокупателейСрезПоследних.Контрагент,
			ЦеныНоменклатурыСрезПоследних.Валюта,
			ЦеныНоменклатурыСрезПоследних.Номенклатура,
			ЦеныНоменклатурыСрезПоследних.ТипЦен,
			ЦеныНоменклатурыСрезПоследних.Цена,
			ЦеныНоменклатурыСрезПоследних.Номенклатура.Артикул) КАК ВложенныйЗапрос
		ПО ЗначенияСвойствОбъектов.Объект = ВложенныйЗапрос.Номенклатура
			И (ВложенныйЗапрос.ТипЦен = &ТипЦен)
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата, ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
		ПО ЗначенияСвойствОбъектов.Объект = ЦеныНоменклатурыСрезПоследних.Номенклатура
ГДЕ
	ЗначенияСвойствОбъектов.Свойство = &Свойство
	И ЗначенияСвойствОбъектов.Значение В(&Значение)

СГРУППИРОВАТЬ ПО
	ЕСТЬNULL(ВложенныйЗапрос.НоменклатураАртикул, ЦеныНоменклатурыСрезПоследних.Номенклатура.Артикул),
	ЗначенияСвойствОбъектов.Значение,
	ЕСТЬNULL(ВложенныйЗапрос.Валюта, ЦеныНоменклатурыСрезПоследних.Валюта),
	ЕСТЬNULL(ВложенныйЗапрос.Номенклатура, ЗначенияСвойствОбъектов.Объект),
	ЕСТЬNULL(ВложенныйЗапрос.Цена, ЦеныНоменклатурыСрезПоследних.Цена),
	ЕСТЬNULL(ВложенныйЗапрос.ТипЦен, ЦеныНоменклатурыСрезПоследних.ТипЦен),
	ЕСТЬNULL(ВложенныйЗапрос.Контрагент, "Нет ни одного контрагенту у которого установлена связь(ценовая группа - Тип цены) для : " + ЗначенияСвойствОбъектов.Объект.ЦеноваяГруппа.Наименование + "-" + ЦеныНоменклатурыСрезПоследних.ТипЦен.Наименование)
ИТОГИ ПО
	Контрагент,
	Номенклатура ТОЛЬКО ИЕРАРХИЯ 
...Показать Скрыть




Обещанное пояснение: В основном запросе вы использовали ограничение при выборе Цены номенклатуры (2ой источник) по переданному Типу Цен, тогда и во Вложенном подзапросе его необходимо учитывать (это можно сделать внутри или уже при самом соединении), привильнее конечно внутри подзапроса, но я указал именно в соединении потому как по условию так понял что Вложенный подзапрос может меняться, либо это будет передано виртуальной таблицей. вообщем как-то так.
16. FallenMAd rus (FallenMad) 8 03.10.14 14:04 Сейчас в теме
Да кстати, данный запрос можно оптимизировать по скорости, Использовав пакетные запросы мы 2 раза лезем к ценам номенклатуры, а табличка то не маленькая (можно ведь один раз получить эту табличку использовав её потом 2 раза, чем 2 раза получать таблицу), плюс предварительно сделать выборку по значениям свойств объектов и Измерение "Объект" выразить как Справочник номенклатуры, а Ресурс "Значение" выразить как справочник Контрагенты, при этом отсекутся таблицы при соединениях.
17. Карина К (EvaMendes) 23.11.14 23:55 Сейчас в теме
(16) FallenMad, спасибо! второй запрос просто не отрабатывает, поэтому скажу про первый: в нем почему-то не работают итоги, то есть результат выборки номенклатуры выходит без группировок, почему, не знаю...
18. Карина К (EvaMendes) 21.12.16 15:27 Сейчас в теме
Прошу помощи в создании нового отчета на СКД. Он уже написан, но не работает. Нужно подсказать по Skype или wats app решение вопроса. Если есть желание, отблагодарим материально.
19. Алекс Кон (alex-l19041) 9 21.12.16 15:28 Сейчас в теме
(18) создайте новую тему. Опишите подробнее проблему
Оставьте свое сообщение