Discussion :

[KingArthur]

Bonjour,


Je dois créer une requête SQL et je ne trouve pas la solution.

Le but est de savoir si le dernier caractère des données des colonnes VILLE, RUE et REGION est un A.
Le problème c'est que les colonnes VILLE, RUE et REGION sont présentes dans potentiellement plusieurs tables dans mon application CHAUSSURES.

J'ai accès à une table de référence dans ma base de données qui contient le nom des tables contenant les colonnes VILLE, RUE et REGION mais cette table peut être amené à évoluer avec le temps mais elle conserve la forme de :

Nom_Table // Nom_Colonne
TABLE_A // VILLE
TABLE_A // RUE
TABLE_B // VILLE
TABLE_B // REGION
etc

Je pensais donc utiliser un CURSOR pour générer dans une variable l'ensemble des tables et des colonnes sur lesquelles la requête va se baser ce qui donnerait un :

Code :

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

1
2
3
4
5
   CURSOR curseur IS
  SELECT regexp_substr(
    (SELECT Nom_Colonne FROM &P_E_C_Table_Param. WHERE NOM_TABLE = '&P_E_C_Table_Param.'),'[^, ]+', 1, LEVEL) FROM dual
  CONNECT BY regexp_substr(
    (SELECT Nom_Colonne FROM &P_E_C_Table_Param. WHERE NOM_TABLE = '&P_E_C_Table_Param.'), '[^, ]+', 1, LEVEL) IS NOT NULL;

Mais je n'arrive pas à faire appliquer la requête sur l'ensemble des colonnes VILLE, RUE et REGION de mon application CHAUSSURES. Je ne trouve pas la syntaxe adéquate en SQL :/
La requête devrait me sortir 0 si le dernier caractère des colonnes sélectionnées est un A et 1 si c'est autre chose.


Merci de votre aide ^^'

[escartefigue]

Le but est de savoir si le dernier caractère des données des colonnes VILLE, RUE et REGION est un A.

C'est assez curieux comme besoin, pouvez vous préciser le contexte pour éclairer notre lanterne

Le problème c'est que les colonnes VILLE, RUE et REGION sont présentes dans potentiellement plusieurs tables dans mon application CHAUSSURES.

Le plus souvent, c'est symptomatique d'une modélisation hasardeuse, et qui dit modélisation compliquée, dit requête alambiquée et couteuse.
Mais bon il faut faire avec sauf si vous pouvez corriger la modélisation, qu'en est il ?

J'ai accès à une table de référence dans ma base de données qui contient le nom des tables contenant les colonnes VILLE, RUE et REGION mais cette table peut être amené à évoluer avec le temps mais elle conserve la forme de

Tout SGBD-R possède un catalogue dans lequel on trouve notamment la liste des colonnes de chaque table. J'espère que vous n'avez pas réinventé la roue en dupliquant les informations du catalogue
Sous réserve que le même contenu fonctionnel possède toujours le même nom de colonne, une simple interrogation du catalogue permet de connaitre les tables concernées.

[McM]

J'avoue ne pas avoir bien compris le code donné. C'est un curseur pour récupérer des noms de table ?
Rien à voir donc avec la demande de rechercher les villes finissant par A

Quel est le but ?

Si c'est un script, c'est faisable avec EXECUTE IMMEDIATE car le nom de table ne peut pas être utilisé en tant que paramètre d'un SELECT
Si c'est un SELECT, tu peux t'en sortir avec une fonction PIPELINED, toujours avec de l'EXECUTE IMMEDIATE.

[KingArthur]

Pour le contexte :

Les personnes avec qui je travaille ont créé un script de redressement de données sur une application TOTO.
Je dois créer un outil qui permet de vérifier que les données ont bien été redressé comme prévu. Pour cela, le dernier caractère de certaines colonnes de certaines tables doit désormais être un "A".


Ce qui fait que je me retrouve avec plusieurs tables et parfois les colonnes VILLE, RUE et REGION (par exemple) sont présentes dans plusieurs tables...
Puisque j'ai plusieurs milliers de table, ça fait un sacré paquet de colonnes...
Et cette modélisation est intouchable.

Donc l'idée est de pouvoir cibler facilement les tables contenant les colonnes que je cherche afin de vérifier le dernier caractère toujours avec 0 si c'est OK, 1 si c'est pas le cas afin que les autres personnes puissent aller regarder pourquoi dans la table BIDULE dans la colonne VILLE à la ligne 846 le dernier caractère n'est pas devenu un A.


Je veux extraire idéalement les lignes qui n'auraient pas le bon dernier caractère avec la colonne et la table où elles sont situées dans la base de données. Si je n'extrais aucune ligne c'est que tout est OK.

La table REFERENCE contient plusieurs informations dont le nom des tables avec les colonnes qui ont été modifié. Je vois cette table comme les coordonnées des données que je dois contrôler pour gagner en performance.

[escartefigue]

En ce cas le SQL dynamique semble en effet la seule solution sauf que
- le choix d'ajouter un 'A' comme critère de traçage pénalise bigrement le suivi et les perfs
- comment fairez vous la distinction entre un 'A' ajouté et un 'A' qui pré-existait avant redressement des données (par exemple la rue d'Alésia)

Une autre démarche eût été de dupliquer les données sur une table de travail pourvue de colonne(s) supplémentaire(s) permettant de distinguer facilement les lignes modifiées de celles inchangées

[KingArthur]

En ce cas le SQL dynamique semble en effet la seule solution sauf que
- le choix d'ajouter un 'A' comme critère de traçage pénalise bigrement le suivi et les perfs

C'est malheureusement une contrainte obligatoire...

- comment fairez vous la distinction entre un 'A' ajouté et un 'A' qui pré-existait avant redressement des données (par exemple la rue d'Alésia)

Les données pré-redressement ne comportent pas de A en dernier caractère, donc pas de problème à ce niveau-là.

Une autre démarche eût été de dupliquer les données sur une table de travail pourvue de colonne(s) supplémentaire(s) permettant de distinguer facilement les lignes modifiées de celles inchangées

La création d'une table temporaire pour effectuer le contrôle est possible avec un échantillon (disons 100 000 lignes par exemple) du moment que je peux garder une trace des lignes en erreur sur un fichier text.

[McM]

Le problème est de trouver le N° de ligne 385.
Ca va être plus complexe (pas impossible, mais complexe) car il faut alors aller chercher la PK de chaque table afin de l'indiquer


Déjà voici un exemple qui permet de compter le nombre de lignes par table de l'utilisateur connecté contenant la colonne VILLE, dont un champ VILLE finit par un "A", et d'afficher les tables qui en ont au moins une (avec le nombre)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
	v_ NUMBER;
BEGIN
FOR r IN (
SELECT table_name FROM user_tab_columns c WHERE column_name = 'VILLE'
AND NOT EXISTS (SELECT 1 FROM user_views v WHERE v.view_name = c.table_name) -- Pas les Vues
AND NOT EXISTS (SELECT 1 FROM user_snapshots v WHERE v.table_name = c.table_name) -- Pas les Vues Matérialisées
)
LOOP
	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| r.table_name ||' WHERE ville LIKE ''%A''' INTO v;
  IF v > 0 THEN DBMS_OUTPUT.PUT_LINE(r.table_name || CHR(9) || v); END IF;
END LOOP;
END;

Si tu veux afficher les lignes, tu peux aussi générer un script sqlplus afin de spooler

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
	v_ NUMBER;
BEGIN
FOR r IN (
SELECT table_name FROM user_tab_columns c WHERE column_name = 'VILLE'
AND NOT EXISTS (SELECT 1 FROM user_views v WHERE v.view_name = c.table_name) -- Pas les Vues
AND NOT EXISTS (SELECT 1 FROM user_snapshots v WHERE v.table_name = c.table_name) -- Pas les Vues Matérialisées
)
LOOP
	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| r.table_name ||' WHERE ville LIKE ''%A''' INTO v;
  IF v > 0 THEN DBMS_OUTPUT.PUT_LINE( 'SELECT * FROM '|| r.table_name ||' WHERE ville LIKE ''%A'';'); END IF;
END LOOP;
END;

Ensuite le résultat du DBMS, tu l'exécutes dans sqlplus après avoir fait un spool