Red Gate forums :: View topic - Parsing error with CONNECT_BY_ROOT
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Schema Compare for Oracle
Schema Compare for Oracle forum

Parsing error with CONNECT_BY_ROOT

Search in Schema Compare for Oracle forum
Post new topic   Reply to topic
Jump to:  
Author Message
rickybibi



Joined: 11 Apr 2005
Posts: 17

PostPosted: Wed Sep 04, 2013 7:26 pm    Post subject: Parsing error with CONNECT_BY_ROOT Reply with quote

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"
Back to top
View user's profile Send private message
neil.anderson



Joined: 28 Sep 2010
Posts: 64

PostPosted: Fri Sep 06, 2013 11:23 am    Post subject: Reply with quote

Hi,

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

Thanks,
Neil
Back to top
View user's profile Send private message
rickybibi



Joined: 11 Apr 2005
Posts: 17

PostPosted: Fri Sep 06, 2013 11:47 am    Post subject: Parsing error with CONNECT_BY_ROOT Reply with quote

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

thanks
Back to top
View user's profile Send private message
neil.anderson



Joined: 28 Sep 2010
Posts: 64

PostPosted: Fri Sep 06, 2013 10:02 pm    Post subject: Reply with quote

Hi,

Please upgrade and try the latest version from here:

http://download.red-gate.com/checkforupdates/SchemaCompareforOracle/SchemaCompareforOracle_3.0.0.719.exe

I believe this may be fixed.

Thanks,
Neil
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group