CREATE VIEW filfmsetup AS SELECT movimag.codice, movimag.data, movimag.ora, movimag.codoper, movimag.codterm, movimag.codcau, movimag.prelievo, movimag.deposito, movimag.cariniz, movimag.codubi, movimag.codcom, movimag.codlot, movimag.qta, movimag.um, movimag.nc, movimag.qtasn, movimag.codlotpf, movimag.sec, movimag.codsped, movimag.impegno, movimag.tm, movimag.tmdiff, movimag.caudef, movimag.caufm, movimag.codmp, movimag.codagg FROM (movimag LEFT JOIN ( SELECT movimag_1.codubi FROM (movimag movimag_1 JOIN ( SELECT movimag_2.codubi, MAX(movimag_2.codice) AS cod FROM movimag movimag_2 WHERE (((movimag_2.codcau)::text = 'INFM'::text) OR ((movimag_2.codcau)::text = 'FIFM'::text)) GROUP BY movimag_2.codubi) q ON ((q.cod = movimag_1.codice))) WHERE ((movimag_1.caufm)::text = 'setup'::text)) q1 ON (((q1.codubi)::text = (movimag.codubi)::text))) WHERE (q1.codubi IS NULL) ORDER BY movimag.codice DESC; ALTER TABLE public.filfmsetup OWNER TO postgres; COMMENT ON VIEW filfmsetup IS 'Estrae i movimenti di magazzino per tutte le location che non hanno avuto l''ultimo fermo come setup';
CREATE VIEW filfmsetup1 AS SELECT movimag.* FROM movimag INNER JOIN ( SELECT (CASE WHEN c1 IS NULL THEN 0 ELSE (CASE WHEN c2 IS NULL THEN 1 ELSE (CASE WHEN c1>c2 THEN 1 ELSE 0 END) END) END) AS nosetup, q1.codubi FROM ( SELECT MAX(tm) AS c1, codubi FROM movimag WHERE codcau='INFM' AND caufm<>'setup' GROUP BY codubi) AS q1 LEFT JOIN ( SELECT MAX(tm) AS c2, codubi FROM movimag WHERE codcau='INFM' AND caufm='setup' GROUP BY codubi) AS q2 ON q1.codubi=q2.codubi) AS q3 ON q3.codubi=movimag.codubi WHERE movimag.codcau='PREL' AND q3.nosetup='1'; ALTER TABLE public.filfmsetup1 OWNER TO postgres; COMMENT ON VIEW filfmsetup1 IS 'Nuova vista che estrae i movimenti di magazzino per tutte le location che non hanno avuto l''ultimo fermo come setup';