Re: Problem with simple SQL query part 2

Hi Terry

If you have Access why not set up an ODBC link to your Oracle database and setup external links to the required tables. Then you can use normal Access SQLs to read and write to Oracle. Alternatively try doing it in PL/SQL as someone else suggested.

 Al

Terry Maguire <tmaguire_at_nospam.tinet.ie> wrote in message 6utae3$9hr8_at_kirk.tinet.ie...
>
>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!
>
>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