OraFAQ Forum: SQL & PL/SQL » Conditional Insert / Join Question

Home » SQL & PL/SQL » SQL & PL/SQL » Conditional Insert / Join Question

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Conditional Insert / Join Question [message #1955]

Wed, 12 June 2002 15:23

Rob
Messages: 70
Registered: January 2000

Member

I have 2 tables that look like this:

USER_DATA
TYPE, VALUE, FK
201 SEA 16
201 LA 10
204 SEA_WA 16
204 LA_CA 10

NAMED_GROUP
PK, NAME
16 SEATTLE
10 LOS ANGELES

I need to merge data from mulitple rows (based on TYPE) on the first table and insert it all on 1 row in the new table based on the second table. I hope to end up with something that looks like this:

16 SEATTLE SEA SEA_WA
10 LOS ANGELES LA LA_CA

Thank you,
Rob

Report message to a moderator

Re: Conditional Insert / Join Question [message #1957 is a reply to message #1955]

Wed, 12 June 2002 16:24

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Is this helping you?

SQL> select * from user_data;

      TYPE VALUE              FK
---------- ---------- ----------
       201 SEA                16
       201 LA                 10
       204 SEA_WA             16
       204 LA_CA              10

SQL> ed
Wrote file afiedt.buf

  1* select * from named_group
SQL> /

        PK NAME
---------- ----------
        16 SEATTLE
        10 LOS ANGELE
SQL> get f2
  1  create or replace function merge (n number)
  2  return varchar2
  3  as
  4  retval varchar2(300);
  5  out varchar2(30);
  6  cursor c1 is select a.name one ,b.value two from
  7  named_group a, user_data b
  8  where a.pk=b.fk
  9  and a.pk=n;
 10  begin
 11  for crec in c1 loop
 12  if c1%rowcount=1 then
 13  out:=crec.one;
 14  retval:= crec.two;
 15  else
 16  retval:= crec.two ||','||RETVAL;
 17  end if;
 18  end loop;
 19  retval:= out||','||retval;
 20  return retval;
 21* end;
SQL> /

Function created.

SQL> column value format a30

SQL> select pk,merge(pk) value from named_group group by pk;

        PK VALUE
---------- ------------------------------
        10 LOS ANGELE,LA_CA,LA
        16 SEATTLE,SEA_WA,SEA

Report message to a moderator

Re: Conditional Insert / Join Question-continuation [message #1958 is a reply to message #1955]

Wed, 12 June 2002 16:52

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> ed
Wrote file afiedt.buf

  1  create table new
  2  (pk number,
  3*  value varchar2(30)
SQL> /

Table created.
SQL> ed
Wrote file afiedt.buf

  1* insert into new (select pk,merge(pk) from named_group group by pk)
SQL> /

2 rows created.

SQL> select * from new;

        PK VALUE
---------- ------------------------------
        10 LOS ANGELE,LA_CA,LA
        16 SEATTLE,SEA_WA,SEA

Report message to a moderator

Re: Conditional Insert / Join Question-continuation [message #1965 is a reply to message #1958]

Thu, 13 June 2002 08:12

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Rob, you mentioned in your other response that there is a maximum of 4 possible matches for each named_group. Assuming that each type links to a separate column in the target table, you can expand this example from the 2 matches to 4 matches:

sql>select ng.pk, ng.name, t204.value city_st, t201.value abbv
  2     from named_group ng,
  3          (select fk, value
  4             from user_data
  5            where type = 201) t201,
  6          (select fk, value
  7             from user_data
  8            where type = 204) t204
  9    where t201.fk (+)= ng.pk
 10      and t204.fk (+)= ng.pk;
 
       PK NAME                           CITY_ST    ABBV
--------- ------------------------------ ---------- ----------
       10 LOS ANGELES                    LA_CA      LA
       16 SEATTLE                        SEA_WA     SEA

Add an 'insert into target_table' before the select to get the results into your target table.

Report message to a moderator

Previous Topic:	Max lines in Package
Next Topic:	using SUBSTR function in an outer join?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 02:14:43 CDT 2024