Thursday, June 25, 2015

Generate Basic Select Except for All-Null Columns


I’m building your standard Select statement, but I hate looking at null fields so I’m tossing those out. It does take a little while to generate depending on the size of the table and how many null fields there are. Like our client's table has about 20 null fields and 3.5M records and it took 3 minutes to write my query. I spool a generated SQL query to a .sql file and then execute it.

DEFINE schema = fss_prd
DEFINE tbl = client_table

-- Press F5 --
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TIMING OFF
SPOOL h:\test.sql

SELECT 'SELECT CASE WHEN '
    || TO_CHAR(column_id)
    || '= 1 THEN ''SELECT '' ELSE ''     , '' END ||LOWER('''
    || column_name
    || ''') FROM &schema..&tbl WHERE ROWNUM < 2 AND '
    || column_name
    || ' IS NOT NULL UNION ALL'
  FROM all_tab_columns
 WHERE owner = UPPER('&schema')
   AND table_name = UPPER('&tbl')
 ORDER BY column_id
;
SELECT 'SELECT ''  FROM &schema..&tbl;'' FROM DUAL;'
FROM DUAL;

SPOOL OFF

@h:\test

No comments:

Post a Comment