Parsing error with CONNECT_BY_ROOT

rickybibi

I received an Parsing failed with message SyntaxError. Unexpected token '.' (Line 40,
[12:33:08] ERROR: Col 34) symbol ..

Is Oracle Schema compare support CONNECT_BY_ROOT ?

Code : CREATE OR REPLACE FORCE VIEW FV_CLASSIFICATION_DECIMAL AS

SELECT DISTINCT
R.REGROUPEMENT_REFERENCE AS "CLASSIFICATION", -- Nature
R.REGROUPEMENT_REFERENCE AS "CLE_CLASSIFICATION",
R.REGROUPEMENT_DESCRIPTION AS "DESCR_CLASSIFICATION",
CASE WHEN "Regroupement_N_1" IS NOT NULL THEN '1-' || "Regroupement_N_1" END AS "NIVEAU_1",
"Regroupement_N_1" AS "CLE_NIVEAU_1",
"Regroupement_descr_N_1" AS "DESCR_NIVEAU_1",
CASE WHEN "Regroupement_N_1" IS NOT NULL THEN '2-' || "Regroupement_N_2" END AS "NIVEAU_2",
"Regroupement_N_2" AS "CLE_NIVEAU_2",
"Regroupement_descr_N_2" AS "DESCR_NIVEAU_2",
CASE WHEN "Regroupement_N_1" IS NOT NULL THEN '3-' || "Regroupement_N_3" END AS "NIVEAU_3",
"Regroupement_N_3" AS "CLE_NIVEAU_3",
"Regroupement_descr_N_3" AS "DESCR_NIVEAU_3",
CASE WHEN "Regroupement_N_1" IS NOT NULL THEN '4-' || "Regroupement_N_4" END AS "NIVEAU_4",
"Regroupement_N_4" AS "CLE_NIVEAU_4",
"Regroupement_descr_N_4" AS "DESCR_NIVEAU_4",
CASE WHEN "Regroupement_N_1" IS NOT NULL THEN '5-' || "Regroupement_N_5" END AS "NIVEAU_5",
"Regroupement_N_5" AS "CLE_NIVEAU_5",
"Regroupement_descr_N_5" AS "DESCR_NIVEAU_5"

FROM COMPTE C,
REGROUPEMENT R,
(SELECT
REGROUPEMENT_CODE,
MAX(CASE WHEN REGROUPEMENTLEVEL = 1 THEN REGROUPEMENT_IDENTIFIANT END) AS "Regroupement_N_1",
MAX(CASE WHEN REGROUPEMENTLEVEL = 2 THEN REGROUPEMENT_IDENTIFIANT END) AS "Regroupement_N_2",
MAX(CASE WHEN REGROUPEMENTLEVEL = 3 THEN REGROUPEMENT_IDENTIFIANT END) AS "Regroupement_N_3",
MAX(CASE WHEN REGROUPEMENTLEVEL = 4 THEN REGROUPEMENT_IDENTIFIANT END) AS "Regroupement_N_4",
MAX(CASE WHEN REGROUPEMENTLEVEL = 5 THEN REGROUPEMENT_IDENTIFIANT END) AS "Regroupement_N_5",
MAX(CASE WHEN REGROUPEMENTLEVEL = 1 THEN REGROUPEMENT_DESCRIPTION END) AS "Regroupement_descr_N_1",
MAX(CASE WHEN REGROUPEMENTLEVEL = 2 THEN REGROUPEMENT_DESCRIPTION END) AS "Regroupement_descr_N_2",
MAX(CASE WHEN REGROUPEMENTLEVEL = 3 THEN REGROUPEMENT_DESCRIPTION END) AS "Regroupement_descr_N_3",
MAX(CASE WHEN REGROUPEMENTLEVEL = 4 THEN REGROUPEMENT_DESCRIPTION END) AS "Regroupement_descr_N_4",
MAX(CASE WHEN REGROUPEMENTLEVEL = 5 THEN REGROUPEMENT_DESCRIPTION END) AS "Regroupement_descr_N_5"

FROM (SELECT DISTINCT
C1.REGROUPEMENT_CODE,
CONNECT_BY_ROOT R1.REGROUPEMENT_CODE REGROUPEMENT_CODE1,
CONNECT_BY_ROOT R1.REGROUPEMENT_DESCRIPTION REGROUPEMENT_DESCRIPTION,
CONNECT_BY_ROOT R1.REGROUPEMENT_IDENTIFIANT REGROUPEMENT_IDENTIFIANT,
LEVEL REGROUPEMENTLEVEL
FROM REGROUPEMENT R1, COMPTE C1
WHERE R1.REGROUPEMENT_CODE = C1.REGROUPEMENT_CODE
CONNECT BY PRIOR R1.REGROUPEMENT_CODE = R1.REGROUPEMENT_CODE_PARENT
ORDER BY C1.REGROUPEMENT_CODE, LEVEL DESC)
GROUP BY REGROUPEMENT_CODE) RG

WHERE C.REGROUPEMENT_CODE = RG.REGROUPEMENT_CODE (+)
AND C.REGROUPEMENT_CODE = R.REGROUPEMENT_CODE
AND C.TYPE_COMPTE_CODE <> 0 --Exclure les comptes de Bilan

ORDER BY "CLE_CLASSIFICATION"

neil.anderson

Hi,

Can you tell me what version of Schema Compare for Oracle you are using please?

Thanks,
Neil

rickybibi

Oh ,Sorry,
It's Oracle Schema compare 3.0.0.527.

thanks

neil.anderson

Hi,

Please upgrade and try the latest version from here:

http://download.red-gate.com/checkforup ... .0.719.exe

I believe this may be fixed.

Thanks,
Neil