Passage de variables dans requête SQL

Version imprimable

Bonjour,

J'ai un bloc pl/sql qui sélectionne plusieurs valeurs ,
je voudrais dans ce même bloc récupérer ces valeurs
pour lancer un autre SELECT :
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 set serveroutput on ; DECLARE cursor cur is SELECT parentid,count(parentid) FROM cms_infoobjects6 GROUP BY parentid having count(parentid)>100 ; res1 integer ; par1 number(10) ; Lc$Req varchar2(256); cur1 cur%rowtype ; Begin for cur1 in Cur loop par1 := cur1.parentid ; exit when cur%notfound; Lc$Req:= 'SELECT count(*) from cms_infoobjects6 where parentid = '||par1 ; execute immediate Lc$Req INTO res1 ; dbms_output.put_line (par1 || ' - ' || res1) ; end loop ; end ; /
résultat :

Citation:

SQL> @C:\Oracle\scripts\plsql\Req-BO.sql
35399 - 140
2448 - 138
2386 - 149
2212 - 128
35400 - 141
35351 - 133
27 - 106
546 - 360
2461 - 193

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL>

requête voulue :
Code:

1 2 SELECT COUNT(*) FROM CMS_InfoObjects6 WHERE ParentID IN (35400,35351,27,35399,2448....) OR ScheduleStatus = 3;
je ne sais pas comment récupérer les parentid pour lancer cette derniere requête,
je ne suis pas développeur et ne connais pas trop pl/sql .

Pour ceux qui ont reconnu, ce sont des requêtes données par le support
SAP pour trouver les reports posant problème dans le CMS de BO .

cordialement

Attention à la clause IN qui est limitée à 1000 entrées.
Ta solution peut poser problème.

Sinon tu peux aussi exécuter la requête voulues directement sans faire de boucles et de SQL dynamique
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT COUNT(*) FROM cms_infoobjects6, (SELECT parentid PID, count(parentid) Count_PID FROM cms_infoobjects6 GROUP BY parentid HAVING count(parentid)>100) WHERE ParentID = PID OR ScheduleStatus = 3;
Mais j'ai un doute quant à ta question et si j'y répond bien...

31/05/2012, 12h02
ducho

Bonjour,

c'est bien cela mais curieusement le résultat n'est pas identique
entre ta requête ( je ne maitrise vraiment pas les
(select from ..select)) et le select ... where parentid in ..

le but est de récupérer les parentsid puis la somme totale des lignes impactées et de creer un job qui enverra un mail quand le bloc renvoie
quelque chose .

merci

La différence provient peut-être de cette clause

Code:

OR ScheduleStatus = 3

Qui est présente dans ta requête voulue mais pas dans le package que tu as posté

A toi de voir s'il faut la conserver
Code:

1 2 3 4 5 6 7 8 9 SELECT COUNT(*) FROM cms_infoobjects6, (SELECT parentid PID FROM cms_infoobjects6 GROUP BY parentid HAVING count(parentid)>100) WHERE ParentID = PID OR ScheduleStatus = 3;
ou pas
Code:

1 2 3 4 5 6 7 SELECT COUNT(*) FROM (SELECT parentid FROM cms_infoobjects6 GROUP BY parentid HAVING count(parentid)>100);

j' ai besoin des deux , mais le résultat est différent :

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
 
SQL> SELECT parentid,count(parentid) FROM cms_infoobjects6 GROUP BY parentid HAVING count(parentid)>100 ;
 
  PARENTID COUNT(PARENTID)
---------- ---------------
     35399             140
      2448             138
      2386             149
      2212             128
     35400             141
     35351             133
        27             106
       546             360
      2461             193
 
9 ligne(s) sÚlectionnÚe(s).
 
SQL> select count(*) from cms_infoobjects6 where parentid in (35399,2448,2386,2212,35400,35351,27,546,2461) or schedulestatus=3 ;
 
  COUNT(*)
----------
      1490
 
SQL> SELECT COUNT(*)
  2  FROM cms_infoobjects6,
  3    (SELECT
  4      parentid PID
  5    FROM cms_infoobjects6
  6    GROUP BY parentid
  7    HAVING count(parentid)>100)
  8  WHERE ParentID = PID
  9  OR ScheduleStatus = 3;
 
  COUNT(*)
----------
      1506
 
SQL>

curieux non ?

cdlt

Tu peux aussi faire ceci

Code:

1
2
3
4
5
SELECT parentid, COUNT(*) 
FROM cms.cms_infoobjects6 a
WHERE EXISTS (SELECT 1 FROM cms.cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100)
OR ScheduleStatus = 3
GROUP BY parentid

Etrange oui...

Et
Code:

1 2 3 SELECT count(*) FROM cms_infoobjects6 WHERE schedulestatus=3;
te donne 2 ?

Et celle-ci ?
Code:

1 2 SELECT COUNT(*) FROM cms_infoobjects6;

31/05/2012, 14h35
McM
pour info, chez moi la requête de Scriuiw part dans les choux (des access full et des nested loop)... j'ai killé au bout de 2min, oracle en était en long ops à 0.22% => 19.000 sec remaining :)
Bon, je n'ai pas la même volumétrie, mais la requête avec le EXISTS met moins de 15s

En fait, c'est ta (Scriuiw) requête qui est fausse, dans le cas des ScheduleStatus = 3, tu récupère toutes les lignes de la sousrequete (nbparentid > 100)
Code:

1 2 3 4 5 6 7 8 9 SELECT * FROM (SELECT 1 AS a FROM dual) t1, (SELECT 1 AS b FROM dual UNION ALL SELECT 2 AS b FROM dual) t2 WHERE t1.a = t2.b OR t1.a = 1 A B 1 1 1 2

résultat des requêtes :

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
 
SQL> SELECT count(*)
  2  FROM cms_infoobjects6
  3  WHERE schedulestatus=3;
 
  COUNT(*)
----------
         2
 
SQL> SELECT COUNT(*)
  2  FROM cms_infoobjects6;
 
  COUNT(*)
----------
      6081
 
SQL>
SQL> select count(*)  from cms_infoobjects6 where parentid in (35399,2448,2386,2212,35400,35351,27,546,2461);
 
  COUNT(*)
----------
      1488
 
SQL>

tout aussi curieux ce résultat :

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
 
SQL> SELECT parentid, COUNT(*)
  2  FROM cms_infoobjects6 a
  3  WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100)
  4  OR ScheduleStatus = 3
  5  GROUP BY parentid;
 
  PARENTID   COUNT(*)
---------- ----------
      2448        138
     35399        140
      2386        149
      3284          1
      2212        128
     35351        133
     35400        141
       546        360
        27        106
        49          1
      2461        193
 
11 ligne(s) sÚlectionnÚe(s).
 
SQL>

31/05/2012, 14h47
ducho

MCM, quelle requête est fausse ?

Citation:

En fait, c'est ta requête qui est fausse, dans le cas des ScheduleStatus = 3, tu récupère toutes les lignes de la sousrequete (nbparentid > 100)

en fait tous les parentis sélectionnés ont un shedulestatus à NULL.

de toute façon, ce comptage est aproximatif, c'est pour faire des essais
sur une base de test .
car normalement les parentid sélectionnés doivent avoir un schedulestatus à
3 ( ce sont celles qui posent pb )

en production, je devrai mettre count(*) >10000 .

sinon, est-ce possible en pl/sql de faire ce que je demande ?

encore merci de votre aide

Pourquoi curieux ?

Code:

1
2
3
4
5
6
7
8
9
10
11
2448	138	parentid avec plus de 100 lignes
35399	140	parentid avec plus de 100 lignes
2386	149	parentid avec plus de 100 lignes
3284	1	parentid avec schedule = 3
2212	128	parentid avec plus de 100 lignes
35351	133	parentid avec plus de 100 lignes
35400	141	parentid avec plus de 100 lignes
546	360	  parentid avec plus de 100 lignes
27	106	  parentid avec plus de 100 lignes
49	1	  parentid avec schedule = 3
2461	193	parentid avec plus de 100 lignes

Cette requête est fausse

Code:

1
2
3
4
5
6
7
8
SELECT COUNT(*) 
FROM cms_infoobjects6,
  (SELECT parentid PID
  FROM cms_infoobjects6 
  GROUP BY parentid 
  HAVING count(parentid)>100)
WHERE ParentID = PID
OR ScheduleStatus = 3;

31/05/2012, 14h52
Scriuiw

Code:

MCM, quelle requête est fausse ?

La mienne :aie:
Bien vu McM :ccool:

La jointure à la sous-requête ne permet pas une extraction correcte avec la clause OR

Finalement, est-ce possible de récupérer la somme des conut(*) ? :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 SQL> SELECT parentid, COUNT(*) 2 FROM cms_infoobjects6 a 3 WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100) 4 OR ScheduleStatus = 3 5 GROUP BY parentid; PARENTID COUNT(*) ---------- ---------- 2448 138 35399 140 2386 149 3284 1 2212 128 35351 133 35400 141 546 360 27 106 49 1 2461 193 11 ligne(s) sÚlectionnÚe(s).
le tout dans un bloc pl/sql .

une fois que cela fonctionnera, j'enverrai un mail , le tout dans un job.

Cela est pour remonter une alerte quand un report BO programmé échoue
(schedulestatus=3 ),
car BO réessaie toute les minutes et crée une ligne dans cette table
avec le même parentid => table explose et TBS aussi .

Je suis resté une demi-journée avec le support SAP sans BO pour corriger cette situation

Cordialement

Bien entendu, si tu ne groupes pas tu auras la somme
Code:

1 2 3 4 5 SELECT COUNT(*) FROM cms_infoobjects6 a WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100) OR ScheduleStatus = 3 ;
Tu peux aussi obtenir la somme et le détail en une requête avec un GROUP BY ROLLUP

apparement non :

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
 
 
SQL> SELECT COUNT(*)
  2  FROM cms_infoobjects6 a
  3  WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100)
  4  OR ScheduleStatus = 3
  5  GROUP BY parentid;
 
  COUNT(*)
----------
       138
       140
       149
         1
       128
       133
       141
       360
       106
         1
       193
 
11 ligne(s) sÚlectionnÚe(s).
 
SQL>

je ne connais pas ROLLUP , un vrai débutant !!

c' est bon :

Code:

1
2
3
4
5
6
7
8
 
 
select sum(x)  from 
(SELECT COUNT(*) x
FROM cms_infoobjects6 a
WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100)
OR ScheduleStatus = 3
GROUP BY parentid);

je vous remercie tous les deux et je clos, prochaine étape envoi d' un mail

Oui, mais ta première requête, tu avais juste le group by en trop.
Simplement :

Code:

1
2
3
4
5
6
7
SELECT COUNT(*) x
FROM cms_infoobjects6 a
WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100)
OR ScheduleStatus = 3
 
X
253917

Oui si tu veux un total en fin

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT parentid, COUNT(*) x
FROM cms_infoobjects6 a
WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100)
OR ScheduleStatus = 3
GROUP BY ROLLUP(parentid)
 
PARENTID	X
18	116
19	117
42	252229
47	116
48	116
49	733
9380	2
29745	5
29871	5
29954	5
249296	471
671746	2
	253917

Apparement tu est un GROUP BY addict

Code:

1
2
3
4
5
SELECT COUNT(*)
FROM cms_infoobjects6 a
WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100)
OR ScheduleStatus = 3
GROUP BY parentid;

Et sans tu te sens comment ?

Code:

1
2
3
4
SELECT COUNT(*)
FROM cms_infoobjects6 a
WHERE EXISTS (SELECT 1 FROM cms_infoobjects6 b WHERE b.parentid = a.parentid HAVING COUNT(*)>100)
OR ScheduleStatus = 3;

;)

31/05/2012, 15h51
ducho

mea culpa
Scriuiw,

je n'avais pas bien vu ta réponse .

je vous remercie ...
31/05/2012, 15h52
McM

Que veux-tu on est perfectionnistes ici :)
un petit clic sur :resolu: et c'est bon

Pas fan du OR dans ce cas là.
Que donne cette requête en terme de temps d'exécution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
With TMP as
(
  SELECT parentid 
    FROM cms_infoobjects6
GROUP BY parentid
  HAVING count(*) > 100
   UNION
  SELECT parentid 
    FROM cms_infoobjects6
   WHERE ScheduleStatus = 3
)
SELECT count(*)
  FROM TMP;

ahhh

je comprends encore moins ce code !!!!
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SQL> WITH TMP AS 2 ( 3 SELECT parentid 4 FROM cms_infoobjects6 5 GROUP BY parentid 6 HAVING count(*) > 100 7 UNION 8 SELECT parentid 9 FROM cms_infoobjects6 10 WHERE ScheduleStatus = 3 11 ) 12 SELECT count(*) 13 FROM TMP; COUNT(*) ---------- 11 SQL>
pas de soucis de temps de réponse

la requête initiale avec le or est donné par le support SAP , je ne l'ai pas remis
en cause .

merci de ta réponse ( il faudrait que je retourne en cours sql ou pl/sql)

cordialement