0

I am trying to convert some PostgreSQL into Informix and factor out the WITH statements in the query below.

I have been unable to locate a solution to factoring out multiple with statements.

The user account to do this does not have 'create table' permissions.

WITH bear AS 
(SELECT
lv.vct_id
lsetup.wrk_id,
lobservation.id AS obs_id,
ladoption.factor_1 AS bear_corr,
lsurvey.dataset_series
FROM crs_vector cv
INNER JOIN crs_observation co
ON cv.id = co.vct_id
INNER JOIN crs_setup cs
ON (co.stp_id_local = cs.id AND lobservation.obt_sub_type = 'BEAR')
INNER JOIN crs_work cw
ON (cs.wrk_id = cw.id)
LEFT JOIN crs_adoption ca
ON (co.id = ca.obn_id_new)
LEFT JOIN crs_survey cs ON
ca.sur_wrk_id_orig = cs.wrk_id
WHERE cw.id = 1672933 AND co.obt_sub_type = 'BEAR'),

dist AS 
(SELECT
lv.vct_id
lsetup.wrk_id,
lobservation.id AS obs_id,
ladoption.factor_1 AS bear_corr,
lsurvey.dataset_series
FROM crs_vector cv
INNER JOIN crs_observation co
ON cv.id = co.vct_id
INNER JOIN crs_setup cs
ON (co.stp_id_local = cs.id AND lobservation.obt_sub_type = 'DIST')
INNER JOIN crs_work cw
ON (cs.wrk_id = cw.id)
LEFT JOIN crs_adoption ca
ON (co.id = ca.obn_id_new)
LEFT JOIN crs_survey cs ON
ca.sur_wrk_id_orig = cs.wrk_id
WHERE cw.id = 1672933 AND co.obt_sub_type = 'DIST'),

SELECT 
bear.vct_id
bear.wrk_id,
bear.obs_id,
bear.factor_1 AS bear_corr,
bear.dataset_series
dist.wrk_id,
dist.obs_id,
dist.factor_1 AS bear_corr,
dist.dataset_series
FROM   bear
FULL OUTER JOIN   dist
ON (bear.vct_id =  dist.vct_id AND bear.wrk_id = dist.wrk_id);
3
  • OK - give us the complete DDL for your tables (i.e. with indexes). You could make this question a lot simpler by cutting out all of the non-key non-joining fields! Just a thought? p.s. welcome to the forum! :-)
    – Vérace
    Jan 14, 2020 at 6:31
  • The “best" solution would be to upgrade to Informix 14.10 which supports the WITH clause. It would give you less of a headache. Jan 14, 2020 at 10:43
  • I have simplified the query above and also shared the resulting answer below. Regarding sharing a complete DDL - have you got any tips for creating a DDL for a subset of an enormous database? This particular DB is about 600gb and these queries hit about 100gb of it.
    – nstillwell
    Feb 13, 2020 at 1:10

2 Answers 2

0

I might be wrong but give a shot

SELECT 
bear.wrk_id,
bear.vct_id,
bear.bearing,
bear.bearing_datum, 
dist.dist_equipment_type,
bear.bear_equipment_type,
dist.distance,
bear.surveyed_class AS bear_surveyed_class,
bear.geodetic_class AS bear_geodetic_class,
bear.adopt_bear_wrk_id,
bear.bear_adopt_plan,
dist.surveyed_class AS dist_surveyed_class,
dist.geodetic_class AS dist_geodetic_class,
dist.adopt_dist_wrk_id,
dist.adopt_dist_wrk_id,
bear.cos_id AS cos_id,
bear.ref_datetime,
bear.stp_id_local AS bear_stp_id_local,
bear.stp_id_remote AS bear_stp_id_remote,
dist.stp_id_local AS dist_stp_id_local,
dist.stp_id_remote AS dist_stp_id_remote
(
SELECT
lsetup.wrk_id,
lsetup.equipment_type AS bear_equipment_type,
lobservation.id AS obs_id,
lobservation.obt_sub_type,
lobservation.cos_id,
lobservation.vct_id,
lobservation.ref_datetime,
lobservation.surveyed_class,
lobservation.geodetic_class,
lobservation.value_1 AS bearing,
lobservation.value_2 AS bear1,
lobservation.value_3 AS bear2,
lobservation.value_1 as dd,
lobservation.stp_id_local,
lobservation.stp_id_remote,
ladoption.factor_1 AS bear_corr,
ladoption.sur_wrk_id_orig AS adopt_bear_wrk_id,
lcsystem.name AS bearing_datum,
concat(TRIM( TRAILING ' ' FROM CAST (lsurvey.dataset_series AS TEXT)) , ' ' , lsurvey.dataset_id) AS bear_adopt_plan
FROM crs_observation lobservation 
INNER JOIN crs_setup lsetup
ON (lobservation.stp_id_local = lsetup.id AND lobservation.obt_sub_type = 'BEAR' AND lobservation.surveyed_class <> 'PSED' )
INNER JOIN crs_work lwork
ON (lsetup.wrk_id = lwork.id AND lwork.status <> 'REQU')
LEFT JOIN crs_adoption ladoption
ON (lobservation.id = ladoption.obn_id_new)
LEFT JOIN crs_coordinate_sys lcsystem
ON (lobservation.cos_id = lcsystem.id)
 LEFT JOIN crs_survey lsurvey ON
ladoption.sur_wrk_id_orig = lsurvey.wrk_id
WHERE lwork.id = 1672933 AND lobservation.obt_sub_type = 'BEAR' AND lobservation.surveyed_class <> 'PSED' bear
FULL OUTER JOIN
  (
    SELECT
lwork.id AS wrk_id,
lobservation.id AS obs_id,
lobservation.obt_sub_type,
lobservation.cos_id,
lobservation.vct_id,
lobservation.ref_datetime,
lobservation.surveyed_class,
lobservation.geodetic_class,
lobservation.value_1 AS distance,
lobservation.value_2 AS dist2,
lobservation.value_3 AS dist3,
ladoption.factor_1 AS dist_corr,
ladoption.sur_wrk_id_orig AS adopt_dist_wrk_id,
lobservation.stp_id_local,
lobservation.stp_id_remote,
lsetup.equipment_type AS dist_equipment_type,
concat(TRIM( TRAILING ' ' FROM CAST (lsurvey.dataset_series AS TEXT)) , ' ' , lsurvey.dataset_id) AS dist_adopt_plan
FROM crs_observation lobservation  
INNER JOIN crs_setup lsetup
ON (lobservation.stp_id_local = lsetup.id)
INNER JOIN crs_work lwork
ON (lsetup.wrk_id = lwork.id AND lwork.status <> 'REQU')
LEFT JOIN crs_adoption ladoption
ON (lobservation.id = ladoption.obn_id_new)
LEFT JOIN crs_survey lsurvey ON
ladoption.sur_wrk_id_orig = lsurvey.wrk_id
WHERE  lwork.id = 1672933 AND lobservation.obt_sub_type = 'SLDI'  AND lobservation.surveyed_class <> 'PSED') DIST
ON (bear.vct_id =  dist.vct_id AND bear.wrk_id = dist.wrk_id AND bear.cos_id = dist.cos_id)
)
0

Solved: Removed the WITH statements, by adding in multiple left outer joins included in a single query. The result is included below with the grouped left joins that were previously in the WITH statements separated by carriage returns.

SELECT
cv.id AS vct_id,
cb.id AS bear_obs_id,
cd.id AS dist_obs_id,
cab.sur_wrk_id_orig AS bear_sur_wrk_id_orig,
csb.dataset_series AS bear_dataset_series,
cad.sur_wrk_id_orig AS dist_sur_wrk_id_orig,
FROM crs_setup AS cs

LEFT OUTER JOIN crs_observation AS cb ON (cb.stp_id_local = cs.id AND cb.obt_sub_type = 'BEAR' )
LEFT OUTER JOIN crs_adoption AS cab ON (cab.obn_id_new = cb.id AND cb.obt_sub_type = 'BEAR' )
LEFT OUTER JOIN crs_survey AS csb ON cab.sur_wrk_id_orig = csb.wrk_id

LEFT OUTER JOIN crs_observation AS cd ON (cd.stp_id_local = cs.id AND cd.obt_sub_type = 'SLDI' )
LEFT OUTER JOIN crs_adoption AS cad ON (cad.obn_id_new = cd.id AND cd.obt_sub_type = 'SLDI' )
LEFT OUTER JOIN crs_survey AS csd ON cad.sur_wrk_id_orig = csd.wrk_id

LEFT OUTER JOIN crs_vector AS cv ON cb.vct_id = cv.id

WHERE cs.wrk_id =  1672933 
AND  ((cb.value_1 IS NOT NULL) OR (cd.value_1 IS NOT NULL))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.