Discussion :

[Ikebukuro]

Amis DBA et développeurs bonjour,

Je voudrais, pour un fichier .sql contenant X SELECT, avoir en sortie QUE les SELECTs qui retournent une ligne. Est-ce possible?

Voici le contenu du fichier .sql : généré automatiquement par un autre SELECT.

Code :

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
 
-- Le SELECT
SQL> select 'SELECT count(*), ''' || T.table_name || ''', ''' || T.column_name || '''' || ' FROM ' || O.object_name || ' WHERE upper(' || T.column_name || ') = ''TEXAS'';' FROM dba_objects O, dba_tab_cols T where T.table_name = O.object_name and O.object_type = 'TABLE' and T.owner = 'HR' and T.data_type = 'VARCHAR2' order by T.table_name, T.column_name ;
 
-- Contenu du fichier .sql
'SELECTCOUNT(*),'''||T.TABLE_NAME||''','''||T.COLUMN_NAME||''''||'FROM'||O.OBJECT_NAME||'WHEREUPPER('||T.COLUMN_NAME||')=''TEXAS'';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS';
SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS';
SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS';
SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS';
SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS';
 
30 rows selected.

Une fois que j'exécute ce fichier, j'obtiens ceci à l'écran : plein de lignes avec 0...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
...
  COUNT(*) 'LOCATION 'POSTAL_COD
---------- --------- -----------
	 0 LOCATIONS POSTAL_CODE
 
 
  COUNT(*) 'LOCATION 'POSTAL_COD
---------- --------- -----------
	 0 LOCATIONS POSTAL_CODE
 
 
  COUNT(*) 'LOCATION 'STATE_PROVINC
---------- --------- --------------
	 1 LOCATIONS STATE_PROVINCE
...

Existe t-il une façon d'obtenir juste ceci comme résultat? :

Code :

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

1
2
3
  COUNT(*) 'LOCATION 'STATE_PROVINC
---------- --------- --------------
	 1 LOCATIONS STATE_PROVINCE

Par avance merci pour votre aide.

[McM]

Rajoute un having count(*) > 0;
mais tu auras un no_row_selected

[Ikebukuro]

Merci McM pour ta réponse.

C'est effectivement nettement mieux mais, comme tu le précises, la sortie est polluée par plusieurs lignes "no rows selected".
Est-il possible de faire mieux?

Je vais chercher car ce serait top.

[McM]

set FEED off

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SQL> select count(*) from dual having count(*) > 1;
 
no rows selected
 
SQL> set feed off
SQL> select count(*) from dual having count(*) > 1;
SQL> select count(*) from dual having count(*) > 0;
 
  COUNT(*)
----------
         1
SQL>

[Ikebukuro]

Un gros merci, c'est toi le meilleur