Re: SQL*PLUS Syntax for Concatenation

In <1995Aug24.125201.29897_at_news.unige.ch> <ratrimo> writes:
>
>dschellenberg_at_man.net (Dave Schellenberg) wrote:
>>We are running Oracle 7 on VAX/VMS. I have two questions dealing
 with SQL*PLUS
>>(and maybe PL/SQL) that I need help with:
>>
>>Question 1:
>>
>> I have two tables, call them table1 and table2. I want something
 like this:
>>
>> UPDATE TABLE1
>> SET TABLE1.COL2 = concatenated CHARs from TABLE2.COL2
>> WHERE TABLE2.ID = TABLE1.ID;
>>
>> In other words, I just want to concatenate all of the strings from
 

>>table2.column2, separating each with a space, and then set a much
 bigger CHAR
>>in table1 to that string. I have no idea!
>>
>>Question 2:
>>
>> I want to find the second-highest date in a column in a table. It
 is too
>>easy to find the highest, using MAX( ), but I can't think of how to
 find the
>>next highest.
>>
>>Anyone here ever done either of these?
>>
>>David Schellenberg
>>Winnipeg, Manitoba
>>dschellenberg_at_man.net
>>
>
 Qusetion1
>---------------------

  try using a PL/SQL Block.

  Declare
  Cursor c1 IS SELECT * FROM TABLE1

                         FOR UPDATE OF COL2;

  TMP_VAR VARCHAR2(255) := NULL;   Begin  

        FOR REC_TABLE1 IN C1 LOOP           EXIT WHEN C1%NOTFOUND;   

            SELECT CONCATENATED COL2
                   INTO  TMP_VAR
                   FROM TABLE2
                   WHERE ID = EMP_TABLE1.ID;


            UPDATE TABLE1
            SET COL2 = TMP_VAR
            WHERE CURRENT OF C1;


        END LOOP;

  COMMIT WORK;
  End

>QUestion 2:
>---------------------
 

>select MAX(DATE_COL) from tab1
> where COL_DATE <>
> (select MAX(COL_DATE) from TAB1)
>

 Regards,

 SWAPAN JHA , USA
 JHAS_at_IX.NETCOM.COM.
>
>
  Received on Fri Aug 25 1995 - 00:00:00 CEST