Sim Zacks wrote:
> Grzegorz Jaśkiewicz wrote:
>> give us postgresql version as well, maybe show query and at least table layout.
>>
>
The queries look like (The one I'm trying to run is the last one, which
is based on all the previous ones).:
CREATE OR REPLACE VIEW assembliesstockbatch AS
SELECT d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname,
c.ownerid, e.partid, d.units, - e.quantity AS quantity,
COALESCE(c.stock, 0::bigint) AS stock, max(b.quantity) AS qtyperunit,
a.leadfree
FROM assemblies a
JOIN assembliesbatch d ON d.assemblyid = a.assemblyid
JOIN allocatedassemblies e ON e.assembliesbatchid = d.assembliesbatchid
LEFT JOIN partsassembly b ON b.assemblyid = a.assemblyid AND e.partid
= b.partid
LEFT JOIN stockperowner_lead_ab() c(partid, ownerid, stock,
leadstateid) ON c.partid = e.partid AND c.ownerid = 1 AND
leadcompcheck_ab(a.leadfree, c.leadstateid)
LEFT JOIN stocklog f ON f.refid = d.batchid AND f.transtypeid = 3 AND
f.partid = e.partid
WHERE (d.assembliesbatchstatusid = ANY (ARRAY[1, 2, 4, 7])) AND
f.commited IS NOT TRUE
GROUP BY d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname,
c.ownerid, e.partid, COALESCE(c.stock, 0::bigint), d.units, e.quantity,
a.leadfree;
CREATE OR REPLACE VIEW assembliesstockbatchprioruse AS
SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname,
a.ownerid, a.partid, a.units, a.quantity, a.stock, COALESCE(sum(-
b.quantity)::double precision, 0::double precision) AS prioruse, sum(
CASE
WHEN c.batchid IS NOT NULL THEN - b.quantity
ELSE 0
END)::double precision AS priorusebatch, a.qtyperunit, a.leadfree
FROM assembliesstockbatch a
LEFT JOIN (allocatedassemblies b
JOIN assembliesbatch c ON b.assembliesbatchid = c.assembliesbatchid
AND (c.assembliesbatchstatusid = 1 OR c.assembliesbatchstatusid = 2 OR
c.assembliesbatchstatusid = 4 OR c.assembliesbatchstatusid = 7)
JOIN assemblies q ON q.assemblyid = c.assemblyid) ON a.partid =
b.partid AND (a.ownerid IS NULL OR a.ownerid = 1) AND a.leadfree =
q.leadfree AND (a.duedate > c.duedate OR a.duedate = c.duedate AND
a.assembliesbatchid > c.assembliesbatchid)
GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname,
a.ownerid, a.partid, a.quantity, a.stock, a.units, a.qtyperunit, a.leadfree;
CREATE OR REPLACE VIEW assembliesstockbatchpriorexpected AS
SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname,
a.ownerid, a.partid, a.units, a.quantity, a.stock, a.prioruse,
a.priorusebatch, COALESCE(sum(
CASE
WHEN COALESCE(e.promisedby::timestamp without time
zone::timestamp with time zone, e.requestedby::timestamp without time
zone::timestamp with time zone,
CASE
WHEN e.deliverywks IS NULL THEN f.issuedate
ELSE NULL::date
END::timestamp without time zone::timestamp with time zone,
CASE
WHEN e.deliverywks <> -1 THEN (f.issuedate +
e.deliverywks * 7)::timestamp without time zone::timestamp with time zone
ELSE a.duedate + '1 day'::interval
END) <= a.duedate THEN COALESCE(e.quantity, 0) -
COALESCE(e.deliveredsum, 0)
ELSE NULL::integer
END), 0::bigint) AS expectedbefore, a.qtyperunit
FROM assembliesstockbatchprioruse a
LEFT JOIN (pos f
JOIN poparts e ON f.poid = e.poid AND f.postatusid >= 20 AND
f.postatusid <= 59 AND f.isrfq = false
JOIN manufacturerpartpn g ON g.pnid = e.pnid) ON e.partid = a.partid
GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname,
a.ownerid, a.partid, a.quantity, a.stock, a.prioruse, a.units,
a.qtyperunit, a.priorusebatch;
CREATE OR REPLACE VIEW assemblycanbuild AS
SELECT assembliesstockbatchpriorexpected.assembliesbatchid,
CASE
WHEN min(
CASE
WHEN (assembliesstockbatchpriorexpected.stock::double
precision - assembliesstockbatchpriorexpected.prioruse -
assembliesstockbatchpriorexpected.quantity::double precision) >=
0::double precision THEN 100000000::double precision
WHEN
COALESCE(assembliesstockbatchpriorexpected.qtyperunit, 0::double
precision) = 0::double precision OR
(assembliesstockbatchpriorexpected.stock::double precision -
assembliesstockbatchpriorexpected.prioruse) < 0::double precision THEN
0::double precision
ELSE
trunc((assembliesstockbatchpriorexpected.stock::double precision -
assembliesstockbatchpriorexpected.prioruse) /
assembliesstockbatchpriorexpected.qtyperunit)
END) = 100000000::double precision THEN 'All'::character varying
ELSE min(
CASE
WHEN
COALESCE(assembliesstockbatchpriorexpected.qtyperunit, 0::double
precision) = 0::double precision OR
(assembliesstockbatchpriorexpected.stock::double precision -
assembliesstockbatchpriorexpected.prioruse) < 0::double precision THEN
0::double precision
ELSE
trunc((assembliesstockbatchpriorexpected.stock::double precision -
assembliesstockbatchpriorexpected.prioruse) /
assembliesstockbatchpriorexpected.qtyperunit)
END)::character varying
END AS canbuild
FROM assembliesstockbatchpriorexpected
WHERE assembliesstockbatchpriorexpected.quantity <> 0
GROUP BY assembliesstockbatchpriorexpected.assembliesbatchid;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
