Discussion :

[Mykhell]

Voila,
j'aimerai comparer ce le resultat dans une requete avec le resultat d'une autre avant d'effectue un update different suivant le resultat
voici mon code et les erreurs.
deso pr le manque d'accents, clavier qwerty

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
52
53
54
55
DECLARE
TYPE MultiSite IS TABLE OF ph2b_item%ROWTYPE;
mname MultiSite;
NewOrderId ph2b_item.order_id%TYPE;
NewItemId ph2b_item.item_id%TYPE := 0;
 
CURSOR cr_consolid
	IS
		SELECT
		  oid.order_id||oid.customer_id||oid.ship_date cr_id
		FROM
		(SELECT 
		  order_id,
		  customer_id,
		  ship_date, 
		  order_date
		FROM
		  ph2b_item
		GROUP BY
		  order_id,
		  customer_id,
		  ship_date, 
		  order_date) oid
		ORDER BY
		  oid.order_date;
		--FOR UPDATE OF ph2b_item;		
BEGIN
	SELECT (MAX(order_id)+1) INTO NewOrderId FROM ph2b_item;
	SELECT (i1.order_id||i1.customer_id||i1.ship_date) BULK COLLECT INTO mname 
	FROM  ph2b_item i1  JOIN  ph2b_item i2
	ON	i1.ship_date = i2.ship_date  AND  i1.customer_id = i2.customer_id AND   i1.order_id <> i2.order_id   
	WHERE i1.customer_id IN 
		(SELECT  ns.customer_id
		 FROM	 ph2_numsites_vw ns
		 WHERE   ns.num_sites>1)
	GROUP BY   (i1.order_id||i1.customer_id||i1.ship_date);
 
FOR to_consolid IN cr_consolid 
	LOOP
		FOR i IN mname.first..mname.last LOOP
			IF ((to_consolid.cr_id) = mname)			
			THEN 
				UPDATE ph2b_item i
				SET i.order_id = NewOrderId, i.item_id = NewItemId
				WHERE CURRENT OF to_consolid;
				NewItemId := NewItemId+1;
			ELSE
				UPDATE ph2b_item i
				SET i.order_id = NewOrderId
				WHERE CURRENT OF to_consolid;
			END IF;
		END LOOP;
	NewOrderId := NewOrderId + 1;
	END LOOP; 
END;

Error report:

ORA-06550: line 30, column 2:
PL/SQL: ORA-00913: too many values
ORA-06550: line 29, column 2:
PL/SQL: SQL Statement ignored
ORA-06550: line 41, column 28:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 41, column 4:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

merci pour votre aide