You can translate the question and the replies:

Building derived views that originally have subqueries in them

I have a query that I'm trying to build the logic for as a derived view. Basically, translating my view that is on a DB to Denodo as a Derived View. Trying to figure out that if we have a subquery in the **from** or where **clause**, do we have to create another Derived view of this subquery, and then our final derived view will use this new other derived view as a source? Here are a couple examples of view logic similar to what I'm talking about: **Example A** ``` SELECT SP.TerritoryID, SP.BusinessEntityID, SP.Bonus, TerritorySummary.AverageBonus FROM (SELECT TerritoryID, AVG(Bonus) AS AverageBonus FROM Sales.SalesPerson GROUP BY TerritoryID) AS TerritorySummary INNER JOIN Sales.SalesPerson AS SP ON SP.TerritoryID = TerritorySummary.TerritoryID ``` **Example B** ``` SELECT a.col1, a.col2, SUM (col3) "COL3", SUM (col4) "COL4", SUM (col4) "COL5" FROM schema_one.table_a a, (SELECT DISTINCT col1, col2 FROM schema_two.table_b) b WHERE a.col1 = b.col1 AND a.col2 = b.col2 GROUP BY a.col1, a.col2; ``` I am assuming that in Example A above, that I would need to build a derived view based off of the code below and then use this as a source to build my target/final view? (SELECT TerritoryID, AVG(Bonus) AS AverageBonus FROM Sales.SalesPerson GROUP BY TerritoryID) AS TerritorySummary INNER JOIN Sales.SalesPerson AS SP ON SP.TerritoryID = TerritorySummary.TerritoryID
user
15-02-2021 11:51:01 -0500

2 Answers

Hi, In the Denodo Platform, a derived view can be created by combining other existing views either graphically or using the VQL Commands. In this situation, since a subquery is involved, as stated by you, I would create a derived view for subquery and perform the required operation. You can refer to the [Creating Derived Views](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/creating_derived_views/creating_derived_views) section under Virtual DataPort VQL Guide for more information. Alternatively, it is possible to create a derived view from VQL query. For example: ``` create view view_name as SELECT a.col1, a.col2, SUM (col3) "COL3", SUM (col4) "COL4", SUM (col4) "COL5" FROM schema_one.table_a a, (SELECT DISTINCT col1, col2 FROM schema_two.table_b) b WHERE a.col1 = b.col1 AND a.col2 = b.col2 GROUP BY a.col1, a.col2; ``` Please note that, with this approach the view would not be editable graphically as complex operations have been carried out. Hope this helps !
Denodo Team
16-02-2021 04:26:07 -0500
Thanks! This helps a ton, I had no idea we could create views straight from the VQL Shell. Exactly what I needed.
user
16-02-2021 10:07:04 -0500
You must sign in to add an answer. If you do not have an account, you can register here