Re: Problem with simple SQL query part 2

On Wed, 30 Sep 1998 14:06:43 +0100, "Terry Maguire" <tmaguire_at_nospam.tinet.ie> 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.
>
>Now call me a traitor if you wish, but I had a look at how MS Access
>generates this type of SELECT query. Here we go
>
>SELECT TOP 246 TERRY_T_MASTER_DOWNLOAD.SEDOL
>FROM TERRY_T_MASTER_DOWNLOAD
>WHERE (((TERRY_T_MASTER_DOWNLOAD.DOWNLOADDATE)>#11/1/92# And
>(TERRY_T_MASTER_DOWNLOAD.DOWNLOADDATE)<#1/1/93#) AND
>((TERRY_T_MASTER_DOWNLOAD.COUNTRY)="United Kingdom"))
>ORDER BY TERRY_T_MASTER_DOWNLOAD.MARKET_CAP DESC;
>
>It uses TOP 246, to grab the 246 highest market capital companies. Is there
>anything similar in Oracle PL/SQL??
>
>
>The next step is to INSERT this data in the destination table T_SEDOL. I
>presumed it would be just a matter of putting the insert line, but that is
>obviously not the case. Again looking at MS Access they do this:
>
>INSERT INTO TERRY_T_SEDOL ( SEDOL )
>SELECT TOP 246 TERRY_T_MASTER_DOWNLOAD.SEDOL
>FROM TERRY_T_MASTER_DOWNLOAD
>WHERE (((TERRY_T_MASTER_DOWNLOAD.DOWNLOADDATE)>#11/1/92# And
>(TERRY_T_MASTER_DOWNLOAD.DOWNLOADDATE)<#1/1/93#) AND
>((TERRY_T_MASTER_DOWNLOAD.COUNTRY)="United Kingdom"))
>ORDER BY TERRY_T_MASTER_DOWNLOAD.MARKET_CAP DESC;
>
>
>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!
>

Terry,

OK, here you go.

What you first need to do is get a query that will return the top 246 records. You can do this by creating an index on the column and using the INDEX_DESC hint.

create index market_cap_idx on T_MASTER_DOWNLOAD ( market_cap );

Now you can use a query hint to select the top 246 records.

select /*+ INDEX_DESC( T_MASTER_DOWNLOAD market_cap ) */ SEDOL from t_master_download
where rownum <= 246
and country = 'United Kingdom'
and downloaddate = '01-DEC-92'
and market_cap > 0;

This should return the top 246 records ordered by market_cap desc.

• Notice I added the constraint "market_cap = 0". This is to insure that Oracle will use the index market_cap_idx. **

Now it is just a simple insert/select statement.

insert into T_SEDOL ( sedol )
select /*+ INDEX_DESC( T_MASTER_DOWNLOAD market_cap ) */ SEDOL from t_master_download
where rownum <= 246
and country = 'United Kingdom'
and downloaddate = '01-DEC-92'
and market_cap > 0;

If you want more information on the INDEX_DESC hint, check out the Oracle Server Tuning manual, chapter 8 I think.

I hope this helps.

chris.

>Regards
>
>Terry Maguire
>IIU
>IFSC House
>Custom House Quay
>Dublin 1
>Ireland
>e-Mail : tmaguire_at_nospam.tinet.ie
>Remove nospam to e-Mail
>
>
>
>
>
>
>
>
Received on Wed Sep 30 1998 - 00:00:00 CDT