Informix v12 使用多個 with 語句替代查詢

我正在嘗試將一些 PostgreSQL 轉換為 Informix，並在下面的查詢中排除 WITH 語句。

我一直無法找到一個解決方案來分解出多個 with 語句。

執行此操作的使用者帳戶沒有“創建表”權限。

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);

我可能錯了，但試一試

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)
)

已解決：通過在單個查詢中添加多個左外連接，刪除了 WITH 語句。結果包含在下面的分組左連接中，這些左連接以前在 WITH 語句中由輸入分隔。

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))

引用自：https://dba.stackexchange.com/questions/257346