One View Referring the other View

Transact-SQL
1

I have a view(vw_NSEMaxIVWithOptionType) referring the another view(vw_NSEMaxImpliedVolatility). How can I achieve the results in the same view.

vw_NSEMaxImpliedVolatility
I get the Maximum Implied Volatility for a given Option Type.

vw_NSEMaxIVWithOptionType
I get the Maximum Implied Volatility and Option Type.

2

I haven't understood your question

Do you mean that you want vw_NSEMaxImpliedVolatility to operate for ALL OPTION_TYP, not just OPTION_TYP <> 'XX' in vw_NSEMaxIVWithOptionType?

Those DISTINCT in your VIEW will REALLY kill performance. It would be much better to select such that you don't get any duplicates and don't need DISTINCT.

3

Thank you for your reply.

Columns (a.OPTION_TYP and MaxOpenInterest) should be in the select list.

I get the error

Column 'a.MaxOpenInterest' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

4

The general rule is that when you use aggregate functions (such as MAX, SUM etc.), any column in the select list that is not inside an aggregate function must be listed in the group by clause. The error you are getting is indicating that you have listed a.MaxOpenInterest in the select list, and that it is neither within an aggregate function, nor is it listed in the group by clause.

So you have two choices:
a) wrap it in an aggregate function. Eg.

....
MAX(c.ImpliedVolatility) AS MaxIV,
AVG(c.ImpliedVolatility) AS MeanIV,
MAX(a.MaxOpenInterest) as MaxOpenInterest
....

b) include the column in the group by clause

    ....
    MAX(c.ImpliedVolatility) AS MaxIV,
    AVG(c.ImpliedVolatility) AS MeanIV,
    a.MaxOpenInterest
    ....
   GROUP BY a.TIMESTAMP, a.SYMBOL, a.EXPIRY_DT, a.MaxOpenInterest