Re: INSERT INTO... problems (Ammending text)

roy_at_enertial.net (Roy B.) wrote in message news:<a6811d09.0303041001.e4eab36_at_posting.google.com>...
> I've submitted this issue to my support staff but have found this NG
> to be quite helpful and the participants quite knowledgeable. I'm an
> Implementation Specialist for a database application product for
> Government use with OK sql skills. Pasted below is the message I've
> sent to my support staff with which I would appreciate any
> information.
>
> Thanks in advance!
>
> {paste}
> I had a question regarding modifying existing rows in the extended
> text for dockets. Let's look at DKTCD.DKT_CD = 'DECRERC'
>
> SQL> select dkt_cd, dscr from dktcd where dkt_cd = 'DECRERC';
>
> DKT_CD DSCR
> ---------- ------------------------------------------------------
> DECRERC DECREE
>
> This docket code has associated extended text:
>
> SQL> select ext_txt from dktext where dkt_cd = 'DECRERC';
>
> EXT_TXT
> ---------------------------------------------------------
> NOW,
>
>
> S/J:
>
> 1 row selected.
>
> In this extended text, we have 'NOW,' followed by three carriage
> returns. When I put this query in SQL+, it actually returns as:
>
> SQL> select ext_txt from dktext where dkt_cd = 'DECRERC';
>
> EXT_TXT
> ---------------------------------------------------------
> NOW,###S/J:
>
> 1 row selected.
>
> The three bar's (#) each equal a carriage return.
>
> Problem:
> I need to have DKTCD.DSCR text inserted as the first line in
> DKTEXT.EXT_TXT for a given DKT_CD. If this was done for the example
> above (DECRERC) example, the following would be returned as extended
> text:
>
> SQL> select ext_txt from dktext where dkt_cd = 'DECRERC';
>
> EXT_TXT
> ---------------------------------------------------------
> DECREE#NOW,###S/J:
>
> 1 row selected.
>
> This would basically take this description (DKTCD.DSCR = 'DECREE'),
> append it with a carriage return, and insert it at the beginning of
> DKTEXT.EXT_TXT.
>
> {/paste}
>
> Thanks again!

A quick reading of Oracle docs on "How to concatenate strings using ||", "ASCII code conversion of CR i.e. CHR(13)" and "Straight table join" will get you what you are looking for. e.g.

Select t1.Line1 || CHR(13) || t2.Line2 || CHR(13) || t2.Line3 from table1 t1, table2 t2
where t1.value = t2.value
and t1.value = "FOO";

Regards
/Rauf Sarwar Received on Thu Mar 06 2003 - 04:02:12 CET