Filter Options for Transformation Maps

The following list includes all the available filter options and transformation map conditions in the Datamaker UI.
tdm48
The following list includes all the available filter options and transformation map conditions in the Datamaker UI.
Filter Name
Description
Condition
ALL
Identifies all the columns.
CHECKED
Identifies all the checked columns.
(checked = 'Y')
VALIDATED
Identifies all the validated columns.
(validated = 'Y')
APPROVED
Identifies all the approved columns.
(approved = 'Y')
KEY
Identifies all the columns containing primary key records.
(tpd_pkey_count > 0) or (tpd_ukey_count > 0)
INDEXED
Identifies all the columns containing indexed records.
tid_index_count > 0
EMPTY
Identifies all the columns that are defined as empty.
(tcs_nullcount > 0) and (tcs_rowcount = 0)
UNIQUE
Identifies all the columns that contain unique values.
(tcs_distinct_count = tcs_rowcount) and (tcs_rowcount > 0)
GUID
Identifies all the columns that contain a GUID.
(upper (tcs_sample_analysis) like '%GUID%')
SEQUENTIAL
Identifies all the columns that contain a sequential numeric list.
(upper (tcs_sample_analysis) like '%SEQUENCE%')
DATE
Identifies all the columns that contain a date.
case (upper (left (tcd_datatype, 9))
when 'DATE","DATETIME","TIMESTAMP"
then 1 else 0) = 1
or case (pos (tcd_format, "YY")
when is > 0 then 1 else 0) = 1
or (pos (lower (tcd_column_name), "_dat") > 0
and pos (lower (tcd_column_name), "_dat")
<> pos (lower (tcd_column_name), "_data"))
DOB
Identifies all the columns that contain a DOB.
((upper (tcs_sample_analysis) like '%DOB%')
or (upper (tcd_column_name) like '%DAT%BIRTH%')
or (upper (tcd_column_name) like '%DOB%'))
TAILING DATES
Identifies all the columns that contain values with tailing dates.
(upper (tcs_sample_analysis) like '%TAILING_DATES%')
NAME
Identifies all the columns that contain a name.
((upper (tcd_column_name) like '%NAM%')
or (upper (tcd_column_name) like '%NM%'))
+ is_char_only
ADDRESS
Identifies all the columns that contain an address.
((upper (tcd_column_name) like '%ADD%')
or (upper (tcd_column_name) like '%ADR%')
or (upper (tcd_column_name) like '%CITY%')
or (upper (tcd_column_name) like '%STREET%')
or (upper (tcd_column_name) like '%STATE%')
or (upper (tcd_column_name) like '%POST%CODE%')
or (upper (tcd_column_name) like '%COUNT%Y')
OR (upper (tcs_sample_analysis) like '%ADDRESS%')
OR (upper (tcs_sample_analysis) like '%USZIP%')
or (upper (tcs_sample_analysis) like '%UKPOSTCODE%')
or (upper (tcs_sample_analysis) like '%POSTAL%'))
+ is_char_only
SSN
Identifies all the columns that contain US Social Security numbers.
((upper (tcd_column_name) like '%SS%')
and (upper (tcd_column_name) not like '%ESS%'))
or (upper (tcd_column_name) like '%SOCIAL%')
OR (upper (tcs_sample_analysis) like '%US-SSN%')
EMAIL
Identifies all the columns that contain email values.
((upper (td_table_name) like '%EMAIL%')
or (upper (tcd_column_name) like '%EMAIL')
or (upper (td_table_name) like 'EMAIL%')
OR (upper (tcs_sample_analysis) like '%EMAIL%'))
UK NINO
Identifies all the columns that contain UK NI numbers.
((upper (tcs_sample_analysis) like '%UK-NINO')
or (upper (tcd_column_name) like '%NAT%INS%')
or (upper (tcd_column_name) like '%NI%NO%'))
+ is_char_only
CREDIT CARD
Identifies all the columns that contain credit card numbers.
(upper (tcs_sample_analysis) like '%CREDITCARD%')
IBAN
Identifies all the columns that contain IBAN numbers.
(upper (tcs_sample_analysis) like '%IBAN%')
FINANCIAL DATA
Identifies all the columns that contain financial data.
(upper (tcd_column_name) like '%ACCOUNT%')
or (upper (tcd_column_name) like '%SALARY%')
or (upper (tcd_column_name) like '%REVENUE%')
or (upper (tcd_column_name) like '%PROFIT%')
or (upper (tcd_column_name) like '%SALES%')
or (upper (tcd_column_name) like '%TRANSACTION%')
PHONE NUMBER
Identifies all the columns that contain phone numbers.
(upper (tcd_column_name) like '%PHONE%')
or (upper(tcd_column_name) like '%NUMBER%')
or (upper (tcs_sample_analysis) like '%PHONE%')
HIGH DISTINCT COUNT (CHARS)
Identifies all the columns with high distinct character data types.
(upper (tcs_sample_analysis) like '%HIGHDISTINCT_C%')
HIGH DISTINCT COUNT (NUMERIC)
Identifies all the columns with high distinct numeric data types.
(upper (tcs_sample_analysis) like '%HIGHDISTINCT_N%')
MIX OF ALPHANUMERIC
Identifies all the columns that contain alphanumeric data.
(upper (tcs_sample_analysis) like '%ALPHANUMERIC%')
MIXED CASE
Identifies all the columns that contain values in mixed case.
(upper (tcs_sample_analysis) like '%MIXEDCASE%')
CONTAINS SPACES
Identifies all the columns that contain spaces.
(upper (tcs_sample_analysis) like '%SPACES%')
CONTAINS SPECIAL CHARACTERS
Identifies all the columns that contain special characters.
(upper (tcs_sample_analysis) like '%SPECIALCHARS%')
NUMERIC CHARACTER
Identifies all the columns that contain numeric characters.
(upper (tcs_sample_analysis) like '%C-NUMERIC%')
FORMATTED NUMERIC DATA
Identifies all the columns that contain formatted numeric data.
(upper (tcs_sample_analysis) like '%NUM-PATTERN%')
TEXT
Identifies all the columns that contain text.
((upper (tcd_datatype) like '%CHAR%') and (tcd_precision > 254))
or (upper (tcd_datatype) like '%STRING%')
or (upper (tcd_datatype) like '%TEXT%')
or (upper (tcd_datatype) like '%CLOB%')
VALUES FROM SEED LIST
Identifies all the columns that contain values from seed lists.
<seedlist>
TRANSFORMED
Identifies all the columns that contain transformed values.
<transformed>
CUSTOM
Allows you to create a customized filter.
In this table, is_char_only is short for the following expression:
and (NOT (case (upper (left (tcd_datatype, 9)) when "DATE","DATETIME","TIMESTAMP","NUMBER","NUMERIC","INTEGER" then 1 else 0) = 1 or case (pos (tcd_format, "YY") when is > 0 then 1 else 0) = 1))
Relevant Columns
checked
case when tfc_auth_stage >= 1 then 'Y' else 'N' end
validated
case when tfc_auth_stage >= 2 then 'Y' else 'N' end
approved
case when tfc_auth_stage >= 3 then 'Y' else 'N' end
td_table_name
coalesce (TFC_TABLE_NAME, TD_TABLE_NAME)
tcd_column_name
coalesce (TFC_COLUMN_NAME, TCD_COLUMN_NAME)
tcd_column_seq
tcd_datatype
tcd_precision
tcd_scale
tcd_nullable
CASE when tcd_nullable = '0' then 'N' else 'Y' end
TFC_ACTION
In gtrep_transformation_column table
TFC_DEFAULT
In gtrep_transformation_column table
TFC_FUNCTION
In gtrep_transformation_column table
tree_icon
case when tfd_fkey_count + tfd_rel_count > 0 then 'treeview16.gif' else null end
tfd_rel_count
(select count (rel_name)
From gtrep_relationship
join gtrep_rel_column on rc_rel_id = rel_id
where rc_parent_column = tcd_column_name
and rel_parent_table = td2.td_table_name
and rel_proj_id in (select pv_proj_id from gtrep_project_version where pv_id = <projectVersion>))
tfd_fkey_count
(select count (tfd_fkey_name)
From gtrep_table_fkey_def
where tfd_ref_column_name = tcd_column_name
and tfd_ref_table_name = td2.td_table_name
and tfd_pv_id == <projectVersion>)
TFC_XREF
In gtrep_transformation_column table
TFC_XREF_IDENT
In gtrep_transformation_column table
TFC_KEEPNULLS
In gtrep_transformation_column table
TFC_LIST_COLNO
In gtrep_transformation_column table
TFC_OVERRIDE_LOOKUP
In gtrep_transformation_column table
TFC_UNIQUE_COLS
In gtrep_transformation_column table
TFC_NOTES
In gtrep_transformation_column table
TFC_PREFORMAT
In gtrep_transformation_column table
TCD_FORMAT
In gtrep_transformation_column table
where_clause_yes
case when coalesce (tfc_where_id, 0) > 0 then 'Y' else '' end
TFC_WHERE_SEQ
In gtrep_transformation_column table
tfc_where_clause
pk_gtrep_cl.f_get_data (TFC_WHERE_ID)
tcs_sample_analysis
(SELECT max (tcs_sample_value)
FROM gtrep_tc_sample
WHERE gtrep_tc_sample.tcs_sample_type = 'analysis'
AND gtrep_tc_sample.tcs_column_name = tcd_column_name
AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
AND gtrep_tc_sample.tcs_sample_date in (
SELECT max(tcs_sample_date)
FROM gtrep_tc_sample
WHERE tcs_sample_type = 'analysis'
AND tcs_column_name = tcd_column_name
AND tcs_table_id = td2.td_table_id))
tcs_sample
'Sample'
TPD_PKEY_COUNT
PK_CNT
PK_CNT
count (PKEY_NAME) (see reference 2)
TPD_UKEY_COUNT
UK_CNT
UK_CNT
count (ukey_name) (see reference 2)
tid_index_count
IDX_CNT
IDX_CNT
count (TID_INDEX_NAME) (see reference 2)
auth_stage
tfc_auth_stage
id
coalesce (tfc_id, 0)
tfc_xpath_element
In gtrep_transformation_column table
tfc_dateformat
In gtrep_transformation_column table
tfc_column_part_s
In gtrep_transformation_column table
tfc_column_part_l
In gtrep_transformation_column table
tcs_rowcount
(SELECT cast (max (tcs_sample_value) as numeric)
FROM gtrep_tc_sample
WHERE gtrep_tc_sample.tcs_sample_type = 'rowcount'
AND gtrep_tc_sample.tcs_column_name = tcd_column_name
AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
AND gtrep_tc_sample.tcs_sample_date in (
SELECT max(tcs_sample_date)
FROM gtrep_tc_sample
WHERE tcs_sample_type = 'rowcount'
AND tcs_column_name = tcd_column_name
AND tcs_table_id = td2.td_table_id))
tcs_distinct_count
(SELECT cast (max (tcs_sample_value) as numeric)
FROM gtrep_tc_sample
WHERE gtrep_tc_sample.tcs_sample_type = 'distcount'
AND gtrep_tc_sample.tcs_column_name = tcd_column_name
AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
AND gtrep_tc_sample.tcs_sample_date in (
SELECT max(tcs_sample_date)
FROM gtrep_tc_sample
WHERE tcs_sample_type = 'distcount'
AND tcs_column_name = tcd_column_name
AND tcs_table_id = td2.td_table_id))
tcs_nullcount
(SELECT cast (max (tcs_sample_value) as numeric)
FROM gtrep_tc_sample
WHERE gtrep_tc_sample.tcs_sample_type = 'nullcount'
AND gtrep_tc_sample.tcs_column_name = tcd_column_name
AND gtrep_tc_sample.tcs_table_id = td2.td_table_id
AND gtrep_tc_sample.tcs_sample_date in (
SELECT max(tcs_sample_date)
FROM gtrep_tc_sample
WHERE tcs_sample_type = 'nullcount'
AND tcs_column_name = tcd_column_name
AND tcs_table_id = td2.td_table_id ))
tfc_order
In gtrep_transformation_column table
Reference 1 - gtrep_transfomation_column
  • tfc_transformation_map
  • tfc_proj_id
  • tfc_table_name
  • tfc_pv_id
  • tfc_column_name
  • tfc_where_seq
  • tfc_action
  • tfc_default
  • tfc_function
  • tfc_list_colno
  • tfc_keep_nulls
  • tfc_xref
  • tfc_xref_ident
  • tfc_unique_cols
  • tfc_notes
  • tfc_override_lookup
  • date_created
  • who_created
  • program_created
  • date_updated
  • who_updated
  • program_updated
  • tfc_auth_stage
  • tfc_id
  • tfd_dateformat
  • tfc_xpath_element
  • tfc_column_part_l
  • tfc_column_part_s
  • tfc_preformat
  • tfc_where_id
  • tfc_order
Reference 2
select TD_TABLE_ID, count (TID_INDEX_NAME) IDX_CNT, count (PKEY_NAME) PK_CNT, count (ukey_name) UK_CNT from gtrep_table_def left outer join gtrep_table_ind_def on tid_table_id = td_table_id left outer join ( select tpd_table_id, tpd_pukey_name pkey_name from gtrep_table_pukey_def where tpd_pv_id = <projectVersion> and tpd_pukey_type = 'P' ) pk on pk.tpd_table_id = td_table_id left outer join ( select tpd_table_id, tpd_pukey_name ukey_name from gtrep_table_pukey_def where tpd_pv_id = <projectVersion> and tpd_pukey_type = 'U' ) uk on uk.tpd_table_id = td_table_id where (td_table_id in (<list of tableIds>) or (coalesce(:ps_all,'N')='ALL')) and td_pv_id = <projectVersion> group by TD_TABLE_ID
Reference 3 - Main Query
SELECT 'columns16.gif' col_icon, 'add16.gif' new_icon, 'delete16.gif' delete_icon, case when tfc_auth_stage >= 1 then 'Y' else 'N' end as checked, case when tfc_auth_stage >= 2 then 'Y' else 'N' end as validated, case when tfc_auth_stage >= 3 then 'Y' else 'N' end as approved, coalesce (TFC_TABLE_NAME, TD_TABLE_NAME) as td_table_name, coalesce (TFC_COLUMN_NAME, TCD_COLUMN_NAME) as tcd_column_name, tcd_column_seq, tcd_datatype, tcd_precision, tcd_scale, tcd_nullable, TFC_ACTION, TFC_DEFAULT, TFC_FUNCTION, case when tfd_fkey_count + tfd_rel_count > 0 then 'treeview16.gif' else null end tree_icon, TFC_XREF, TFC_XREF_IDENT, TFC_KEEPNULLS, TFC_LIST_COLNO, TFC_OVERRIDE_LOOKUP, TFC_UNIQUE_COLS, TFC_NOTES, TFC_PREFORMAT, TCD_FORMAT, case when coalesce (tfc_where_id, 0) > 0 then 'Y' else '' end where_clause_yes, TFC_WHERE_SEQ, pk_gtrep_cl.f_get_data (TFC_WHERE_ID) tfc_where_clause, tcs_sample_analysis, 'Sample' as tcs_sample, PK_CNT as TPD_PKEY_COUNT, UK_CNT as TPD_UKEY_COUNT, IDX_CNT as tid_index_count, tfc_auth_stage auth_stage, coalesce (tfc_id, 0) id, tfc_xpath_element, tfc_dateformat, tfc_column_part_s, tfc_column_part_l, tcs_rowcount, tcs_distinct_count, tcs_nullcount, tfc_order FROM ( select td2.TD_TABLE_ID, td2.TD_PROJ_ID, td2.TD_TABLE_NAME, td2.TD_PV_ID, TCD_COLUMN_NAME, TCD_COLUMN_SEQ, TCD_DATATYPE, TCD_PRECISION, TCD_SCALE, TCD_FORMAT, CASE when tcd_nullable = '0' then 'N' else 'Y' end as tcd_nullable, IDX_CNT, PK_CNT, UK_CNT, (SELECT max (tcs_sample_value) FROM gtrep_tc_sample WHERE gtrep_tc_sample.tcs_sample_type = 'analysis' AND gtrep_tc_sample.tcs_column_name = tcd_column_name AND gtrep_tc_sample.tcs_table_id = td2.td_table_id AND gtrep_tc_sample.tcs_sample_date in ( SELECT max(tcs_sample_date) FROM gtrep_tc_sample WHERE tcs_sample_type = 'analysis' AND tcs_column_name = tcd_column_name AND tcs_table_id = td2.td_table_id ) ) as tcs_sample_analysis, (SELECT cast (max (tcs_sample_value) as numeric) FROM gtrep_tc_sample WHERE gtrep_tc_sample.tcs_sample_type = 'rowcount' AND gtrep_tc_sample.tcs_column_name = tcd_column_name AND gtrep_tc_sample.tcs_table_id = td2.td_table_id AND gtrep_tc_sample.tcs_sample_date in ( SELECT max(tcs_sample_date) FROM gtrep_tc_sample WHERE tcs_sample_type = 'rowcount' AND tcs_column_name = tcd_column_name AND tcs_table_id = td2.td_table_id ) ) as tcs_rowcount, (SELECT cast (max (tcs_sample_value) as numeric) FROM gtrep_tc_sample WHERE gtrep_tc_sample.tcs_sample_type = 'distcount' AND gtrep_tc_sample.tcs_column_name = tcd_column_name AND gtrep_tc_sample.tcs_table_id = td2.td_table_id AND gtrep_tc_sample.tcs_sample_date in ( SELECT max(tcs_sample_date) FROM gtrep_tc_sample WHERE tcs_sample_type = 'distcount' AND tcs_column_name = tcd_column_name AND tcs_table_id = td2.td_table_id ) ) as tcs_distinct_count, (SELECT cast (max (tcs_sample_value) as numeric) FROM gtrep_tc_sample WHERE gtrep_tc_sample.tcs_sample_type = 'nullcount' AND gtrep_tc_sample.tcs_column_name = tcd_column_name AND gtrep_tc_sample.tcs_table_id = td2.td_table_id AND gtrep_tc_sample.tcs_sample_date in ( SELECT max(tcs_sample_date) FROM gtrep_tc_sample WHERE tcs_sample_type = 'nullcount' AND tcs_column_name = tcd_column_name AND tcs_table_id = td2.td_table_id ) ) as tcs_nullcount, (select count (rel_name) from gtrep_relationship join gtrep_rel_column on rc_rel_id = rel_id where rc_parent_column = tcd_column_name and rel_parent_table = td2.td_table_name and rel_proj_id in (select pv_proj_id from gtrep_project_version where pv_id = :pl_pv_id) ) as tfd_rel_count, (select count (tfd_fkey_name) from gtrep_table_fkey_def where tfd_ref_column_name = tcd_column_name and tfd_ref_table_name = td2.td_table_name and tfd_pv_id = :pl_pv_id ) as tfd_fkey_count from ( select TD_TABLE_ID, count (TID_INDEX_NAME) IDX_CNT, count (PKEY_NAME) PK_CNT, count (ukey_name) UK_CNT from gtrep_table_def left outer join gtrep_table_ind_def on tid_table_id = td_table_id left outer join ( select tpd_table_id, tpd_pukey_name pkey_name from gtrep_table_pukey_def where tpd_pv_id = :pl_pv_id and tpd_pukey_type = 'P') pk on pk.tpd_table_id = td_table_id left outer join (select tpd_table_id, tpd_pukey_name ukey_name from gtrep_table_pukey_def where tpd_pv_id = :pl_pv_id and tpd_pukey_type = 'U') uk on uk.tpd_table_id = td_table_id where (td_table_id in (:pl_table_id) or (coalesce(:ps_all,'N')='ALL')) and td_pv_id = :pl_pv_id group by TD_TABLE_ID ) td1 join gtrep_table_def td2 on td2.td_table_id = td1.td_table_id join gtrep_table_col_def on tcd_table_id = td1.td_table_id ) td left outer join GTREP_TRANSFORMATION_COLUMN ON TFC_TRANSFORMATION_MAP = :ps_transformation_map AND TFC_TABLE_NAME = TD_TABLE_NAME AND TFC_COLUMN_NAME = TCD_COLUMN_NAME AND TFC_PV_ID = TD_PV_ID
Arguments are
<table list ids> <projectVersion> ps_transformation_map ps_all
.
Sorting was done on
td_table_name A tcd_column_seq A has_where_clause A tfc_where_seq A