Hello List-
I'm attempting to do an INNER JOIN on one of the views in my database,
however, when I import the schema file that has it in there, it removes the
"INNER" from the statement. I've also tried to add it by modifying it via
the 'Properites' -> 'Definition' tab on the VIEW. But, when I put INNER in
front of JOIN, it doesn't save it: (INNER JOIN elementdata.area a USING
(elementid))
-- View: "elementdata.floor_info"
-- DROP VIEW elementdata.floor_info;
CREATE OR REPLACE VIEW elementdata.floor_info AS
SELECT d.elementid, d.projectname, d.floor_id, d.floor_name, d.floor_type,
d.percent_area_coverage, a.area * (d.percent_area_coverage /
100::numeric)::double precision AS area_coverage_sf, d.floor_install_date,
d.est_life_yrs, d.dimension_notes, d.floor_underlayment, d.floor_subfloor,
d.floor_warranty, d.insurance_info, d.floor_contractor, d.material_quote,
future_value((a.area * m."quote"::double precision)::numeric,
m.material_inflation_rate / 100::numeric, ('now'::text::date -
m.quote_date)::numeric / 365.25) AS current_replacement_cost,
d.floor_install_date + (d.est_life_yrs * 365.25)::integer AS
est_replacement_date, future_value((a.area * m."quote"::double
precision)::numeric, m.material_inflation_rate / 100::numeric,
(d.floor_install_date + (d.est_life_yrs * 365.25)::integer -
m.quote_date)::numeric / 365.25) AS est_replacement_cost, d.comments
FROM elementdata.data_floor_info d
JOIN elementdata.area a USING (elementid)
LEFT JOIN projectdata.material_quote m ON d.material_quote::text =
m.quote_id::text AND d.projectname::text = m.projectname::text;
ALTER TABLE elementdata.floor_info OWNER TO postgres;
But, if I put LEFT or RIGHT in front of the JOIN statement, it saves it
without any problems...
-- View: "elementdata.floor_info"
-- DROP VIEW elementdata.floor_info;
CREATE OR REPLACE VIEW elementdata.floor_info AS
SELECT d.elementid, d.projectname, d.floor_id, d.floor_name, d.floor_type,
d.percent_area_coverage, a.area * (d.percent_area_coverage /
100::numeric)::double precision AS area_coverage_sf, d.floor_install_date,
d.est_life_yrs, d.dimension_notes, d.floor_underlayment, d.floor_subfloor,
d.floor_warranty, d.insurance_info, d.floor_contractor, d.material_quote,
future_value((a.area * m."quote"::double precision)::numeric,
m.material_inflation_rate / 100::numeric, ('now'::text::date -
m.quote_date)::numeric / 365.25) AS current_replacement_cost,
d.floor_install_date + (d.est_life_yrs * 365.25)::integer AS
est_replacement_date, future_value((a.area * m."quote"::double
precision)::numeric, m.material_inflation_rate / 100::numeric,
(d.floor_install_date + (d.est_life_yrs * 365.25)::integer -
m.quote_date)::numeric / 365.25) AS est_replacement_cost, d.comments
FROM elementdata.data_floor_info d
LEFT JOIN elementdata.area a USING (elementid)
LEFT JOIN projectdata.material_quote m ON d.material_quote::text =
m.quote_id::text AND d.projectname::text = m.projectname::text;
ALTER TABLE elementdata.floor_info OWNER TO postgres;
By doing a LEFT JOIN, it is taking too long to return the result, so I only
want to do an INNER JOIN on this column...
Any thoughts/comments??
Thanks for your time and assistance!
-Jeanna
