PROBLEM:
When I run the following script in QB and save as a gdb/shp the EOSIZE_AC field is exported as a long when it should be a float or double and so decimal values are lost. When exported as Excel it works fine.
SELECT fnai_eo_fleo_view.*, eo_shape.shape
FROM
eo LEFT OUTER JOIN
fnai_eo_fleo_view
ON eo.eo_id = fnai_eo_fleo_view.eo_id
LEFT OUTER JOIN element_subnational est
ON eo.element_subnational_id = est.element_subnational_id
LEFT OUTER JOIN shape
ON EO.SHAPE_ID = SHAPE.SHAPE_ID
LEFT OUTER JOIN eo_shape
ON EO.eo_id = eo_shape.eo_id
WHERE
-- exclude distribution prohibited
(eo.data_sensitive_reasons NOT LIKE '%PROHIBITED%'
OR eo.data_sensitive_reasons IS NULL)
-- tracked species only
AND
est.d_eo_track_status_id = 1
-- QC passed records only
--AND (eo.d_data_qc_status_id = 2 AND shape.map_qc_status = 'P')
-- limit to particular species
AND sname LIKE 'Asplenium erosum'
ORDER BY SNAME, EO_NUM
SOLUTION:
nvl(round(to_number(substr((SELECT spat_att.attribute_value FROM spatial_attribute spat_att WHERE spat_att.attribute_name = 'Hectares' and spat_att.feature_id = eo.shape_id),1,12))*2.4711,4),-9999) as eosize_acDoes this problem occur when exporting query results that include a numeric column that stores decimal values? If not, I think the calculation used within their view is likely the reason why ogr2ogr isn't able to determine the data type. I verified that they can work around this by adding an explicit cast for any column they need to be in a decimal format. For example:
SELECT fnai_eo_fleo_view.*, eo_shape.shape, cast(fnai_eo_fleo_view.eosize_acc as number(10, 4)) as eosize_acc_dec ...If it turns out the problem occurs because of the calculation, they might want to try placing the cast into the view's definition. I don't know it it'll work, but it seems worth a shot.