Re: sql query help please

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Thu, 28 Apr 2011 13:57:27 -0700
Message-ID: <BANLkTikYbQBxzZjtr=Vb-TeVL6yB0+bdNA_at_mail.gmail.com>

I assumed that since the op is loading from an external file, he means that it is not exposed as an external table. I could be wrong on this assumption, but if I'm right, then I don't see any simple way around using two tables.

Regards,
Mike

On Thu, Apr 28, 2011 at 12:59 PM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

> No, no need for an intermediate table. The multi-table insert (inserting
> multiple times into the same table, that is) probably does the job.
> Alternatively
>
>
> insert into tableB
> select div,
> cust,
> case n
> when 1 then jan
> when 2 then feb
> when 3 then mar
> end
> from external table
> cross join (select 1 n from dual
> union all
> select 2 from dual
> union all
> select 3 from dual)
>
> HTH
>
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
> Konagora <http://www.konagora.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
> On 04/28/2011 09:07 PM, Michael Moore wrote:
>
> first load it into a table (a) just as it is currently formated. Then
> create a table (b) with the new format and load from a to b as follows.
>
> insert into tableB
> (select div, cust, jan from tableA
> union all
> select div, cust, feb from tableA
> union all
> select div, cust, mar from tableA) ;
>
> Mike
>
> On Thu, Apr 28, 2011 at 11:45 AM, Kenneth Naim <kennethnaim_at_gmail.com>wrote:
>
>> Look at multi table inserts. It’ll work perfectly for what you need.
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Eugene Pipko
>> *Sent:* Thursday, April 28, 2011 2:42 PM
>> *To:* oracle-l_at_freelists.org
>> *Subject:* sql query help please
>>
>>
>>
>> Hi all,
>>
>> Oracle 9i on Win2K3.
>>
>> I am loading data from external file and need to convert it into different
>> format.
>>
>>
>>
>> Current format:
>>
>> --------------------
>>
>> DIV CUST JAN FEB MAR
>>
>>
>>
>> New format:
>>
>> --------------------
>>
>> DIV CUST JAN
>>
>> DIV CUST FEB
>>
>> DIV CUST MAR
>>
>>
>>
>> Could you please point me to a reference from where I can learn how to do
>> it?
>>
>>
>>
>> Thanks,
>>
>> Eugene
>>
>>
>>
>>
>>
>>
>> ------------------------------
>>
>>
>> Checked by AVG - www.avg.com
>> Version: 10.0.1325 / Virus Database: 1500/3602 - Release Date: 04/28/11
>> ------------------------------
>>
>>
>> Checked by AVG - www.avg.com
>> Version: 10.0.1325 / Virus Database: 1500/3602 - Release Date: 04/28/11
>>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 28 2011 - 15:57:27 CDT