Sql = want to use make an inner join statement but not with tables

Question

Hello my friends, I want to use the fact that on two tables t1 and t2 I can make an inner join with on t1.colum1>t2.colum2 to calculate the maximum drawdown of a return vector. The problem is that an inner join is only possible with two stored databases or tables and I wanted to do it selecting just a part of the tables.

Is there any other possibility, I am totally new to sql and I can't find any other option?

Thank you

edit

before manipulating my inner join to be able to calculate my maximum drawdown I have to be able to make this inner join on a selection on the tables and not the tables themselves. So I followed Mark's advice but I am still getting an error. Here is my query:

select * 
from (select * from bars where rownum <= 10 as x)as tab1
inner join (select * from bars where rownum <= 10  as y) as tab2
on tab1.x=tab2.y

The error is ora-00907 missing right parenthesis

• additional information extracted from OP's message published as answer to this post. *

Answer 1

(inner) join is not limited to whole tables.

Answer 2

You can inner join on subselects too, you just need to give the subselects an alias:

SELECT *
FROM (SELECT 1 AS X) AS T1
INNER JOIN (SELECT 1 AS Y) AS T2
ON T1.X = T2.Y

If you post your non-working query, I can give you a better answer more tailored to your exact tables.

Answer 3

Mark I have a doubt now reading your answer again...I did the first alias you do in the subquery but i didn't do the second alias, i don't understand it..it is like you are making two times an alias...I am going to try this tomorrow and will return you the result around 9 am ny time. Thank you a lot!

Answer 4

I got a definition of maximum drawdown from an investment website (thanks Google!). So I think we need to calculate the percentage drop between the highest point in a graph and its subsequent lowest point.

The following query calculates the maximum drawdown on investments in Oracle stock over the last twelve months. It joins the investments table to itself, with aliases to distinguish the versions of the table (one for the highest peak, one for the lowest trough). This may not mirror your precise business logic, but it shows the SQL techniques which Oracle offers you.

select round(((max_return-min_return)/max_return)*100, 2) as max_drawdown
from
    ( select max(t1.return_amt) as max_return
             , min(t2.return_amt) as min_return
      from investments t1
           join  investments t2
           on ( t1.stock_id = 'ORCL'
                and   t2.stock_id = t1.stock_id
                and   t2.created_date > t1.created_date )
      where t1.created_date >= add_months(sysdate, -12)
      and t2.created_date >= add_months(sysdate, -12)
    )
/

This query will return zero if the stock has not experienced a drop during the window. It also does not check for a following upturn (as I understand drawdown it is supposed to be the bottom of a trough, a point we can only establish once the stock has started to climb again).

With regard to training at home, we can download software from Oracle TechNet for that purpose. If bandwidth or disk space are an issue go for the Express Edition; it doesn't have all the features but you probably won't want them for a while yet. Oracle do provide a free IDE, SQL Developer. As its name suggests it is primarily targeted at developers but it has many of the DBA-oriented features of DB Artisan. For full-on database management Oracle offers Enterprise Manager.

edit

In the comments outis suggests

You could add a t1.return_amt > t2.return_amt in the join as a minor optimization

I think it is unlikely that return_amt would be indexed, so I think it is unlikely that such a clause would have an impact on performance. What it would do is change the behaviour for stocks which do not have a drawdown. The query I presented returns zero for stocks which have increased continuously through the time window. The additional filter would return a NULL in such a case. Which is the more desirable outcome is a matter of taste (or requirements spec).

Answer 5

Hello guys, before manipulating my inner join to be able to calculate my maximum drawdown I have to be able to make this inner join on a selection on the tables and not the tables themselves. So I followed Mark's advices but I am still getting an error, here is my query: select * from (select * from bars where rownum <= 10 as x)as tab1 inner join (select * from bars where rownum <= 10 as y) as tab2 on tab1.x=tab2.y The error is ora-00907 missing right parenthesis Thank you