Dynamic SQL formatting issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Dynamic SQL formatting issue

  1. #1
    Join Date
    Feb 2012
    Posts
    5

    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 10:12 AM.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458

    Wink

    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

  4. #4
    Join Date
    Feb 2012
    Posts
    5
    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
  •  



Click Here to Expand Forum to Full Width