-
Dynamic SQL formatting issue
Hi All,
I'm trying to write some dynamic SQL that reports a count of distinct values in each column for a given table.
I've written this:
------------------------------------------------
SPOOL COUNT_DISTINCTS.LST
SELECT 'SELECT '''||rpad(COLUMN_NAME,30,' ')||' '',COUNT(DISTINCT '||COLUMN_NAME||' ) FROM '||TABLE_NAME||';'
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = '&TABLE_NAME'
;
SPOOL OFF
@COUNT_DISTINCTS.LST
------------------------------------------------
Which gives this:
Code:
ACT_RTL_NON_RTL_GRPG 2
ACT_HOLDER_TYPE 1
ACT_HOLDER_CATEGORY 2
ACT_CORP_NON_CORP_IND 1
ACT_ATM_TRANS_ACT_IND 2
ACT_DORM_ACCOUNT_PRD_IND 1
-----------------------------------------------
How do I get the numbers to line up!?
Many thanks in advance.
Last edited by speedypeavey; 02-29-2012 at 11:12 AM.
-
I changed the sql to use dba_tab_columns and picked a table that I had so that it would run.
Code:
SELECT 'SELECT '''||rpad(COLUMN_NAME,30,' ')||' ''||LPAD(COUNT(DISTINCT '||COLUMN_NAME||' ), 5) FROM '||owner||'.'||TABLE_NAME||';'
FROM DBA_TAB_COLUMNS
WHERE owner='RMANCAT'
AND table_name='RSR'
ORDER BY column_id;
You separated the two columns by a comma, plus having trimspool on probably caused the spaces to go away. If you connect to two columns with a double pipe (||) and use ltrim on the count then the output will line up.
Code:
RSR_KEY 136
DBINC_KEY 1
RSR_RECID 136
RSR_STAMP 120
RSR_PKEY 49
RSR_L0KEY 22
RSR_LEVEL 3
RSR_TYPE 3
RSR_OPER 5
RSR_CMDID 52
RSR_STATUS 4
RSR_MBYTES 29
RSR_START 120
RSR_END 94
RSR_IBYTES 42
RSR_OBYTES 49
RSR_OPTIMIZED 1
RSR_OTYPE 4
RSR_SRECID 51
RSR_SSTAMP 51
RSR_ODEVTYPE 1
SITE_KEY 1
RSR_OSB_ALLOCATED 1
-
Perhaps this:
Code:
SQL> SELECT x.column_name,
2 TO_NUMBER(EXTRACTVALUE(
3 DBMS_XMLGEN.getxmltype(
4 'select count(DISTINCT '||x.column_name||') counter from '|| x.owner||'.'||x.table_name ),
5 '/ROWSET/ROW/COUNTER' ) )
6 num_distinct
7 FROM dba_tab_columns x
8 WHERE owner = 'SCOTT'
9 AND table_name = 'EMP'
10 ORDER BY x.column_id;
COLUMN_NAME NUM_DISTINCT
-------------------- ------------
EMPNO 14
ENAME 14
JOB 5
MGR 6
HIREDATE 13
SAL 12
COMM 4
DEPTNO 3
8 rows selected.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Thanks Guys,
I've gone with Gandolf's solution as it's a simple tweak to my existing code. Works a treat!
Horace.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|