Re: Problem with simple SQL query part 2

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/30
Message-ID: <36124221.10545904@192.86.155.100>#1/1

A copy of this was sent to "Terry Maguire" <tmaguire_at_nospam.tinet.ie> (if that email address didn't require changing) On Wed, 30 Sep 1998 14:06:43 +0100, you wrote:

>
>Hi
>
>After the previous thread of messages, I'm not any closer to the solution of
>my problem. Thanks to all of you who wrote in with answers. This time I'm
>going to explain exactly what I'm trying to do. To refresh your memories
>here is the original query:
>insert into t_sedol (sedol)
>
>select sedol,market_cap from t_master_download
>
>where rownum<=246
>
>and country='United Kingdom'
>
>and downloaddate='01-DEC-92'
>
>order by market_cap desc;
>
>
>1. Table T_MASTER_DOWNLOAD - Source Table
>2. Table T_SEDOL - Destination Table
>
>
>Thanks firstly to Mark Styles for pointing out that you cannot have an ORDER
>BY in a sub-query. Thanks also to Alan Patil pointing out my error with
>ROWNUM. But I ask you, how can I get around this problem:
>
>The data in T_MASTER_DOWNLOAD contains financial data on companies. There
>are 4 specific criteria that I must work by to retrieve the data I need:
>
>(a) The country e.g. United Kingdom
>(b) The downloaddate e.g. 01-DEC-92
>(c) The market capital of a company
>(d) The amount of companies
>
>The first 2 are easy to look after as these are in the WHERE clause. It's
>the next ones I'm having problems with. Market capital of a company is what
>we are using to determine if we analyse a company or not. So what I want to
>do in the SELECT statement is to have the highest market capital company at
>the top and then descend from that. Then what I require is to take the top
>246 companies from this.
>

I believe you will find you can use pl/sql to accomplish this. For example:

declare

    cnt number default 0;
begin

    for x in ( select sedol, market_cap

                 from t_master_download
                where country='United Kingdom'
                  and downloaddate = to_date( '01-DEC-1992', 'DD-MON-YYYY' )
                order by market_cap desc )
    loop
        cnt := cnt + 1;
        exit when ( cnt > 246 );
        insert into t_sedol ( sedol ) values ( sedol );

    end loop;
end;
/

you could wrap that in a stored procedure and parameterize the query as well so you might be able to execute something like:

begin

   my_procedure( p_country => 'United Kingdom',

                 p_downloaddate => to_date( '01-DEC-1992', 'DD-MON-YYYY' ) );

end;

with the procedure:

create or replace procedure my_procedure( p_country in varchar2, p_downloaddate in date )
as

    cnt number default 0;
begin

    for x in ( select sedol, market_cap

                 from t_master_download
                where country= p_country
                  and downloaddate = p_downloaddate
                order by market_cap desc )
    loop
        cnt := cnt + 1;
        exit when ( cnt > 246 );
        insert into t_sedol ( sedol ) values ( sedol );

    end loop;
end;
/

[snip]

>
>Such a statement cannot be used in Oracle (forgetting all the MS Access
>crap). As Mark Styles pointed out, you can't use an ORDER BY in a subquery.
>Then how do you INSERT data in the manner I wish to. How do you do this type
>of INSERT??
>
>Any help on these questions would be greatly appreciated!
>
>Regards
>
>Terry Maguire
>IIU
>IFSC House
>Custom House Quay
>Dublin 1
>Ireland
>e-Mail : tmaguire_at_nospam.tinet.ie
>Remove nospam to e-Mail
>
>
>
>
>
>
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Wed Sep 30 1998 - 00:00:00 CDT