Re: SELECT DISTINCT and Insert. Interesting question

Another approach may be to revise your select distinct statement to: SELECT DISTINCT f1, f2, f3 ...f1||f2||f3||..   INTO v1, v2, v3....vConcat
( or just f||f2||f3||... without the individual fields themselves - into vConcat)   FROM t1, t2....
 WHERE t1.f1 = t2.f1 ....
ORDER BY t1.f1;

Then you can use your
INSERT INTO temp_table(f1) values (vConcat.)

yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:

>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.

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Thu Nov 07 2002 - 08:43:09 CST