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);
Best Answer
I might be wrong but give a shot