Parsing error with CONNECT_BY_ROOT

Compares and synchronizes the schemas of Oracle databases

Moderators: eddie davis, richardjm, Michael Christofides, neil.anderson

Parsing error with CONNECT_BY_ROOT

Postby rickybibi » Wed Sep 04, 2013 7:26 pm

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"
rickybibi
 
Posts: 17
Joined: Mon Apr 11, 2005 1:26 pm

Postby neil.anderson » Fri Sep 06, 2013 11:23 am

Hi,

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

Thanks,
Neil
neil.anderson
 
Posts: 64
Joined: Tue Sep 28, 2010 2:17 pm

Parsing error with CONNECT_BY_ROOT

Postby rickybibi » Fri Sep 06, 2013 11:47 am

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

thanks
rickybibi
 
Posts: 17
Joined: Mon Apr 11, 2005 1:26 pm

Postby neil.anderson » Fri Sep 06, 2013 10:02 pm

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
neil.anderson
 
Posts: 64
Joined: Tue Sep 28, 2010 2:17 pm


Return to Schema Compare for Oracle

Who is online

Users browsing this forum: No registered users and 0 guests