Bonjour,

J'ai la requête suivante, testée dans SQL Developper :
Code sql : 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
 
select 
	substr(pro.codpro, 2, 8) 	codpro, 
	replace(pro.design3, ';', ' ') 	libelle, 
	null 				codepere, 
	null 				monnaie, 
	null 				prix1, 
	null 				prix2, 
	null 				prix3, 
	'LITRE' 			unite1, 
	pru.coefuv 			coeff1, 
	pro.coduni 			unite2, 
	null 				coeff2, 
	null 				unite3, 
	null 				coeff3, 
	null 				unite4, 
	null 				crit1, 
	pro.coduni 			crit2, 
	pro.codzn8 			crit3, 
	pro.fampro 			crit4, 
	pro.codzn2 			crit5, 
	pro128.valzod 			crit6, 
	null 				crit7, 
	pro.codzn5 			crit8, 
	pro.codzn11 			crit9, 
	pro130.valzod 			crit10, 
	pro.codzn3 			crit11, 
	null 				crit12, 
	null 				crit13, 
	null 				crit14, 
	pro.codzn13 			crit15, 
	pro129.valzod 			crit16 
from pro
left outer join pru on pru.codsoc = pro.codsoc and pru.codpro = pro.codpro and pru.coduni = pro.coduni
left outer join zod pro131 on pro131.codsoc = pro.codsoc and pro131.typzod = 'PRO' and pro131.clezod = pro.codpro and pro131.numzod = 131 and pro131.datzod = ' '
left outer join zod pro128 on pro128.codsoc = pro.codsoc and pro128.typzod = 'PRO' and pro128.clezod = pro.codpro and pro128.numzod = 128 and pro128.datzod = ' '
left outer join zod pro130 on pro130.codsoc = pro.codsoc and pro130.typzod = 'PRO' and pro130.clezod = pro.codpro and pro130.numzod = 130 and pro130.datzod = ' '
left outer join zod pro129 on pro129.codsoc = pro.codsoc and pro129.typzod = 'PRO' and pro129.clezod = pro.codpro and pro129.numzod = 129 and pro129.datzod = ' '
/*
where pro.codsoc = 100
and pro.codpro = pro.prostk
and pro.coduni not like 'C%'
and pro.codpro not like '%00'
and nvl(pro131.valzod, 'O') = 'O'
*/
where pro.codsoc = 100
and substr(pro.codpro, 2) in ('11050030', '11060030', '11070030', '11110000', '11220020', '11240001', '11310030', '11310031', '11320030', '11320031', '11810001', '11840022', '11895201', '11895215', '11895220', '11945320', '11955220', '12540020', '12545231', '12545240', '12545330', '12580030', '12615230', '12700020', '12880020', '13130001', '13420000', '13450000', '14000001', '14040001', '14110020', '14110030', '14142801', '14142830', '14220001', '14380030', '14651001', '14730020', '14935320', '14945320', '14960020', '14960040', '15140001', '15140020', '15140030', '15300030', '15535220', '15565220', '15655101', '15655201', '15723320', '15723330', '15730020', '15746220', '16100030', '16260001', '16380030', '16390001', '16450000', '16450001', '16470001', '16470013', '16470026', '16515201', '16515215', '16515228', '16515301', '16515320', '16515330', '16516201', '16516220', '16516230', '16516301', '16516320', '', '16516330', '16525030', '16640001', '16640002', '16755001', '16810001', '16825301', '16830001', '16845201', '16845222', '16845301', '16870001', '16873300', '16873301', '16915201', '16915301', '16940027', '17010030', '17090022', '17205220', '17290130', '17290140', '17290630', '17290640', '17292840', '17310101', '17312801', '17452801', '17460101', '17462801', '17510131', '17512801', '17512831', '17630001', '17650001', '17670030', '17740030', '17740031', '18002830', '18002840', '18010030', '18060031', '18060032', '18130030', '18140030', '18140040', '18145240', '18160001', '18180001', '18180030', '18180040', '18200001', '18200030', '18200040', '18200041', '18380001', '18395201', '18395220', '18395222', '18425030', '18620030', '18620031', '18650030', '18650031', '18731830', '18740130', '18741130', '18775230', '18775330', '18900030', '18910030', '18920030', '18980030', '18990030', '19010030', '19010040', '19040030', '19040040', '19080013', '19245230', '19440130', '19441130', '19450131', '19450231', '19451131', '19451831', '19550001', '19551001', '19551020', '19551030', '19555420', '19585215', '19585228', '19585320', '19655201', '19655301', '19830001', '19831001', '19831011', '19831024', '19833320', '19833330', '19875201', '19875251', '19875401', '19885330', '19890001', '20600000', '21500000', '21600000', '21900000', '24600000', '31400000', '31500000', '36030030', '36070030', '36130030', '36220030', '36380030', '36390030', '36480030', '36490030', '36580031', '36700031', '37010030', '38350030', '38470030', '38500030', '39100000', '42000030', '42010030', '42110030', '42200030', '42210030', '43210030', '47620020', '48170030', '58060001', '58065002', '58065301', '60300031', '70130001', '70395220', '70420001', '70445001', '70445401', '70455121', '70455330', '70510001', '70520001', '70530001', '70555201', '70555301', '70565301', '70620020', '70740030', '70760001', '71540050', '71910020', '71940030', '71950030', '71980020', '72005020', '72015030', '72015230', '72090020', '72100020', '72120020', '72140020', '72230014', '72270020', '72280020', '72580030', '72640031', '72720030', '72910001', '73040001', '73050020', '73150020', '73250020', '73260020', '73270020', '73340020', '73670030', '73680030', '73910050', '74235020', '74390027', '74430030', '74450030', '74450035', '74565120', '74580029', '74580039', '74650001', '74780033', '75250030', '75260030', '75430001', '75650037', '75830001', '75910030', '75920030', '75980001', '75980030', '76560034', '76600034', '77181020', '77186030', '77186031', '77187031', '77340034', '77850001', '77850031', '77850040', '77880030', '77880040', '77900031', '77920031', '77930031', '77930040', '77960030', '77990002', '77990030', '78000000', '78090020', '78130000', '78260020', '78760020', '78770020', '78780020', '78800030', '78810030', '78820030', '78830030', '78840030', '78850030', '87202830', '87210030', '87400030', 'PRT AVEC HISO A CLASSER');

La requête me ramène 223 lignes.

J'ai un traitement PL/SQL.
Je dois modifier sa requête initiale (d'où les commentaire) afin de rattraper d'anciennes données.
Je ne peux donc pas faire autrement que de laisser ce IN monstrueux dans la requête.

Voici le code PL/SQL :
Code sql : 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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
	-- Lecture de la base
	dbms_output.put_line('Lecture de la base');
	begin
		for curPro in
		(
			select 
				substr(pro.codpro, 2, 8) 	codpro, 
				replace(pro.design3, ';', ' ') 	libelle, 
				null 				codepere, 
				null 				monnaie, 
				null 				prix1, 
				null 				prix2, 
				null 				prix3, 
				'LITRE' 			unite1, 
				pru.coefuv 			coeff1, 
				pro.coduni 			unite2, 
				null 				coeff2, 
				null 				unite3, 
				null 				coeff3, 
				null 				unite4, 
				null 				crit1, 
				pro.coduni 			crit2, 
				pro.codzn8 			crit3, 
				pro.fampro 			crit4, 
				pro.codzn2 			crit5, 
				pro128.valzod 			crit6, 
				null 				crit7, 
				pro.codzn5 			crit8, 
				pro.codzn11 			crit9, 
				pro130.valzod 			crit10, 
				pro.codzn3 			crit11, 
				null 				crit12, 
				null 				crit13, 
				null 				crit14, 
				pro.codzn13 			crit15, 
				pro129.valzod 			crit16 
			from pro
			left outer join pru on pru.codsoc = pro.codsoc and pru.codpro = pro.codpro and pru.coduni = pro.coduni
			left outer join zod pro131 on pro131.codsoc = pro.codsoc and pro131.typzod = 'PRO' and pro131.clezod = pro.codpro and pro131.numzod = 131 and pro131.datzod = ' '
			left outer join zod pro128 on pro128.codsoc = pro.codsoc and pro128.typzod = 'PRO' and pro128.clezod = pro.codpro and pro128.numzod = 128 and pro128.datzod = ' '
			left outer join zod pro130 on pro130.codsoc = pro.codsoc and pro130.typzod = 'PRO' and pro130.clezod = pro.codpro and pro130.numzod = 130 and pro130.datzod = ' '
			left outer join zod pro129 on pro129.codsoc = pro.codsoc and pro129.typzod = 'PRO' and pro129.clezod = pro.codpro and pro129.numzod = 129 and pro129.datzod = ' '
			/*
			where pro.codsoc = 100
			and pro.codpro = pro.prostk
			and pro.coduni not like 'C%'
			and pro.codpro not like '%00'
			and nvl(pro131.valzod, 'O') = 'O'
			*/
			where pro.codsoc = 100
      			and substr(pro.codpro, 2) in ('11050030', '11060030', '11070030', '11110000', '11220020', '11240001', '11310030', '11310031', '11320030', '11320031', '11810001', '11840022', '11895201', '11895215', '11895220', '11945320', '11955220', '12540020', '12545231', '12545240', '12545330', '12580030', '12615230', '12700020', '12880020', '13130001', '13420000', '13450000', '14000001', '14040001', '14110020', '14110030', '14142801', '14142830', '14220001', '14380030', '14651001', '14730020', '14935320', '14945320', '14960020', '14960040', '15140001', '15140020', '15140030', '15300030', '15535220', '15565220', '15655101', '15655201', '15723320', '15723330', '15730020', '15746220', '16100030', '16260001', '16380030', '16390001', '16450000', '16450001', '16470001', '16470013', '16470026', '16515201', '16515215', '16515228', '16515301', '16515320', '16515330', '16516201', '16516220', '16516230', '16516301', '16516320', '', '16516330', '16525030', '16640001', '16640002', '16755001', '16810001', '16825301', '16830001', '16845201', '16845222', '16845301', '16870001', '16873300', '16873301', '16915201', '16915301', '16940027', '17010030', '17090022', '17205220', '17290130', '17290140', '17290630', '17290640', '17292840', '17310101', '17312801', '17452801', '17460101', '17462801', '17510131', '17512801', '17512831', '17630001', '17650001', '17670030', '17740030', '17740031', '18002830', '18002840', '18010030', '18060031', '18060032', '18130030', '18140030', '18140040', '18145240', '18160001', '18180001', '18180030', '18180040', '18200001', '18200030', '18200040', '18200041', '18380001', '18395201', '18395220', '18395222', '18425030', '18620030', '18620031', '18650030', '18650031', '18731830', '18740130', '18741130', '18775230', '18775330', '18900030', '18910030', '18920030', '18980030', '18990030', '19010030', '19010040', '19040030', '19040040', '19080013', '19245230', '19440130', '19441130', '19450131', '19450231', '19451131', '19451831', '19550001', '19551001', '19551020', '19551030', '19555420', '19585215', '19585228', '19585320', '19655201', '19655301', '19830001', '19831001', '19831011', '19831024', '19833320', '19833330', '19875201', '19875251', '19875401', '19885330', '19890001', '20600000', '21500000', '21600000', '21900000', '24600000', '31400000', '31500000', '36030030', '36070030', '36130030', '36220030', '36380030', '36390030', '36480030', '36490030', '36580031', '36700031', '37010030', '38350030', '38470030', '38500030', '39100000', '42000030', '42010030', '42110030', '42200030', '42210030', '43210030', '47620020', '48170030', '58060001', '58065002', '58065301', '60300031', '70130001', '70395220', '70420001', '70445001', '70445401', '70455121', '70455330', '70510001', '70520001', '70530001', '70555201', '70555301', '70565301', '70620020', '70740030', '70760001', '71540050', '71910020', '71940030', '71950030', '71980020', '72005020', '72015030', '72015230', '72090020', '72100020', '72120020', '72140020', '72230014', '72270020', '72280020', '72580030', '72640031', '72720030', '72910001', '73040001', '73050020', '73150020', '73250020', '73260020', '73270020', '73340020', '73670030', '73680030', '73910050', '74235020', '74390027', '74430030', '74450030', '74450035', '74565120', '74580029', '74580039', '74650001', '74780033', '75250030', '75260030', '75430001', '75650037', '75830001', '75910030', '75920030', '75980001', '75980030', '76560034', '76600034', '77181020', '77186030', '77186031', '77187031', '77340034', '77850001', '77850031', '77850040', '77880030', '77880040', '77900031', '77920031', '77930031', '77930040', '77960030', '77990002', '77990030', '78000000', '78090020', '78130000', '78260020', '78760020', '78770020', '78780020', '78800030', '78810030', '78820030', '78830030', '78840030', '78850030', '87202830', '87210030', '87400030', 'PRT AVEC HISO A CLASSER')
		)
		loop
		dbms_output.put_line('Produit : ' || curPro.codpro);
			-- Insertion de la ligne dans le fichier
                	utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.codpro, curPro.libelle, curPro.codepere, curPro.monnaie, curPro.prix1);
                	utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.prix2, curPro.prix3, curPro.unite1, curPro.coeff1, curPro.unite2);
                	utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.coeff2, curPro.unite3, curPro.coeff3, curPro.unite4, curPro.crit1);
                	utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.crit2, curPro.crit3, curPro.crit4, curPro.crit5, curPro.crit6);
                	utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.crit7, curPro.crit8, curPro.crit9, curPro.crit10, curPro.crit11);
                	utl_file.putf(Spoint, '%s;%s;%s;%s;%s\n', curPro.crit12, curPro.crit13, curPro.crit14, curPro.crit15, curPro.crit16);
		end loop;
 
		dbms_output.put_line('Fin de lecture de la base');
 
       	exception
       		when NO_DATA_FOUND then
       			dbms_output.put_line('Aucune donnée trouvée');
       		when OTHERS then
       			dbms_output.put_line(sqlerrm);
       	end;

Sauf que là, mon fichier ne contient pas 223 lignes, mais 7699...

La requête suivante retourne aussi 7699 :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
 
select count(*) 
from pro 
left outer join pru on pru.codsoc = pro.codsoc and pru.codpro = pro.codpro and pru.coduni = pro.coduni
left outer join zod pro131 on pro131.codsoc = pro.codsoc and pro131.typzod = 'PRO' and pro131.clezod = pro.codpro and pro131.numzod = 131 and pro131.datzod = ' '
left outer join zod pro128 on pro128.codsoc = pro.codsoc and pro128.typzod = 'PRO' and pro128.clezod = pro.codpro and pro128.numzod = 128 and pro128.datzod = ' '
left outer join zod pro130 on pro130.codsoc = pro.codsoc and pro130.typzod = 'PRO' and pro130.clezod = pro.codpro and pro130.numzod = 130 and pro130.datzod = ' '
left outer join zod pro129 on pro129.codsoc = pro.codsoc and pro129.typzod = 'PRO' and pro129.clezod = pro.codpro and pro129.numzod = 129 and pro129.datzod = ' '
where codsoc = 100;

C'est à dire que... le "IN" a été tout simplement ignoré, sans aucun message d'avertissement !

Comment faire pour qu'il soit pris en compte ?

En le découpant et en mettant des OR ?

Version d'Oracle :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production