Requêtes imbriquées PL/SQL

Version imprimable

29/10/2012, 16h00
johnketta

Requêtes imbriquées PL/SQL

Bonjour,
j'ai crée une requête (ici schématisée):
select Table1, table2 liées par un ID
cette requête me ramène des records contenant :
Numèro1, numèro2, qté, date, jours condition qté <> 0
jours est un champ calculé selon sysdate -date.
le problème se situe au niveau des records qui ont une qté <> 0 et la date est vide.
Si la date est vide, je dois faire une requête dans la table2 et retrouver le record précédent de celui qui contient la date vide car celui-là contient une date mais la qté est 0.
Cette table2 contient aussi les les champs Numèro1, numèro2, qté, date je dois reprendre cette date pour calculer 'jours' et utiliser le résultat dans la première requête!
comment faire pour résoudre ce problème?

merci d'avance
29/10/2012, 17h21
mnitu

Postez un petit jeu d’essai : création des tables, insertion des données etc. ainsi que le résultat attendu et les quelques explications nécessaires à la compréhension de votre problème.

requêtes imbriquées PL/SQL

Bonjour,
voici les informations:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE TABLE FAL_LOT ( FAL_LOT_ID NUMBER(12) NOT NULL, DIC_FAMILY_ID VARCHAR2(10 BYTE), DIC_LOT_CODE2_ID VARCHAR2(10 BYTE), DIC_LOT_CODE3_ID VARCHAR2(10 BYTE), FAL_ORDER_ID NUMBER(12) NOT NULL, C_LOT_STATUS VARCHAR2(10 BYTE) NOT NULL, GCO_GOOD_ID NUMBER(12) NOT NULL, LOT_PLAN_BEGIN_DTE DATE, LOT_PLAN_END_DTE DATE, LOT_PSHORT_DESCR VARCHAR2(50 BYTE), LOT_REFCOMPL VARCHAR2(100 BYTE) NOT NULL, )
Code:

1 2 3 4 5 6 7 8 9 CREATE TABLE FAL_TASK_LINK ( FAL_SCHEDULE_STEP_ID NUMBER(12) NOT NULL, * FAL_LOT_ID NUMBER(12) NOT NULL, * TAL_AVALAIBLE_QTY NUMBER(15,4), * SCS_STEP_NUMBER NUMBER(9) NOT NULL, * FAL_TASK_ID NUMBER(12) NOT NULL, * TAL_BEGIN_REAL_DATE DATE, )
Extrait des données:

Citation:

CODE_OF |NOOF |REF_ARTE |DESCR |SEQ|OPERATION |TAL_AVALAIBLE_QTY|PLAN_FIN |DATE_DÉBUT|NBRE_JOURS
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|10|Usiner dessous |0 |30.11.2012|16.10.2012|14|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|20|Laver |0 |30.11.2012|24.10.2012|6|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|30|Ebavurer guichet|473 |30.11.2012|29.10.2012|1|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|40|Laver |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|50|Adoucir |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|60|Polir |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|70|Laver |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|80|Gicler fin |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|90|Argenter |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|100|Brosser Soleil |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|110|Dorer |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|120|Visiter TF |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|130|PVD STR |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|140|Visiter après S|0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|150|Zaponner final |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|160|Visiter TF |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|170|Décalquer |0 |30.11.2012|null |null|
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|180|Visiter après d|0 |30.11.2012|null |null|
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT substr(DECODE(LOT.DIC_LOT_CODE2_ID,1,'Pré-série',2, 'Prioritaire',3,'Sous-traitance',4,'Joaillerie',5,'Rhabillage', 0,'Aucun',NULL, 'pas saisi'),1,15) as Code_OF, substr(LOT.LOT_REFCOMPL,1,15) NoOF ,substr((select GCO.GOO_MAJOR_REFERENCE FROM GCO_GOOD GCO WHERE GCO.GCO_GOOD_ID = LOT.GCO_GOOD_ID),1,15) as REF_ARTECAD ,substr(LOT.LOT_PSHORT_DESCR,1,35)as descr ,LIN.SCS_STEP_NUMBER as seq , substr((select TAS_REF from FAL_TASK TAS WHERE TAS.FAL_TASK_ID = LIN.FAL_TASK_ID),1,20) as operation ,LIN.TAL_AVALAIBLE_QTY ,LOT.LOT_PLAN_END_DTE plan_fin ,LIN.TAL_BEGIN_REAL_DATE Date_Début , TRUNC(SYSDATE)-TRUNC(LIN.TAL_BEGIN_REAL_DATE) NBRE_JOURS from FAL_LOT LOT , FAL_TASK_LINK LIN where LOT.C_LOT_STATUS = '2' and LOT.FAL_LOT_ID = LIN.FAL_LOT_ID and LIN.TAL_AVALAIBLE_QTY <> 0 and substr(LOT.LOT_REFCOMPL,1,15) = 'OF-10-200670-01' ORDER BY LIN.SCS_STEP_NUMBER asc
Avec la requête ci-dessus, j'obtiens le résultat escompté:

Citation:

CODE_OF |NOOF |REF_ARTE |DESCR |SEQ|OPERATION |TAL_AVALAIBLE_QTY|PLAN_FIN |DATE_DÉBUT|NBRE_JOURS
pas saisi|OF-10-200670-01|ACD108-10|Clifton DD9000 Bleu|30|Ebavurer guichet|473 |30.11.2012|29.10.2012|1|

Certaines fois le champ DATE_DÉBUT est vide pour la SEQ qui a une QTY <> 0, et je dois récupérer la DATE_DÉBUT du record précédent dans la table FAL_TASK_LINK
c'est à dire la DATE_DÉBUT de la SEQ 20 et calculer le NBRE_JOURS pour la SEQ 30 (QTY <> 0)

Citation:

SEQ|OPERATION |TAL_AVALAIBLE_QTY|DATE_DÉBUT|NBRE_JOURS
10 |Usiner dessous |0 |16.10.2012|14 |
20 |Laver |0 |24.10.2012|6 |
30 |Ebavurer guiche|473 |null |null|
40 |Laver |0 |null |null|
50 |Adoucir |0 |null |null|
60 |Polir |0 |null |null|
70 |Laver |0 |null |null|
80 |Gicler fin |0 |null |null|
90 |Argenter |0 |null |null|
100|Brosser Soleil |0 |null |null|
110|Dorer |0 |null |null|
120|Visiter TF |0 |null |null|
130|PVD STR |0 |null |null|
140|Visiter après S|0 |null |null|
150|Zaponner final |0 |null |null|
160|Visiter TF |0 |null |null|
170|Décalquer |0 |null |null|
180|Visiter après d|0 |null |null|

J'espère que c'est un peu plus clair :)
salutations

30/10/2012, 11h40
mnitu
Editez votre poste pour :
- Utiliser la balise code ou le bouton #
- Corriger vos ordres SQL de création des tables: il ne peut pas y avoir une virgule sur la dernière ligne avant la parenthèse fermante
- Ajouter des ordres SQL d’insertion dans les tables : insert into … values …
- Assurer la cohérence des données par rapport aux tables : en fait vos exemples des données n’ont rien à voir avec les tables crées précédemment.
Une fois finie exécutez toutes vos ordres SQL pour les tester. Si vous ne trouvez pas important de fournir un jeu d’essai correct pourquoi allons-nous perdre notre temps avec vos incohérences ?

requêtes imbriquées PL/SQL

Toutes mes excuses, je n'avais pas réalisé. :oops:
Voici les infos (simplifiées au maximum):

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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
CREATE TABLE FAL_LOT
(
FAL_LOT_ID NUMBER(12) NOT NULL,
DIC_LOT_CODE2_ID VARCHAR2(10 BYTE),
C_LOT_STATUS VARCHAR2(10 BYTE) NOT NULL,
LOT_PLAN_END_DTE DATE,
LOT_PSHORT_DESCR VARCHAR2(50 BYTE),
LOT_REFCOMPL VARCHAR2(100 BYTE) NOT NULL
)
 
--SQL Statement which produced this data:
--
--  SELECT LOT.FAL_LOT_ID,
--              LOT.DIC_LOT_CODE2_ID as CODE
--                  ,LOT.LOT_REFCOMPL
--                  ,LOT.C_LOT_STATUS
--                  ,LOT.LOT_PSHORT_DESCR 
--                  ,LOT.LOT_PLAN_END_DTE
--    from           FAL_LOT LOT
--    where     LOT.FAL_LOT_ID = 15673292
--
Insert into FAL_LOT
   (FAL_LOT_ID, 
    CODE, LOT_REFCOMPL, C_LOT_STATUS, LOT_PSHORT_DESCR, LOT_PLAN_END_DTE)
 Values
   (15673292, NULL, 'OF-10-200670-01', '2', 'Clifton DD9000 Bleu', 
    TO_DATE('11/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
 
 
 
 
 
 
CREATE TABLE FAL_TASK_LINK
(
FAL_SCHEDULE_STEP_ID NUMBER(12) NOT NULL,
 FAL_LOT_ID NUMBER(12) NOT NULL,
 TAL_AVALAIBLE_QTY NUMBER(15,4),
 SCS_STEP_NUMBER NUMBER(9) NOT NULL,
 FAL_TASK_ID NUMBER(12) NOT NULL,
 TAL_BEGIN_REAL_DATE DATE
)
 
--SQL Statement which produced this data:
--
--  SELECT LIN.FAL_LOT_ID,
--  LIN.SCS_STEP_NUMBER as seq
--                  ,LIN.TAL_AVALAIBLE_QTY
--                  ,LIN.TAL_BEGIN_REAL_DATE  Date_Début
--    from           FAL_TASK_LINK LIN
--    where     LIN.FAL_LOT_ID = 15673292
--   ORDER BY  LIN.SCS_STEP_NUMBER asc
--
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 10, 0, TO_DATE('10/16/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 20, 0, TO_DATE('10/24/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 30, 473, TO_DATE('10/29/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 40, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 50, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 60, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 70, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 80, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 90, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 100, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 110, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 120, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 130, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 140, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 150, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 160, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 170, 0, NULL);
Insert into FAL_TASK_LINK
   (FAL_LOT_ID, 
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 Values
   (15673292, 180, 0, NULL);
COMMIT;

Voici le select qui me retourne le résultat escompté:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
                substr(DECODE(LOT.DIC_LOT_CODE2_ID,1,'Pré-série',2, 'Prioritaire',3,'Sous-traitance',4,'Joaillerie',5,'Rhabillage', 0,'Aucun',NULL, 'pas saisi'),1,15) as Code_OF,
                substr(LOT.LOT_REFCOMPL,1,15) NoOF
               ,substr(LOT.LOT_PSHORT_DESCR,1,35)as descr
               ,LIN.SCS_STEP_NUMBER as seq
                ,LIN.TAL_AVALAIBLE_QTY
                ,LOT.LOT_PLAN_END_DTE     plan_fin
                ,LIN.TAL_BEGIN_REAL_DATE  Date_Début
                ,  TRUNC(SYSDATE)-TRUNC(LIN.TAL_BEGIN_REAL_DATE) NBRE_JOURS
  from FAL_LOT LOT ,
          FAL_TASK_LINK LIN
  where LOT.C_LOT_STATUS = '2' and LOT.FAL_LOT_ID = LIN.FAL_LOT_ID
    and LIN.TAL_AVALAIBLE_QTY <> 0
    and  substr(LOT.LOT_REFCOMPL,1,15) = 'OF-10-200670-01' 
ORDER BY  LIN.SCS_STEP_NUMBER asc

Résultat:
CODE_OF|NOOF|DESCR|SEQ|TAL_AVALAIBLE_QTY|PLAN_FIN|DATE_DÉBUT|NBRE_JOURS
pas saisi|OF-10-200670-01|Clifton DD9000 Bleu|30|473|30.11.2012|29.10.2012|1|

Mon problème (hypothèse), c'est quand il n'y a pas de date dans 'Date_Début' pour la requête ci-dessus, je devrais alors récupérer la date du record précedent, ici la SEQ 20 de la table LIN.

30/10/2012, 15h45
skuatamad

Je ne suis pas sûr d'avoir bien compris, est ce que LAG convient :

Code:

coalesce(LIN.TAL_BEGIN_REAL_DATE, lag(LIN.TAL_BEGIN_REAL_DATE) over (order by LIN.SCS_STEP_NUMBER))
30/10/2012, 16h00
johnketta

requêtes imbriquées PL/SQL

Non cela ne fonctionne pas, j'avais déjà essayé mais comme je ne selectionne pas tous les records de la table LIN, LAG ne voit pas le record précédent. Il faut que j'imbrique une nouvelle requête avec la condition: si LIN.TAL_BEGIN_REAL_DATE est vide alors chercher le record précédent dans LIN.
Merci pour la réponse

Je viens d'essayer de créer le jeu d'essai

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
52
53
54
55
56
57
58
59
60
61
62
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> CREATE TABLE FAL_LOT
  2  (
  3  FAL_LOT_ID NUMBER(12) NOT NULL,
  4  DIC_LOT_CODE2_ID VARCHAR2(10 BYTE),
  5  C_LOT_STATUS VARCHAR2(10 BYTE) NOT NULL,
  6  LOT_PLAN_END_DTE DATE,
  7  LOT_PSHORT_DESCR VARCHAR2(50 BYTE),
  8  LOT_REFCOMPL VARCHAR2(100 BYTE) NOT NULL
  9  )
 10  /
 
Table created
SQL> INSERT INTO FAL_LOT
  2     (FAL_LOT_ID,
  3      CODE, LOT_REFCOMPL, C_LOT_STATUS, LOT_PSHORT_DESCR, LOT_PLAN_END_DTE)
  4   VALUES
  5     (15673292, NULL, 'OF-10-200670-01', '2', 'Clifton DD9000 Bleu',
  6      TO_DATE('11/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
 
INSERT INTO FAL_LOT
   (FAL_LOT_ID,
    CODE, LOT_REFCOMPL, C_LOT_STATUS, LOT_PSHORT_DESCR, LOT_PLAN_END_DTE)
 VALUES
   (15673292, NULL, 'OF-10-200670-01', '2', 'Clifton DD9000 Bleu',
    TO_DATE('11/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))
 
ORA-00904: "CODE" : identificateur non valide
SQL> COMMIT;
 
Commit complete
SQL> CREATE TABLE FAL_TASK_LINK
  2  (
  3  FAL_SCHEDULE_STEP_ID NUMBER(12) NOT NULL,
  4   FAL_LOT_ID NUMBER(12) NOT NULL,
  5   TAL_AVALAIBLE_QTY NUMBER(15,4),
  6   SCS_STEP_NUMBER NUMBER(9) NOT NULL,
  7   FAL_TASK_ID NUMBER(12) NOT NULL,
  8   TAL_BEGIN_REAL_DATE DATE
  9  )
 10  /
 
Table created
SQL> INSERT INTO FAL_TASK_LINK
  2     (FAL_LOT_ID,
  3      SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
  4   VALUES
  5     (15673292, 10, 0, TO_DATE('10/16/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
 
INSERT INTO FAL_TASK_LINK
   (FAL_LOT_ID,
    SEQ, TAL_AVALAIBLE_QTY, DATE_DÉBUT)
 VALUES
   (15673292, 10, 0, TO_DATE('10/16/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))
 
ORA-00904: "DATE_DÉBUT" : identificateur non valide
 
SQL>

Mais comme visiblement ça ne marche pas j'ai abandonné.

Et quelque chose comme ça ?

Code:

1
2
3
4
5
       ,coalesce(LIN.TAL_BEGIN_REAL_DATE, (select max(LIN2.TAL_BEGIN_REAL_DATE) keep (dense_rank first order by LIN2.SCS_STEP_NUMBER desc)
                                             from FAL_TASK_LINK LIN2
                                            where LIN2.SCS_STEP_NUMBER < LIN.SCS_STEP_NUMBER
                                              and LIN2.TAL_BEGIN_REAL_DATE is not null)
       ) as Date_Début

08/11/2012, 12h57
johnketta

Requêtes imbriquées PL/SQL

Merci pour vos réponses, toutes intéressantes mais inefficaes dans ce cas précis. Le problème à été soumis à une autre personne et je m'en occupe plus. Je vais dans le cas ou celà sera résolu de saisir ici la solution.
08/11/2012, 14h20
mnitu

Citation:

Si vous ne trouvez pas important de fournir un jeu d’essai correct pourquoi allons-nous perdre notre temps avec vos incohérences ?