Utilisation des données d'une Nested Table

Version imprimable

Bonjour,

j'ai créé une table de cette manière:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE or replace TYPE BANNER_LIST AS TABLE OF NUMBER  
/
 
CREATE TABLE ADEXPR03.WEB_PLAN_MEDIA_MONTH_FRED
(
    ID_MEDIA                    NUMBER(10,0)   NOT NULL,
    ID_PRODUCT                  NUMBER(10,0)   NOT NULL,
    MONTH_MEDIA_NUM             NUMBER(6,0)    NOT NULL,
    TOTALUNITE                  NUMBER(10,0)       NULL,
    LIST_BANNERS                BANNER_LIST     
)
NESTED TABLE LIST_BANNERS STORE AS WEB_PLAN_MEDIA_MONTH_TAB
TABLESPACE DATAWEB01ADEXPR03
NOLOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
CACHE
NOROWDEPENDENCIES
/

quelques lignes dans cette table:

ID_MEDIA ID_PRODUCT MONTH_MEDIA_NUM LIST_BANNERS
 10000 629805 201501 BANNER_LIST(34406592, 34567375)
 10000 629805 201501 BANNER_LIST(34406592, 34567375)
 10000 629805 201502 BANNER_LIST(34406592)
 10000 629805 201503 BANNER_LIST(34406592)
 10000 629805 201503 BANNER_LIST(34406592)
 10000 629805 201504 BANNER_LIST(34406592, 34567379)
 10000 629805 201505 BANNER_LIST(34406592, 34567375, 34567379)

- comment faire en pl/sql pour ne retrouver que les lignes où il n'y a que les valeurs (34406592, 34567379)
du genre

Code:

1
2
select * from ADEXPR03.WEB_PLAN_MEDIA_MONTH_FRED
where list_banners='34406592, 34567379'/

et qu'il n'y ai que la ligne "10000 629805 201504 BANNER_LIST(34406592, 34567379)" qui ressorte

- comment faire un distinct en pl/sql pour éliminer les doublons
afin d'avoir au final:

Code:

1
2
select distinct ID_MEDIA ID_PRODUCT MONTH_MEDIA_NUM LIST_BANNERS from ADEXPR03.WEB_PLAN_MEDIA_MONTH_FRED
where list_banners='34406592, 34567379'/

ID_MEDIA ID_PRODUCT MONTH_MEDIA_NUM LIST_BANNERS
 10000 629805 201501 BANNER_LIST(34406592, 34567375)
 10000 629805 201502 BANNER_LIST(34406592)
 10000 629805 201503 BANNER_LIST(34406592)
 10000 629805 201504 BANNER_LIST(34406592, 34567379)
 10000 629805 201505 BANNER_LIST(34406592, 34567375, 34567379)

Merci, je débute avec les nested, et votre aide est précieuse.

Pour le premier besoin, je l'aurai écrit ainsi :
Code:

1 2 3 4 5 select * from ADEXPR03.web_plan_media_month_fred t where exists (select null from table(t.list_banners) u where u.column_value in (34406592, 34567379));
Pour le distinct c'est plus compliqué, je n'ai pas de solution sous la main.

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
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> 
SQL> select *
  2  from WEB_PLAN_MEDIA_MONTH_FRED
  3  where list_banners multiset intersect BANNER_LIST(34406592, 34567379) = BANNER_LIST(34406592, 34567379)
  4  /
   ID_MEDIA  ID_PRODUCT MONTH_MEDIA_NUM  TOTALUNITE LIST_BANNERS
----------- ----------- --------------- ----------- ------------
      10000      629805          201504             <Object>
      10000      629805          201505             <Object>
 
SQL> 
SQL> Select id_media,
  2         id_product,
  3         month_media_num,
  4         Cast (collect(column_value) As BANNER_LIST)
  5    From (
  6          Select distinct t.id_media,
  7                 t.id_product,
  8                 t.month_media_num,
  9                 b.column_value
 10            from WEB_PLAN_MEDIA_MONTH_FRED t,
 11                 Table (list_banners)  b
 12         )
 13   Group By id_media,
 14         id_product,
 15         month_media_num
 16  /
   ID_MEDIA  ID_PRODUCT MONTH_MEDIA_NUM CAST(COLLECT(COLUMN_VALUE)ASBA
----------- ----------- --------------- ------------------------------
      10000      629805          201501 <Object>
      10000      629805          201502 <Object>
      10000      629805          201503 <Object>
      10000      629805          201504 <Object>
      10000      629805          201505 <Object>
 
SQL>

28/04/2017, 15h22
Waldar
J'avais mal compris la première question, du coup je suis reparti de celle de mnitu pour y répondre, c'est beaucoup plus simple :
Code:

1 2 3 select * from adexpr03.web_plan_media_month_fred where list_banners = banner_list(34406592, 34567379);

Plutôt

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> 
SQL> select *
  2    from WEB_PLAN_MEDIA_MONTH_FRED
  3   where BANNER_LIST(34406592, 34567379) Submultiset list_banners
  4  /
   ID_MEDIA  ID_PRODUCT MONTH_MEDIA_NUM  TOTALUNITE LIST_BANNERS
----------- ----------- --------------- ----------- ------------
      10000      629805          201504             <Object>
      10000      629805          201505             <Object>
 
SQL>

02/05/2017, 11h01
jio77
Super, merci beaucoup!
Citation:
Envoyé par Waldar

J'avais mal compris la première question, du coup je suis reparti de celle de mnitu pour y répondre, c'est beaucoup plus simple :

Code:

1 2 3 select * from adexpr03.web_plan_media_month_fred where list_banners = banner_list(34406592, 34567379);

Bonjour,

merci pour les réponses, cela fonctionne très bien pour rechercher un ou plusieurs éléments...

en ce qui concerne le distinct, ce n'est pas ce que je cherchais mais ça répond à une autre question que je n'avais pas encore posé :)
donc très bien!

pour le distinct , ce que je cherche à faire est

si j'ai ces lignes:

ID_MEDIA ID_PRODUCT MONTH_MEDIA_NUM LIST_BANNERS
 10000 629805 201501 BANNER_LIST(34406592, 34567375)
 10000 629453 201501 BANNER_LIST(34406592, 34567375)
 10000 629805 201502 BANNER_LIST(34406592)
 10000 629805 201503 BANNER_LIST(34406592)
 10000 629878 201503 BANNER_LIST(34406592)
 10000 629805 201504 BANNER_LIST(34406592, 34567379)
 10000 629889 201504 BANNER_LIST(34406592, 34567379)
 10000 622593 201504 BANNER_LIST(34406592, 34567379)
 10000 629805 201505 BANNER_LIST(34406592, 34567379)
 10000 629805 201505 BANNER_LIST(34406592, 34567375, 34567379)

j'aimerai obtenir :

LIST_BANNERS
 BANNER_LIST(34406592, 34567375)
 BANNER_LIST(34406592)
 BANNER_LIST(34406592, 34567379)
 BANNER_LIST(34406592, 34567375, 34567379)

plutôt que

LIST_BANNERS
 BANNER_LIST(34406592, 34567375,34567379)

Merci.

Citation:

Envoyé par mnitu

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
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> 
SQL> select *
  2  from WEB_PLAN_MEDIA_MONTH_FRED
  3  where list_banners multiset intersect BANNER_LIST(34406592, 34567379) = BANNER_LIST(34406592, 34567379)
  4  /
   ID_MEDIA  ID_PRODUCT MONTH_MEDIA_NUM  TOTALUNITE LIST_BANNERS
----------- ----------- --------------- ----------- ------------
      10000      629805          201504             <Object>
      10000      629805          201505             <Object>
 
SQL> 
SQL> Select id_media,
  2         id_product,
  3         month_media_num,
  4         Cast (collect(column_value) As BANNER_LIST)
  5    From (
  6          Select distinct t.id_media,
  7                 t.id_product,
  8                 t.month_media_num,
  9                 b.column_value
 10            from WEB_PLAN_MEDIA_MONTH_FRED t,
 11                 Table (list_banners)  b
 12         )
 13   Group By id_media,
 14         id_product,
 15         month_media_num
 16  /
   ID_MEDIA  ID_PRODUCT MONTH_MEDIA_NUM CAST(COLLECT(COLUMN_VALUE)ASBA
----------- ----------- --------------- ------------------------------
      10000      629805          201501 <Object>
      10000      629805          201502 <Object>
      10000      629805          201503 <Object>
      10000      629805          201504 <Object>
      10000      629805          201505 <Object>
 
SQL>

02/05/2017, 13h12
mnitu

Citation:

Envoyé par Waldar

J'avais mal compris la première question, du coup ...

Moi aussi.
02/05/2017, 15h54
jio77

Une idée pour le distinct?

Citation:

Envoyé par mnitu

Moi aussi.
11/05/2017, 10h05
mnitu

Détaillez ce qui ne va pas. Je ne comprends pas bien votre demande vous avez 5 valeurs différentes pour la zone MONTH_MEDIA et vous sortez que 4 lignes ?

Citation:

Envoyé par mnitu

Détaillez ce qui ne va pas. Je ne comprends pas bien votre demande vous avez 5 valeurs différentes pour la zone MONTH_MEDIA et vous sortez que 4 lignes ?

bonjour,
j'ai dans la table:
ID_MEDIA ID_PRODUCT MONTH_MEDIA_NUM LIST_BANNERS
 10000 629805 201501 BANNER_LIST(34406592, 34567375)
 10000 629453 201501 BANNER_LIST(34406592, 34567375)
 10000 629805 201502 BANNER_LIST(34406592)
 10000 629805 201503 BANNER_LIST(34406592)
 10000 629878 201503 BANNER_LIST(34406592)
 10000 629805 201504 BANNER_LIST(34406592, 34567379)
 10000 629889 201504 BANNER_LIST(34406592, 34567379)
 10000 622593 201504 BANNER_LIST(34406592, 34567379)
 10000 629805 201505 BANNER_LIST(34406592, 34567379)
 10000 629805 201505 BANNER_LIST(34406592, 34567375, 34567379)
je ne récupère que la colonne LIST_BANNERS via une requête
j'aurai:
LIST_BANNERS
 BANNER_LIST(34406592, 34567375)
 BANNER_LIST(34406592, 34567375)
 BANNER_LIST(34406592)
 BANNER_LIST(34406592)
 BANNER_LIST(34406592)
 BANNER_LIST(34406592, 34567379)
 BANNER_LIST(34406592, 34567379)
 BANNER_LIST(34406592, 34567379)
 BANNER_LIST(34406592, 34567379)
 BANNER_LIST(34406592, 34567375, 34567379)
j'aimerai faire un distinct, comme pour une colonne normale afin d'avoir:
LIST_BANNERS
 BANNER_LIST(34406592, 34567375)
 BANNER_LIST(34406592)
 BANNER_LIST(34406592, 34567379)
 BANNER_LIST(34406592, 34567375, 34567379)
Mais là ça ne veux pas car il y a l'erreur
ORA-00932: types de données incohérents ; attendu : - ; obtenu : CLOB)
donc comment faire un distinct sur cette colonne.
car les solutions données sont super pour d'autres cas de figure en donnant, et vont me servir, mais pas pour ce cas particulier.

Merci.