пытаюсь прекрутить св-ва товара (через smspecscale). получается туева ..ча всякого ...
Код:
SELECT c.article as "Артикул",
c.name as "Наименование",
NVL(inprices.price, 0) as "Зак цен",
NVL(buy.quantity, 0) as "Ко-во прихода",
NVL(pr.subarticle, '--') as "СубАрт",
NVL(inprices.price, 0)*NVL(buy.quantity, 0) as "Cумма прихода",
NVL(saleret.quantity, 0) as "Кол-во возврата",
NVL(saleret.quantity, 0)*NVL(saleret.price, 0) as "$$$ возврата",
NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0) as "Остаток",
NVL(outprices.price,0) as "resprice",
NVL((NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0))*
NVL(outprices.price,0),0) as "$$$"
FROM supermag.smcard c,
supermag.smgoods q,
supermag.sacardclass cc,
(
SELECT DISTINCT dc.article, ss.subarticle,
SUM(ss.quantity) as quantity
-- SUM(dc.itemprice) as price
FROM supermag.smdocuments d,
supermag.smspec dc,
supermag.smspecscale ss
WHERE ss.doctype=dc.doctype
AND ss.docid=dc.docid
AND not(ss.quantity = 0)
AND d.doctype=dc.doctype
AND d.id=dc.docid
AND d.docstate=3
AND TO_DATE(d.createdat) >= to_date('01.07.2007', 'DD.MM.YYYY')
AND d.locationto = (1)
GROUP BY dc.article, ss.subarticle
) pr,
(
SELECT DISTINCT dc.article, ss.subarticle,
SUM(ss.quantity) as quantity
-- SUM(dc.itemprice) as price
FROM supermag.smdocuments d,
supermag.smspec dc,
supermag.smspecscale ss
WHERE ss.doctype=dc.doctype
AND ss.docid=dc.docid
AND not(ss.quantity = 0)
AND d.doctype=dc.doctype
AND d.id=dc.docid
AND d.docstate=3
AND (d.opcode = (0)) -- 0 if sklad; 4 if tochka
AND TO_DATE(d.createdat) between to_date('1.06.2007', 'DD.MM.YYYY') and to_date('01.07.2007', 'DD.MM.YYYY')
AND d.locationto = (1)
GROUP BY dc.article, ss.subarticle --dc.itemprice,
) buy,
(
SELECT his.PRICE, his.ARTICLE, his.STORELOC
FROM SUPERMAG.SMPRICEHISTORY his, SUPERMAG.SMLOCPRICES prt
WHERE his.PRICETYPE = prt.PRICETYPE
AND prt.LOCID = his.STORELOC
AND (bitand(prt.FLAGS, 2) = 2)
AND his.RECID = (SELECT MAX(h.RECID)
FROM SUPERMAG.SMPRICEHISTORY h
WHERE h.STORELOC = his.STORELOC
AND h.ARTICLE = his.ARTICLE
AND h.PRICETYPE = his.PRICETYPE
AND TRUNC(h.EVENTTIME) <= TRUNC(TO_DATE('01.07.2007', 'DD.MM.YYYY')))
) outprices,
(
SELECT his.PRICE, his.ARTICLE
FROM SUPERMAG.SMPRICES his
WHERE his.PRICETYPE = 1
AND his.STORELOC = 1
) inprices,
(
SELECT DISTINCT dc.article, ss.subarticle,
SUM(ss.quantity) as quantity,
SUM(dc.itemprice) as price
FROM supermag.smdocuments d,
supermag.smspec dc,
supermag.smspecscale ss
WHERE ss.doctype=dc.doctype
AND ss.docid=dc.docid
AND not(ss.quantity = 0)
AND d.doctype=dc.doctype
AND d.id=dc.docid
AND d.docstate=3
AND d.opcode = 3
AND TO_DATE(d.createdat) >= to_date('01.06.2007', 'DD.MM.YYYY')
AND d.locationto = (1)
AND d.clientindex = 4 --client!!!
GROUP BY dc.article, ss.subarticle
) saleret,
(
SELECT DISTINCT dc.article, ss.subarticle,
SUM(ss.quantity) as quantity,
SUM(dc.itemprice) as price
FROM supermag.smdocuments d,
supermag.smspec dc,
supermag.smspecscale ss
WHERE ss.doctype=dc.doctype
AND ss.docid=dc.docid
AND not(ss.quantity = 0)
AND d.doctype=dc.doctype
AND d.id=dc.docid
AND d.docstate=3
AND TO_DATE(d.createdat) >= to_date('01.07.2007', 'DD.MM.YYYY')
AND d.locationfrom = (1)
GROUP BY dc.article, ss.subarticle
) rs
WHERE c.article=pr.article(+)
AND c.article=rs.article(+)
AND c.article=q.article(+)
AND c.article=buy.article(+)
AND c.article=outprices.article(+)
AND q.storeloc=outprices.storeloc
AND c.article=inprices.article(+)
AND c.article=saleret.article(+)
--AND c.accepted=1--active cards
AND q.storeloc(+)=(1)
AND c.idclass=cc.id(+)
AND cc.tree like '1.1.1.%'
AND c.name like '%Из Рук В Руки%'
AND (NVL(q.quantity,0)-NVL(pr.quantity,0)+NVL(rs.quantity,0))>=0-- > 0 condition