Which Column in my Flattened Table caused the “Subquery used as an expression returned more than one

[Deleted User]

Jim Knicely authored this tip.

A flattened table contains columns that are derived from query-expressions that must return only one row and column value, or none. If a column’s query-expression does return more than one row you will get a “Subquery used as an expression returned more than one row” error.

If you have more than one derived column in your flattened table and you get that error, you’ll have to figure out which query-expression is the culprit as it is not indicated in the error message.

Example:

dbadmin=> \dt dim_*
               List of tables
Schema | Name  | Kind  |  Owner  | Comment
--------+-------+-------+---------+---------
public | dim_1 | table | dbadmin |
public | dim_2 | table | dbadmin |
public | dim_3 | table | dbadmin |
(3 rows)

dbadmin=> CREATE TABLE public.fact_flat
dbadmin-> (
dbadmin(>     c1 int,
dbadmin(>     dim_1_c2 varchar(10) SET USING ( SELECT dim_1.c2 FROM public.dim_1 WHERE (dim_1.c1 = fact_flat.c1)),
dbadmin(>     dim_2_c2 varchar(10) SET USING ( SELECT dim_2.c2 FROM public.dim_2 WHERE (dim_2.c1 = fact_flat.c1)),
dbadmin(>     dim_3_c2 varchar(10) SET USING ( SELECT dim_3.c2 FROM public.dim_3 WHERE (dim_3.c1 = fact_flat.c1))
dbadmin(> );
CREATE TABLE

dbadmin=> INSERT INTO public.fact_flat (c1) SELECT 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT refresh_columns('public.fact_flat', '');
ERROR 4840:  Subquery used as an expression returned more than one row

Uh oh! Which derived column is causing this error? You can find out by executing each of the derived columns individually dumping the output to a file. After all the queries have executed, you can check the output file for errors to discover the offending column(s).

Example:

[dbadmin@s18384357 ~]$ vsql -Atc "SELECT 'SELECT (' || REPLACE(column_set_using, CHR(10), '') || ') AS ' || column_name || ' FROM ' || table_schema || '.' || table_name || ' LIMIT 1; --Derived Column: ' || column_name FROM v_catalog.columns WHERE table_schema || '.' || table_name = 'public.fact_flat' AND column_set_using <> '';" | vsql -a &> /home/dbadmin/refresh_columns_check.txt

[dbadmin@s18384357 ~]$ cat /home/dbadmin/refresh_columns_check.txt
SELECT (( SELECT dim_1.c2 FROM public.dim_1 WHERE (dim_1.c1 = fact_flat.c1))) AS dim_1_c2 FROM public.fact_flat LIMIT 1; --Derived Column: dim_1_c2
dim_1_c2
----------
TEST1
(1 row)

SELECT (( SELECT dim_2.c2 FROM public.dim_2 WHERE (dim_2.c1 = fact_flat.c1))) AS dim_2_c2 FROM public.fact_flat LIMIT 1; --Derived Column: dim_2_c2
ERROR 4840:  Subquery used as an expression returned more than one row

SELECT (( SELECT dim_3.c2 FROM public.dim_3 WHERE (dim_3.c1 = fact_flat.c1))) AS dim_3_c2 FROM public.fact_flat LIMIT 1; --Derived Column: dim_3_c2
dim_3_c2
----------
TEST3
(1 row)

In the example, you can see that the column DIM_2_C2 is the offender here!