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

Откуда:
Сообщений: 3
Доброго времени суток! Есть запрос -

SELECT

	SUM( p1.sm) AS 'Softline',
	SUM( p2.sm) AS 'Euroline',
	SUM( p3.sm) AS 'Proline',
	SUM( p4.sm) AS 'Alphaline',
	SUM( p5.sm) AS 'Softline82',
	SUM( p6.sm) AS 'WHS',
	SUM( p7.sm) AS 'Topline',
	SUM( moskit.sm) AS 'МС',
	SUM( sp.sm) AS 'СП',

	SUM( p1.qu)	AS 'QuSL',
	SUM( p2.qu) AS 'QuEl',
	SUM( p3.qu) AS 'QuPl',
	SUM( p4.qu) AS 'QuAl',
	SUM( p5.qu) AS 'QuSl',
	SUM( p6.qu) AS 'QuWhs',
	SUM( p7.qu) AS 'QuTl',
	SUM( moskit.qu) AS 'QuMS',
	SUM( sp.qu) AS 'QuSp'
FROM
	
(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,26) and opc.deleted is null)/100,0)))*ISNULL(oi.qu,0) AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o left join
	orderitem oi on oi.idorder=o.idorder
	left join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 11	--Softline
	and iddocstate=3 -- в производстве
) as p1,

(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,26) and opc.deleted is null)/100,0)))*ISNULL(oi.qu,0) AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o left join
	orderitem oi on oi.idorder=o.idorder
	left join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 34 -- Euroline
	and iddocstate=3 -- в производстве
) as p2,

(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL( (select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,26) and opc.deleted is null)/100,0)))*ISNULL(oi.qu,0) AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o right join
	orderitem oi on oi.idorder=o.idorder
	right join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 255 -- Proline
	and iddocstate=3 -- в производстве
) as p3,

(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL( (select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,31) and opc.deleted is null)/100,0)))*ISNULL(oi.qu,0) AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o right join
	orderitem oi on oi.idorder=o.idorder
	right join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 155 -- Alphaline
	and iddocstate=3 -- в производстве
) as p4,

(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL( (select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,34) and opc.deleted is null)/100,0)))*oi.qu AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o right join
	orderitem oi on oi.idorder=o.idorder
	right join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 337 -- Softline82
	and iddocstate=3 -- в производстве
) as p5,

(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL( (select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,36) and opc.deleted is null)/100,0)))*oi.qu AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o right join
	orderitem oi on oi.idorder=o.idorder
	right join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 338 -- WHS
	and iddocstate=3 -- в производстве
) as p6,

(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL( (select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,27) and opc.deleted is null)/100,0)))*oi.qu AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o right join
	orderitem oi on oi.idorder=o.idorder
	right join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 33 -- Topline
	and iddocstate=3 -- в производстве
) as p7,

(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL( (select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,24) and opc.deleted is null)/100,0)))*oi.qu AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o right join
	orderitem oi on oi.idorder=o.idorder
	right join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 35 and -- Без профиля
	oi.idconstructiontype = 32 --МС
	and iddocstate=3 -- в производстве
) as moskit, 

(
SELECT
	(ISNULL(f.sm,0)+
	(f.sm*ISNULL( (select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,25) and opc.deleted is null)/100,0)))*oi.qu AS sm,
	ISNULL(oi.qu,0) as qu

	FROM
	orders o right join
	orderitem oi on oi.idorder=o.idorder
	right join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder and f.idfinparam=142 and f.deleted is null AND
	oi.idprofsys = 35 and -- Без профиля
	(oi.idconstructiontype = 33 or oi.idconstructiontype = 35) --Стеклопакеты
	and iddocstate=3 -- в производстве
) as sp


Стабильно возвращает только null. Данные для формирования запроса тестовые. Если выполнить кусок запроса отдельно то все замечательно, например так -

+
SELECT SUM(a1.sum1) as 'сумма', SUM(a1.qu) as 'кол-во' from
(SELECT
	(f.sm+
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(12,17,21) and opc.deleted is null)/100,0))-
	(f.sm*ISNULL((select sum(perc) from orderpricechange opc where opc.idorder=o.idorder and idpricechange in(10,16,19,28) and opc.deleted is null)/100,0)))*oi.qu AS sum1,
	oi.qu as qu

FROM
	orders o right join
	orderitem oi on oi.idorder=o.idorder
	right join	finparamcalc f on f.idmodel=oi.idmodel
WHERE
	o.idorder in (select os.idorder from ordersign os where os.deleted is null and os.idsign=9 and os.dtcreate between '20131210 00:00:00' and '20131210 23:59:59') and
	o.idcustomer = (select distinct cus.idcustomer from customer cus where cus.name = 'ИП Дёмин В.А.' and cus.deleted is null) AND
	oi.typ=1 AND
	o.deleted is null AND
	oi.deleted is null AND
	f.idorder=o.idorder AND 
	f.idfinparam=142 AND
	f.deleted is null AND
	oi.idprofsys = 34 AND	--
	iddocstate=3 -- в производстве
) a1


Возвращает верные данные, помогите разобраться где ошибка

Сообщение было отредактировано: 17 дек 13, 19:34
17 дек 13, 18:14    [15306901]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решить проблему  [new]
sdet
Member

Откуда:
Сообщений: 463
cooler97,
Разбейте запрос на части и посмотрите какая часть дает неправильный результат (по вашему мнению)
17 дек 13, 18:21    [15306926]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решить проблему  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
cooler97
Стабильно возвращает только null. Данные для формирования запроса тестовые. Если выполнить кусок запроса отдельно то все замечательно, например так -
У вас CROSS JOIN нескольких подзапросов.

Так что что бы возвращало не null, нужно что бы все запросы что то возвращали.

Соответственно нужно выполнить не "кусок запроса отдельно", а "каждый кусок запроса отдельно", и посмотреть, какой не возвражает записей.
17 дек 13, 19:32    [15307170]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решить проблему  [new]
cooler97
Member

Откуда:
Сообщений: 3
А если они возвращают null? т.е. по логиге подзапросы которые именуются p1, p2 и т.д. могут ничего не возвращать. Как это можно обойти ?
18 дек 13, 12:43    [15310329]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решить проблему  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
cooler97
А если они возвращают null? т.е. по логиге подзапросы которые именуются p1, p2 и т.д. могут ничего не возвращать. Как это можно обойти ?
Тогда надо делать left join или full join вместо cross join
18 дек 13, 12:49    [15310405]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решить проблему  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
alexeyvg
cooler97
А если они возвращают null? т.е. по логиге подзапросы которые именуются p1, p2 и т.д. могут ничего не возвращать. Как это можно обойти ?
Тогда надо делать left join или full join вместо cross join
Поэкспериментируйте на простых примерах:
--	Это ваш запрос, возвращающий данные
select *
from 
(select top 1 * from sysobjects) p1,
(select top 1 * from sysobjects) p2

--	Это ваш запрос, в котором один из поздапросов не возвращает данные
select *
from 
(select top 1 * from sysobjects) p1,
(select top 1 * from sysobjects where 1=0) p2

--	Это ваш запрос, в котором один из поздапросов не возвращает данные, но переделанный на full join 
select *
from 
(select top 1 * from sysobjects) p1
 full join (select top 1 * from sysobjects where 1=0) p2 on 1=1
18 дек 13, 12:54    [15310471]     Ответить | Цитировать Сообщить модератору
 Re: Помогите решить проблему  [new]
cooler97
Member

Откуда:
Сообщений: 3
Теперь понятно где моя ошибка! Спасибо всем большое за ответы! Вы мне очень помогли!
18 дек 13, 19:04    [15313530]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить