Respuesta: Como hacer el inner join Estas son las estructuras de las tablas que utilizo. DAVID
Código SQL:
Ver original-- Table: programa -- DROP TABLE programa; CREATE TABLE programa ( profolio CHARACTER VARYING(9) NOT NULL, mercod INTEGER NOT NULL, turcodigo CHARACTER(2) NOT NULL, oricodigo CHARACTER VARYING(10) NOT NULL, ofabcod CHARACTER VARYING(20) NOT NULL, profecha DATE, volumensolicitado BIGINT, _rechazo DOUBLE PRECISION, volumenreal NUMERIC(6,2), numtrozos INTEGER, largo INTEGER, largorecup CHARACTER VARYING(20), pzaslargo INTEGER, destino CHARACTER VARYING(6), empalillado BOOLEAN, macrolote CHARACTER VARYING(15), observaciones CHARACTER VARYING(1000), largoprod INTEGER, material INTEGER, bloqueado BOOLEAN DEFAULT FALSE, CONSTRAINT pk_programa PRIMARY KEY (profolio), CONSTRAINT fk_programa_relations_mercado FOREIGN KEY (mercod) REFERENCES mercado (mercod) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_programa_relations_orientac FOREIGN KEY (oricodigo) REFERENCES orientacionlaterales (oricodigo) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_programa_relations_turno FOREIGN KEY (turcodigo) REFERENCES turno (turcodigo) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE programa OWNER TO postgres; COMMENT ON TABLE programa IS 'Tabla de programas de aserradero creados'; -- Index: programa_pk -- DROP INDEX programa_pk; CREATE UNIQUE INDEX programa_pk ON programa USING btree (profolio); -- Index: relationship_2_fk -- DROP INDEX relationship_2_fk; CREATE INDEX relationship_2_fk ON programa USING btree (ofabcod); -- Index: relationship_3_fk -- DROP INDEX relationship_3_fk; CREATE INDEX relationship_3_fk ON programa USING btree (turcodigo); -- Index: relationship_4_fk -- DROP INDEX relationship_4_fk; CREATE INDEX relationship_4_fk ON programa USING btree (oricodigo); -- Index: relationship_6_fk -- DROP INDEX relationship_6_fk; CREATE INDEX relationship_6_fk ON programa USING btree (mercod); -- Trigger: calcula_volumen on programa -- DROP TRIGGER calcula_volumen ON programa; CREATE TRIGGER calcula_volumen AFTER INSERT OR UPDATE ON programa FOR EACH ROW EXECUTE PROCEDURE calcula_volumen_tri();
Código SQL:
Ver original-- Table: esquemaprograma -- DROP TABLE esquemaprograma; CREATE TABLE esquemaprograma ( item INTEGER NOT NULL DEFAULT 1, profolio CHARACTER VARYING(9) NOT NULL, esqcodigo INTEGER NOT NULL, patcodigo CHARACTER VARYING(4) NOT NULL, refcreacion DATE NOT NULL, rendimiento DOUBLE PRECISION NOT NULL, pzasesquema INTEGER, observaciones CHARACTER VARYING(100), CONSTRAINT pk_esquemaprograma PRIMARY KEY (item, esqcodigo, patcodigo, profolio, refcreacion, rendimiento), CONSTRAINT fk_esquemap_esquemapr_programa FOREIGN KEY (profolio) REFERENCES programa (profolio) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fk_esquemap_esquemapr_rendimie FOREIGN KEY (esqcodigo, patcodigo, refcreacion, rendimiento) REFERENCES rendimientosimulado (esqcodigo, patcodigo, refcreacion, rendimiento) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT ) WITH OIDS; ALTER TABLE esquemaprograma OWNER TO postgres; COMMENT ON TABLE esquemaprograma IS 'Tabla de los esquemas asociados a un programa'; -- Trigger: calcula_trozos on esquemaprograma -- DROP TRIGGER calcula_trozos ON esquemaprograma; CREATE TRIGGER calcula_trozos AFTER INSERT ON esquemaprograma FOR EACH ROW EXECUTE PROCEDURE calcula_trozos_tri();
Código SQL:
Ver original-- Table: simulaciones -- DROP TABLE simulaciones; CREATE TABLE simulaciones ( simcodigo serial NOT NULL, item INTEGER NOT NULL DEFAULT 1, esqcodigo INTEGER, patcodigo CHARACTER VARYING(4), profolio CHARACTER VARYING(9), refcreacion DATE, rendimiento DOUBLE PRECISION, simnombrebd CHARACTER VARYING(100), simruta CHARACTER VARYING(200), simobservacion CHARACTER VARYING(1000), simtrozosconsumo INTEGER, simvolumenconsumo DOUBLE PRECISION, simdiametropromedio DOUBLE PRECISION, fechareal DATE, turnoreal CHARACTER(2), aserrin DOUBLE PRECISION, pzasadicionalesl1 INTEGER DEFAULT 0, pzasadicionalesl2 INTEGER DEFAULT 0, astillas DOUBLE PRECISION, largoreal DOUBLE PRECISION, diametromedio DOUBLE PRECISION, anchobasa DOUBLE PRECISION, diametromin DOUBLE PRECISION, conicidad DOUBLE PRECISION, alturacortemultiple DOUBLE PRECISION, npiezascentralesesquema INTEGER, npiezaslat1esquema INTEGER, npiezaslat2esquema INTEGER, nmultiploscentrales INTEGER, npiezaslat1 INTEGER, npiezaslat2 INTEGER, porcretorno DOUBLE PRECISION, porclat1espbajo DOUBLE PRECISION, porclat1espalto DOUBLE PRECISION, porclat2espbajo DOUBLE PRECISION, porclat2espalto DOUBLE PRECISION, sumaanchoslaterales DOUBLE PRECISION, sumaanchoscentrales DOUBLE PRECISION, porcpzasempalilladas DOUBLE PRECISION, porcpzasnoempalilladas DOUBLE PRECISION, anchomincentral DOUBLE PRECISION, anchomaxcentral DOUBLE PRECISION, rendpotencial DOUBLE PRECISION, volconsumounitario DOUBLE PRECISION, rendcentral DOUBLE PRECISION, rendlateral DOUBLE PRECISION, cortecruz CHARACTER(2), porcrecupcentrales DOUBLE PRECISION, anchomediocentral DOUBLE PRECISION, npiezascentrales DOUBLE PRECISION, "VelocidadEsquemaTotal" DOUBLE PRECISION, "VelocidadSeteoLinea" DOUBLE PRECISION, CONSTRAINT pk_simulaciones PRIMARY KEY (simcodigo, item), CONSTRAINT fk_simulaci_reference_esquemap FOREIGN KEY (item, esqcodigo, patcodigo, profolio, refcreacion, rendimiento) REFERENCES esquemaprograma (item, esqcodigo, patcodigo, profolio, refcreacion, rendimiento) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE simulaciones OWNER TO postgres; COMMENT ON TABLE simulaciones IS 'Tabla de simulaciones anexadas a un programa';
Código SQL:
Ver original-- Table: produccionsimulada -- DROP TABLE produccionsimulada; CREATE TABLE produccionsimulada ( simcodigo INTEGER NOT NULL, item INTEGER NOT NULL DEFAULT 1, prodtipo CHARACTER VARYING(2) NOT NULL, prodmercado CHARACTER VARYING(20) NOT NULL, prodescuadria CHARACTER VARYING(20) NOT NULL, prodlargo NUMERIC(3,2) NOT NULL, prodpzas INTEGER, prodvolumen DOUBLE PRECISION, CONSTRAINT pk_produccionsimulada PRIMARY KEY (simcodigo, item, prodtipo, prodmercado, prodescuadria, prodlargo), CONSTRAINT fk_producci_relations_simulaci FOREIGN KEY (simcodigo, item) REFERENCES simulaciones (simcodigo, item) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE produccionsimulada OWNER TO postgres; COMMENT ON TABLE produccionsimulada IS 'Tabla detalle de los datos anexados a un programa';
Código SQL:
Ver original-- Table: "real" -- DROP TABLE "real"; CREATE TABLE "real" ( realcodigo serial NOT NULL, item INTEGER NOT NULL DEFAULT 1, esqcodigo INTEGER, patcodigo CHARACTER VARYING(4), profolio CHARACTER VARYING(9), refcreacion DATE, rendimiento DOUBLE PRECISION, consumotrozos INTEGER, descripcion CHARACTER VARYING(100), configuracion CHARACTER VARYING(100), horaini CHARACTER(5), horafin CHARACTER(5), treal INTEGER, tpotencial INTEGER, tmuerto INTEGER, tesperado INTEGER, colacion INTEGER, pzaska INTEGER, pzaskb INTEGER, rechazos INTEGER, recuperaciones INTEGER, largorecuperado INTEGER, mercado CHARACTER VARYING(50), esccant1 CHARACTER VARYING(15), esccant2 CHARACTER VARYING(15), pzasbuenascant1 INTEGER, pzasbuenascant2 INTEGER, pzasrechcant1 INTEGER, pzasrechcant2 INTEGER, pzasnewness INTEGER, volumennewness DOUBLE PRECISION, slashnewness DOUBLE PRECISION, despuntenewness DOUBLE PRECISION, observacion CHARACTER VARYING(1500), fechareal DATE, turnoreal CHARACTER(2), responsable CHARACTER VARYING(15), largoreal INTEGER, fechafin DATE, oricodigo CHARACTER VARYING(10), CONSTRAINT pk_real PRIMARY KEY (realcodigo, item), CONSTRAINT fk_real_reference_esquemap FOREIGN KEY (item, esqcodigo, patcodigo, profolio, refcreacion, rendimiento) REFERENCES esquemaprograma (item, esqcodigo, patcodigo, profolio, refcreacion, rendimiento) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE "real" OWNER TO postgres;
|