Looking for help with element Analysis

Analytics

Looking for help with element Analysis

Looking for help, Teradata newbie.

I wrote this code to analyze a table's element. It creates a volatile table then inserts analysis of the initial table into the VT. I have a couple of questions here.

First, how do I use the DBC to populate the first 3 fields in the INSERT statement (TABLE_NAME, ROW_NAME, NULLABLE).
Next, how can I create an iterative loop to analyze all the date fields in all tables of a database. (i.e. loop the insert statement to step through the tables in a database)

Any ideas/help will be greatly appreciated.

Thanks.

-- /* Code for volatile table */
CREATE VOLATILE TABLE VT_ELEMENT_ANALYSIS
, NO FALLBACK
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
(
TBL_NAME VARCHAR(25)
,ROW_NAME VARCHAR(25)
,NULLABLE VARCHAR(1)
,TTL_LESS_NULL DECIMAL (15, 0)
,PCT_LESS_NULL DECIMAL (6, 5)
,NUM_NULL DECIMAL (15, 0)
,PCT_NULL DECIMAL (6, 5)
,NUM_BLANK DECIMAL (15, 0)
,PCT_BLANK DECIMAL (6, 5)
,HIGH_DATE DECIMAL (15,0)
,PCT_HIGH_DATE DECIMAL (6,5)
,LOW_DATE DECIMAL (15,0)
,PCT_LOW_DATE DECIMAL (6,5)
,TTL_ROW DECIMAL (15, 0)
)
PRIMARY INDEX
(TBL_NAME, ROW_NAME)
ON
COMMIT PRESERVE ROWS
;

-- /* Code for INSERT */
INSERT INTO VT_ELEMENT_ANALYSIS
SELECT 'member_table' AS TABLE_NAME
,'row_name_DT' AS ROW_NAME
,'Y' AS NULLABLE
,COUNT (row_name_DT) AS TTL_LESS_NULL
,CAST(TTL_LESS_NULL AS DEC (15,6)) / CAST(TTL_ROW AS DEC (15,6)) AS PCT_LESS_NULL
,COUNT(
CASE
WHEN row_name_DT IS NULL THEN 1 END) AS NUM_NULL
,CAST(NUM_NULL AS DEC (15,6)) / CAST(TTL_ROW AS DEC(15,6)) AS PCT_NULL
,NULL AS NUM_BLANK
,CAST(NUM_BLANK AS DEC (15,6)) / CAST(COUNT(*) AS DEC(15,6)) AS PCT_BLANK
,COUNT (
CASE
WHEN row_name_DT = '9999-12-31' THEN 1 END) AS HIGH_DATE
,CAST (HIGH_DATE AS DEC(15,6)) / CAST(TTL_ROW AS DEC(15,6)) AS PCT_HIGH_DATE
,COUNT (
CASE
WHEN row_name_DT = '0001-01-01' THEN 1 END) AS LOW_DATE
,CAST (LOW_DATE AS DEC(15,6)) / CAST(TTL_ROW AS DEC(15,6)) AS PCT_LOW_DATE
,COUNT(*) AS TTL_ROW

FROM member_table
;