# How to extend views with SQL blocks when using dynamic schemas

**URL:** <https://community.omni.co/t/how-to-extend-views-with-sql-blocks-when-using-dynamic-schemas/113>\
**Category:** Modeling\
**Tags:** dynamic-schemas\
**Created:** [June 20, 2024, 9:55am UTC](https://community.omni.co/t/how-to-extend-views-with-sql-blocks-when-using-dynamic-schemas/113 "2024-06-20T09:55:44Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![conor](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/conor/32/45_2.png) [@conor](https://community.omni.co/u/conor)\
**Post date:** [June 20, 2024, 9:55am UTC](https://community.omni.co/t/how-to-extend-views-with-sql-blocks-when-using-dynamic-schemas/113/1 "2024-06-20T09:55:44Z")

</div>

In my previous post called [Working with dynamic schemas](https://community.omni.co/t/working-with-dynamic-schemas/99), we discussed how to create dynamic schemas in Omni to work with your database when you have a single-tenant database architecture.

There might be some views in the database that you want to create further derived views on top of using the [sql](https://docs.omni.co/docs/modeling/views#sql) block to make these logical extensions. However, when you write this SQL, you might be unsure what view to reference when you want this object to work with your dynamic schemas so that the view changes based on the user attribute.

The right way to define the `sql:` is by using a view reference, the Omni templating used to refer to tables or fields. For example, using the same example from the [Working with dynamic schemas](https://community.omni.co/t/working-with-dynamic-schemas/99) post, we have a dynamic schema called `store_ecom` and a table called `orders`. The view reference will be `${store_ecom__orders}`.

Then if we define a new view and want to write the logic as a sql block, we could write the following:

```sql
sql: |-
WITH returned_item_count AS (
  SELECT 
    orders.id as order_id, 
    order_items.order_item_id, 
    SUM(order_items.quantity) OVER (PARTITION BY orders.id) as number_items_in_order
  FROM ${store_ecom__orders} as orders
  LEFT JOIN ${store_ecom__order_items} as order_items 
    ON orders.id = order_items.order_id
  WHERE orders.status = 'returned'
  GROUP BY 1, 2
)

SELECT *
FROM returned_item_count

```

This approach ensures your views are dynamically aligned with user-specific schemas, providing seamless and context-aware data access.