Commande MERGE sous ORACLE 9.2.0.6

Version imprimable

Bonjour,

Je tente de mettre en oeuvre la commande MERGE comme alternative à un update de vue inline.

Mon script est le suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
MERGE INTO testdbruevn A
USING (
 SELECT	a.datsitu||a.cdfili||a.idestd ID,
  	(select nvl(	(select '1' from dual where 0=(select count(*) from tfixsyr where a.coddev=tfixsyr.coddev)),'0') from dual) WK_STATUT1,
  	(select nvl(	(select '1' from dual where 0=(select count(*) from tguisyr where a.codgui=tguisyr.codgui)),'0') from dual) WK_STATUT2,
  	(select nvl(	(select '1' from dual where 0=(select count(*) from tpcisyr where a.numpci=tpcisyr.numpci)),'0') from dual) WK_STATUT3,
  	(select nvl(	(select '1' from dual where (a.natinst = '50' AND a.codsec IN ('12','13','14','22','23','24')   
						OR      a.natinst = '51' AND a.codsec IN ('11','12','14','21','22','24')   
						OR      a.natinst = '52' AND a.codsec IN ('12','22')                       
						OR      a.natinst = '53' AND a.codsec IN ('12','22')                       
						OR      a.natinst = '54' AND a.codsec IN ('12','22')                       
						OR      a.natinst = '55' AND a.codsec IN ('12','13','22','23')             
						OR      a.natinst = '56' AND a.codsec IN ('11','13','21','23'))), '0') from dual)  WK_STATUT4,
  	(select nvl(	(select '1' from dual where a.natid NOT IN ('I', 'S', 'R', 'C')), '0') from dual) WK_STATUT5,
  	(select numpcemar from  tpcisyr where a.numpci=numpci) WK_NUMPCEMAR,
  	b.codtit WK_CODTIT,
  	c.valtyptit WK_CODSEDOL,
  	d.codtyppdt WK_TYPPDT,
  	(SELECT b.crsdev from tfixsyr b where  b.coddev = a.coddev and to_char(b.datsitu,'YYYYMM') like substr(a.datsitu,1,6)) WK_CRSDEV,
  	(SELECT b.nbrdec from tfixsyr b where  b.coddev = a.coddev and to_char(b.datsitu,'YYYYMM') like substr(a.datsitu,1,6)) WK_NBRDEC,
  	case d.codtyppdt
  	when 'A' then 0
  	else a.valfac
  	end WK_VALFAC
  FROM    testdbruevn a, ttitbdr b, ttitcodbdr c, ttyptitbdr d
	WHERE	substr(a.datsitu,1,6)='&1'
	AND	a.cdfili = '&2'
	AND	a.idtitre=b.codisn
	AND	a.coddev = b.ca3isodevcot
	AND	b.codtit = c.codtit
	AND	c.codtyptit=7
	AND	b.codtyptit = d.codtyptit	
	) B   
ON ((select A.datsitu||A.cdfili||A.idestd from testdbruevn A)=B.ID) 
WHEN MATCHED THEN
  UPDATE SET A.statut1=B.WK_STATUT1
  UPDATE SET A.statut2=B.WKSTATUT2
  UPDATE SET A.statut3=B.WKSTATUT3
  UPDATE SET A.statut4=B.WKSTATUT4
  UPDATE SET A.statut5=B.WKSTATUT5
  UPDATE SET A.numpcemar=B.WKNUMPCEMAR
  UPDATE SET A.codtit=B.WKCODTIT
  UPDATE SET A.codsedol=B.WKCODSEDOL
  UPDATE SET A.typpdt=B.WKTYPPDT
  UPDATE SET A.crsdev=B.WKCRSDEV
  UPDATE SET A.nbrdec=B.WKNBRDEC
  UPDATE SET A.valfac=B.WKVALFAC
WHEN NOT MATCHED THEN
 UPDATE SET A.statut1=B.WK_STATUT1
;

Cependant, à l'execution il retourne une :

ORA-00905: missing keyword

Pouvez-vous m'aider à la faire fonctionner.

Merci d'avance !

Bonjour,
essayez

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
MERGE INTO testdbruevn A
USING (
 SELECT	a.datsitu||a.cdfili||a.idestd ID,
  	(SELECT nvl(	(SELECT '1' FROM dual WHERE 0=(SELECT count(*) FROM tfixsyr WHERE a.coddev=tfixsyr.coddev)),'0') FROM dual) WK_STATUT1,
  	(SELECT nvl(	(SELECT '1' FROM dual WHERE 0=(SELECT count(*) FROM tguisyr WHERE a.codgui=tguisyr.codgui)),'0') FROM dual) WK_STATUT2,
  	(SELECT nvl(	(SELECT '1' FROM dual WHERE 0=(SELECT count(*) FROM tpcisyr WHERE a.numpci=tpcisyr.numpci)),'0') FROM dual) WK_STATUT3,
  	(SELECT nvl(	(SELECT '1' FROM dual WHERE (a.natinst = '50' AND a.codsec IN ('12','13','14','22','23','24')   
						OR      a.natinst = '51' AND a.codsec IN ('11','12','14','21','22','24')   
						OR      a.natinst = '52' AND a.codsec IN ('12','22')                       
						OR      a.natinst = '53' AND a.codsec IN ('12','22')                       
						OR      a.natinst = '54' AND a.codsec IN ('12','22')                       
						OR      a.natinst = '55' AND a.codsec IN ('12','13','22','23')             
						OR      a.natinst = '56' AND a.codsec IN ('11','13','21','23'))), '0') FROM dual)  WK_STATUT4,
  	(SELECT nvl(	(SELECT '1' FROM dual WHERE a.natid NOT IN ('I', 'S', 'R', 'C')), '0') FROM dual) WK_STATUT5,
  	(SELECT numpcemar FROM  tpcisyr WHERE a.numpci=numpci) WK_NUMPCEMAR,
  	b.codtit WK_CODTIT,
  	c.valtyptit WK_CODSEDOL,
  	d.codtyppdt WK_TYPPDT,
  	(SELECT b.crsdev FROM tfixsyr b WHERE  b.coddev = a.coddev AND to_char(b.datsitu,'YYYYMM') LIKE substr(a.datsitu,1,6)) WK_CRSDEV,
  	(SELECT b.nbrdec FROM tfixsyr b WHERE  b.coddev = a.coddev AND to_char(b.datsitu,'YYYYMM') LIKE substr(a.datsitu,1,6)) WK_NBRDEC,
  	case d.codtyppdt
  	when 'A' then 0
  	else a.valfac
  	end WK_VALFAC
  FROM    testdbruevn a, ttitbdr b, ttitcodbdr c, ttyptitbdr d
	WHERE	substr(a.datsitu,1,6)='&1'
	AND	a.cdfili = '&2'
	AND	a.idtitre=b.codisn
	AND	a.coddev = b.ca3isodevcot
	AND	b.codtit = c.codtit
	AND	c.codtyptit=7
	AND	b.codtyptit = c.codtyptit	
	) B   
ON (A.datsitu||A.cdfili||A.idestd=B.ID) 
WHEN MATCHED THEN
  UPDATE SET A.statut1=B.WK_STATUT1
  UPDATE SET A.statut2=B.WKSTATUT2
  UPDATE SET A.statut3=B.WKSTATUT3
  UPDATE SET A.statut4=B.WKSTATUT4
  UPDATE SET A.statut5=B.WKSTATUT5
  UPDATE SET A.numpcemar=B.WKNUMPCEMAR
  UPDATE SET A.codtit=B.WKCODTIT
  UPDATE SET A.codsedol=B.WKCODSEDOL
  UPDATE SET A.typpdt=B.WKTYPPDT
  UPDATE SET A.crsdev=B.WKCRSDEV
  UPDATE SET A.nbrdec=B.WKNBRDEC
  UPDATE SET A.valfac=B.WKVALFAC
WHEN NOT MATCHED THEN
 UPDATE SET A.statut1=B.WK_STATUT1
;

18/07/2007, 10h16
Cofondinde

Bonjour,

Cela ne fonctionne pas non plus,
Il retourne la même erreur et la situe (pour ce que ça vaut) au niveau du second update de la clause WHEN MATCHED THEN.

Mettre en commentaire les update de la cette clause en n'en laissant qu'un seul ne change rien. L'erreur est alors plalc au niveau de l'update de la clause WHEN NOT NATCHED.
18/07/2007, 16h18
JahPil78

A mon humble avis, la clause not matched signifie que le join entre les 2 tables n'existe pas,
donc, tu ne peux pas faire de update t1.xx = t2.yy

L'insert sera lui possible dans la clause not matched ...

Comme c'est pas beau les

Code:

1
2
3
(SELECT nvl(	(SELECT '1' FROM dual 
WHERE 0=(SELECT count(*) FROM tfixsyr WHERE a.coddev=tfixsyr.coddev)),'0') 
FROM dual) WK_STATUT1

8-)

A remplacer par

Code:

1
2
3
4
(SELECT NVL(max('1'),'0') 
FROM tfixsyr 
WHERE a.coddev=tfixsyr.codd
AND ROWNUM = 1) WK_STATUT1

La clause UPDATE doit être unique dans le merge.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE SET A.statut1=B.WK_STATUT1,
  A.statut2=B.WKSTATUT2,
  A.statut3=B.WKSTATUT3,
  A.statut4=B.WKSTATUT4,
  A.statut5=B.WKSTATUT5,
  A.numpcemar=B.WKNUMPCEMAR,
  A.codtit=B.WKCODTIT,
  A.codsedol=B.WKCODSEDOL,
  A.typpdt=B.WKTYPPDT,
  A.crsdev=B.WKCRSDEV,
  A.nbrdec=B.WKNBRDEC,
  A.valfac=B.WKVALFAC

Et le NOT MATCHED, tu peux pas faire d'UPDATE mais un INSERT