Re: SELECT DISTINCT and Insert. Interesting question

TurkBear (john.greco_at_dot.state.mn.us) wrote:

: sriniksk_at_yahoo.com (Srini) wrote:

: >Hi,
: >
: >Here is an interesting question seeking for expert's advice.
: >
: >SELECT DISTINCT f1, f2, f3 ...
: > INTO v1, v2, v3....
: > FROM t1, t2....
: > WHERE t1.f1 = t2.f1 ....
: >ORDER BY t1.f1;
: >
: >Now I'm trying dump the result into a Temporary table [has only one
: >coulmn varchar2(255)] like:
: >
: >INSERT INTO temp_table(f1) values (v1 || v2 || v3....)
: >
: >If the 'f1' in 'temp_table' is the Primary Key, I'm getting Unique
: >Constraint error. I was expecting the SELECT DISTINCT to return
: >unique row.
: >
: >Any clue what am I doing wrong here.
: >Appreciate your valuable time.
: >
: >Srini.

: Just a thought..
: There may be embedded spaces at the end of some of the fields so the 'distinctness' is lost when concatenated ..
: Try
: Insert into temp_table(f1) values (trim(v1)||trim(v2)||trim(v3)...)

: It may resolve it...

There don't even need to be spaces for this to happen.

Imagine
row 1: v1='a' v2='ab' v1||v2 => aab
row 2: v1='ab' v2='b' v1||v2 => aab

I'm sure you can see that with more variables the issue gets worse, and simple changes such as trimming blanks will not solve the problem in general. To use save the data in a single column, you need to either remove the primary key, or you need to stick something between the values, something that does not appear in the strings. If, for example, the data does not contain a comma, then you could use something like

        v1 || ',' || v2 || ',' || v3

but to do this you need a magic character that isn't in the data.

The other solution, (my own choice in this matter) would be to insert a unique key (such as a sequence), and possibly a timestamp. The extra field(s) will likely be useful later anyway, when you want to examine the data and make sense of it. Received on Wed Nov 06 2002 - 17:10:44 CST