OraFAQ Forum: SQL & PL/SQL » selecting,joining,concatenating, updating...all with one function or procedure

Home » SQL & PL/SQL » SQL & PL/SQL » selecting,joining,concatenating, updating...all with one function or procedure

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

selecting,joining,concatenating, updating...all with one function or procedure [message #39228]

Thu, 27 June 2002 12:03

Toyn
Messages: 36
Registered: April 2001

Member

SQL> select * from tony;

RO1 RO2 RO3 ORDER_NUM
---------- ---------- ---------- ----------
1 2 3 100
2 4 101
3 4 5 102

SQL> select * from tony_dict;

CODE CODE_DSCR
---------- ----------
1 A
2 B
3 C
4 D
5 E

SQL> select * from test

RO_TEXT ORDER_NUM
---------- -----------
100
101
102

In the table tony I have ro1,ro2,ro3 and an identifying order_num. These 3 columns are all codes which are listed in the table tony_dict, with a corellating description. My destination table is test. I want to select ro1,ro2,ro3 for order_num 100, find each description for those codes, concatenate that description with a ',' separator, then insert that concatenated string into ro_text in the test table for that order_num. I'm not even really sure where to begin here. Any suggestions would be very helpful. Thanks.

Report message to a moderator

Re: selecting,joining,concatenating, updating...all with one function or procedure [message #39229 is a reply to message #39228]

Thu, 27 June 2002 14:22

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Here's just one way - you can get rid of the trailing comma(s) yourself.

select b1.code_descr||','|| b2.code_descr||','|| b3.code_descr, a.order_num
from tony a, tony_dict b1, tony_dict b2, tony_dict b3
where a.ro1 = b1.code(+)
and a.ro2 = b2.code(+)
and a.ro3 = b3.code(+)

A,B,C 100
B,D, 101
C,D,E 102

Report message to a moderator

Previous Topic:	Varrays
Next Topic:	Re: ora-00600[17034].

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:08:39 CDT 2024