select c.article, c.shortname, m.abbrev, round(g.quantity,2) goods, round(nvl(sl.salerate,0),2) ssreal from supermag.smcard c, supermag.sameasurement m, supermag.smgoods g, supermag.smcardassort ca, supermag.sacardassort aca, supermag.SMSTOCKLEVELS sl where c.article=ca.article and c.idmeasurement=m.id and c.article=g.article and ca.idassort=aca.id and c.article=sl.article and g.storeloc=sl.storeloc and aca.tree='дерево' and g.storeloc=ид_мх order by c.shortname
select c.article, c.shortname, m.abbrev, round(g.quantity,2) goods, round(nvl(sl.salerate,0),2) ssreal from supermag.smcard c, supermag.sameasurement m, supermag.smgoods g, supermag.smcardassort ca, supermag.sacardassort aca, supermag.SMSTOCKLEVELS sl where c.article=ca.article and c.idmeasurement=m.id and c.article=g.article and ca.idassort=aca.id and g.article=sl.article(+) and g.storeloc=sl.storeloc(+) and aca.tree='дерево' and g.storeloc=ид_мх order by c.shortname
select c.article, c.shortname, m.abbrev, round(g.quantity,2) goods, round(nvl(sl.salerate,0),2) ssreal from supermag.smcard c, supermag.sameasurement m, supermag.smgoods g, supermag.smcardassort ca, supermag.sacardassort aca, supermag.SMSTOCKLEVELS sl where c.article=ca.article and c.idmeasurement=m.id and c.article=g.article and ca.idassort=aca.id and g.article=sl.article(+) and g.storeloc=sl.storeloc(+) and aca.tree='дерево' and g.storeloc=ид_мх order by c.shortname
select y.art art, z.suppl suppl, y.loc locate
from (select z.art art, z.locationto loc, max(z.rd) ra
from (select MAX(rownum) rd,
X.Supplier,
X.Art,
X.SupplDate,
X.DocID,
(select LocationTo
from SMDocuments D
where D.DocType = 'WI'
and D.ID = X.DocID) LocationTo
from (select Supplier,
Art,
substr(max_data, 9) DocID,
to_date(substr(max_data, 1, 8), 'YYYYMMDD') SupplDate
from (select D.ClientIndex as Supplier,
S.Article as Art,
max(to_char(D.Createdat, 'YYYYMMDD') || D.id) max_data
from SMSpec S, SMDocuments D
where D.DocType = S.DocType
and S.DocID = D.ID
and S.DocType = 'WI'
and D.DocState = 3
and D.OpCode = 0
group by D.ClientIndex, S.Article)
order by substr(max_data, 9),
to_date(substr(max_data, 1, 8), 'YYYYMMDD') desc) X,
SMSpec S
where S.DocType = 'WI'
and S.DocID = X.DocID
and S.Article = X.Art
group by X.Supplier, X.Art, X.SupplDate, X.DocID
order by X.Art, X.SupplDate) z
group by z.art, z.locationto
order by z.art, z.locationto, max(z.rd)) y,
(select MAX(rownum) rd,
X.Supplier suppl,
X.Art,
X.SupplDate,
X.DocID DID
from (select Supplier,
Art,
substr(max_data, 9) DocID,
to_date(substr(max_data, 1, 8), 'YYYYMMDD') SupplDate
from (select D.ClientIndex as Supplier,
S.Article as Art,
max(to_char(D.Createdat, 'YYYYMMDD') || D.id) max_data
from SMSpec S, SMDocuments D
where D.DocType = S.DocType
and S.DocID = D.ID
and S.DocType = 'WI'
and D.DocState = 3
and D.OpCode = 0
group by D.ClientIndex, S.Article)
order by substr(max_data, 9),
to_date(substr(max_data, 1, 8), 'YYYYMMDD') desc) X,
SMSpec S
where S.DocType = 'WI'
and S.DocID = X.DocID
and S.Article = X.Art
group by X.Supplier, X.Art, X.SupplDate, X.DocID
order by X.Art, X.SupplDate) z
where y.ra = z.rd select t.article, t.locationto, t.lcl, cl.name from (select article, locationto, max(clientindex) keep (dense_rank last order by createdat) lcl from smspec, smdocuments where id=docid and opcode=0 and docstate=3 and trunc(createdat)>=sysdate-120 group by article, locationto) t, smclientinfo cl where t.lcl=cl.id order by t.article
select t.article, t.locationto, t.lcl, cl.name from (select article, locationto, max(clientindex) keep (dense_rank last order by createdat) lcl from smspec, smdocuments where id=docid and opcode=0 and docstate=3 and trunc(createdat)>=sysdate-120 group by article, locationto) t, smclientinfo cl where t.lcl=cl.id order by t.article