Hello, Nikolay!
You wrote on Tue, 23 May 2006 19:08:45 +0300:
Вот мой запрос, выполняется на ура в 1.5.2 (в двойке тоже работает):
------------------------ start clipboard --------------------
/* оборотная ведомость ТМЦ */
select k1.store,
min(s2.name) as st_name,
min(s2.fpr) as centr,
k1.acc,
k1.code,
min(m1.name) as name,
min(m1.unit) as unit,
min(s1.name) as unitname,
min(m1.price) as price,
/* остатки на начало периода */
(select first 1 ost from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon<:start_year*12+:start_mon order by nyear desc, nmon desc,
dpk desc) as start_qty,
(select first 1 sos from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon<:start_year*12+:start_mon order by nyear desc, nmon desc,
dpk desc) as start_amt,
/* обороты за период */
(select sum(pri) from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon>=:start_year*12+:start_mon and
nyear*12+nmon<=:stop_year*12+:stop_mon) as in_qty,
(select sum(spr) from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon>=:start_year*12+:start_mon and
nyear*12+nmon<=:stop_year*12+:stop_mon) as in_amt,
(select sum(ras) from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon>=:start_year*12+:start_mon and
nyear*12+nmon<=:stop_year*12+:stop_mon) as out_qty,
(select sum(sra) from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon>=:start_year*12+:start_mon and
nyear*12+nmon<=:stop_year*12+:stop_mon) as out_amt,
/* остатки на конец периода */
(select first 1 ost from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon<=:stop_year*12+:stop_mon order by nyear desc, nmon desc,
dpk desc) as stop_qty,
(select first 1 sos from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon<=:stop_year*12+:stop_mon order by nyear desc, nmon desc,
dpk desc) as stop_amt
from ksu k1
left join spm m1 on k1.code=m1.code
left join sps s1 on s1.csp=6 and s1.code=m1.unit
left join sps s2 on s2.csp=2 and s2.code=k1.store
left join sps s3 on s3.csp=4 and s3.code=k1.acc
/* where k1.acc=3 */
group by 1,4,5
having
(select first 1 ost from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon<:start_year*12+:start_mon order by nyear desc, nmon desc,
dpk desc)<>0
or
(select first 1 sos from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon<:start_year*12+:start_mon order by nyear desc, nmon desc,
dpk desc)<>0
or
(select first 1 ost from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon<=:stop_year*12+:stop_mon order by nyear desc, nmon desc,
dpk desc)<>0
or
(select first 1 sos from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon<=:stop_year*12+:stop_mon order by nyear desc, nmon desc,
dpk desc)<>0
or
(select sum(pri) from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon>=:start_year*12+:start_mon and
nyear*12+nmon<=:stop_year*12+:stop_mon)<>0
or
(select sum(spr) from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon>=:start_year*12+:start_mon and
nyear*12+nmon<=:stop_year*12+:stop_mon)<>0
or
(select sum(ras) from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon>=(:start_year*12+:start_mon) and
nyear*12+nmon<=:stop_year*12+:stop_mon)<>0
or
(select sum(sra) from ksu where store=k1.store and acc=k1.acc and
code=k1.code
and nyear*12+nmon>=:start_year*12+:start_mon and
nyear*12+nmon<=:stop_year*12+:stop_mon)<>0
------------------------- end clipboard ---------------------
Как видишь, по вложенным select'ам не группирую. Несколько похоже на то, что
тебе нужно. Посмотри, не этого ли ты желаешь:
select
s.name as storename,
sum(pc.rest) as rest,
sum(pc.reserve) as reserve,
r.name as regionname,
r.id_region,
(select first 1 rp.price_retail from region_prices rp
where (r.id_region=rp.id_region) and
pc.id_product=rp.id_product) as PriceRetail
from stores s
left join regions r on s.id_region=r.id_region
join product_cards pc on s.id_store=pc.id_store and pc.id_product=582
group by 1,4,5
order by s.name
В твоем запросе меня смутил вот этот кусочек:
from stores s left join regions r on s.id_region=r.id_region,
product_cards pc
where s.id_store=pc.id_store
and pc.id_product=582
Здесь ты смешиваешь явный join с неявным imho.
Удач
--
Alexander A. Venikov, Tobolsk, Russia
Real e-mail address is venix<angry_dog>tn<dot>tob<dot>ru
