SQL Query to get the Flattened User Defined Table (UDT) Information
SQL Query to get the Flattened User Defined Table (UDT) Information

 566 total views

In this article we will look into querying the User Defined Table backend tables to get the flattened UDT info.

If you are new to User-Defined Table, please check out my previous article

The UDT Information is split into 4 parts as UDT Details, Row Details, Column Details and Column Instance Details. All these 4 parts reside in 4 different tables. So getting this info in single row is a challenge as each column instance will be stored on a separate row.

SQL Query to pull Flattened UDT Info

The first approach involves hard-coding the Column Names in the query and getting the max value for each column which solves the purpose by getting the column values for each row value.

SELECT /*+ MATERIALIZE */
	r.row_low_range_or_name SSN,
	MAX(CASE 
			WHEN c_tl.user_column_name = 'PERSON_NUMBER'
				THEN v.value
			ELSE ''
			END) PERSON_NUMBER,
	MAX(CASE WHEN c_tl.user_column_name = 'COUNTRY'
				THEN v.value
			ELSE ''
			END) COUNTRY,
	MAX(CASE WHEN c_tl.user_column_name = 'ADDRESS1'
				THEN v.value
			ELSE ''
			END) ADDRESS1,
	MAX(CASE WHEN c_tl.user_column_name = 'ADDRESS2'
				THEN v.value
			ELSE ''
			END) ADDRESS2,
	MAX(CASE WHEN c_tl.user_column_name = 'ADDRESS3'
				THEN v.value
			ELSE ''
			END) ADDRESS3,
	MAX(CASE WHEN c_tl.user_column_name = 'CITY'
				THEN v.value
			ELSE ''
			END) CITY,
	MAX(CASE WHEN c_tl.user_column_name = 'COUNTY'
				THEN v.value
			ELSE ''
			END) COUNTY,
	MAX(CASE WHEN c_tl.user_column_name = 'STATE'
				THEN v.value
			ELSE ''
			END) STATE,
	MAX(CASE WHEN c_tl.user_column_name = 'POSTAL'
				THEN v.value
			ELSE ''
			END) POSTAL,
	MAX(CASE 
			WHEN c_tl.user_column_name = 'EMAIL'
				THEN v.value
			ELSE ''
			END) EMAIL,
	MAX(CASE WHEN c_tl.user_column_name = 'PHONE_NUMBER'
				THEN v.value
			ELSE ''
			END) PHONE_NUMBER,
	MAX(CASE WHEN c_tl.user_column_name = 'EXTN'
				THEN v.value
			ELSE ''
			END) EXTN
FROM ff_user_tables t
INNER JOIN ff_user_rows_f r ON t.user_table_id = r.user_table_id
INNER JOIN ff_user_columns c
INNER JOIN ff_user_columns_tl c_tl ON c.user_column_id = c_tl.user_column_id ON t.user_table_id = c.user_table_id INNER JOIN ff_user_column_instances_f v ON c.user_column_id = v.user_column_id
	AND r.user_row_id = v.user_row_id WHERE t.base_user_table_name = 'XX_CUSTOM_UDT'
GROUP BY r.row_low_range_or_name
ORDER BY 1

We have to modify the above query with the exact UDT name and column names to get the right output.

If you want to get the generic column output without modifying the SQL Query for all UDT tables, then you have to structure the columns on UDT with 001, 002 and so on.. and then use the below query to get the output:

SELECT /*+ MATERIALIZE */
	t.user_table_id,
	t.base_user_table_name table_name,
	r.row_low_range_or_name source_value,
	MAX(CASE WHEN SUBSTR(c_tl.user_column_name, 1, 3) = '001'
				THEN v.value
			ELSE ''
			END) column_1_value,
	MAX(CASE WHEN SUBSTR(c_tl.user_column_name, 1, 3) = '002'
				THEN v.value
			ELSE ''
			END) column_2_value,
	MAX(CASE WHEN SUBSTR(c_tl.user_column_name, 1, 3) = '003'
				THEN v.value
			ELSE ''
			END) column_3_value,
	MAX(CASE WHEN SUBSTR(c_tl.user_column_name, 1, 3) = '004'
				THEN v.value
			ELSE ''
			END) column_4_value
FROM ff_user_tables t
INNER JOIN ff_user_rows_f r ON t.user_table_id = r.user_table_id
INNER JOIN ff_user_columns c
INNER JOIN ff_user_columns_tl c_tl ON c.user_column_id = c_tl.user_column_id ON t.user_table_id = c.user_table_id INNER JOIN ff_user_column_instances_f v ON c.user_column_id = v.user_column_id
	AND r.user_row_id = v.user_row_id 
	AND t.base_user_table_name = 'XX_CUSTOM_UDT'
GROUP BY t.user_table_id,
	t.base_user_table_name,
	r.row_low_range_or_name
ORDER BY 2,
3

In most of the cases, we do not have control over the setup of UDT, so we will end up modifying the 1st SQL and use it. However if there is a chance to setup the UDT and if lot of reports use the same UDT, then the columns can be configured starting with 001, 002, etc to make effective use of the 2nd query.

Hope this helps you when you have to query the flattened details from User-Defined Tables.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply