Re: Substitute a variable in PL/PGSQL.

Roberts, Jon wrote:

>>What I am trying to do is find the difference between two tables, one
>>that stores the
>>information in a single column, and the other which stores the same
>>
>>
>data
>
>
>>in multiple
>>columns.
>>
>>E.g.
>>CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
>>
>>
>text,
>
>
>>col6 text, col7 text, col8 text, col9 text, col10 text);
>>CREATE TABLE test2(col_data text NOT NULL,  some_data  text NOT NULL,
>>other_data text,
>>                                      CONSTRAINT test2_index PRIMARY
>>
>>
>KEY(
>
>
>>                                           col_data,
>>                                           some_data ));
>>
>>Trying to find data set in test2.col_data that is not in test.col1 to
>>test.col10.
>>
>>
>>
>
>FINALLY you get to the requirements.  Next time, just ask a question
>like the above.  You were asking how to solve a technical problem that
>didn't relate to the actual business need.
>
>Here are three ways to skin this cat.
>
>--version 1
>select col_data from test2
>except
>select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') ||
>
>       coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') ||
>
>       coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') ||
>
>       coalesce(col10, '')
>  from test
>
>--version 2
>select col_data
>  from test2 t2
> where not exists (select null
>                     from test t
>                    where t2.col_data = coalesce(t.col1, '') ||
>                                        coalesce(t.col2, '') ||
>                                        coalesce(t.col3, '') ||
>                                        coalesce(t.col4, '') ||
>                                        coalesce(t.col5, '') ||
>                                        coalesce(t.col6, '') ||
>                                        coalesce(t.col7, '') ||
>                                        coalesce(t.col8, '') ||
>                                        coalesce(t.col9, '') ||
>                                        coalesce(t.col10, ''))
>--version 3
>select t2.col_data
>  from test2 t2
>  left join (select coalesce(col1, '') || coalesce(col2, '') ||
>                    coalesce(col3, '') || coalesce(col4, '') ||
>                    coalesce(col5, '') || coalesce(col6, '') ||
>                    coalesce(col7, '') || coalesce(col8, '') ||
>                    coalesce(col9, '') || coalesce(col10, '') as
>col_data
>               from test) t
>    on t2.col_data = t.col_data
> where t.col_data is null
>
>
>Jon
>
>
Thanks Jon for the hints.
Steve