I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many interesting typos), and in some parts have quite bad database design.
To ease working with these, I use a large "view library" as I call it, which defines views upon views that bring a lot of clarity, eliminate repetition, and allow me to ultimately aggregate all data which my application needs in a specific screen into one or two views that the application needs to query.
This works very well and I'm happy with it.
(Yes really. The performance is usually great, and when it isn't it can usually be fixed without much hassle and is almost always revealed to be related to a bad join or sub-query somewhere that would have caused the issue regardless of whether views are being used or not. I've only ever had one issue related to using views per se, which was MS SQL Server failing to optimize queries against one of the most complex views --perhaps the most complex in fact-- which I was able to solve simply by turning the view into a procedure that materializes the results of a couple intermediate views first.)
Now in some new deployment variants of SAP Business One, I will lose the ability of creating arbitrary SQL views in the database. Bad news. I will however be allowed to run some read-only queries, via a limited subset of the SQL language...
This brought me to the question: is there a way to "flatten" an SQL view via MS SQL Server (any version), so that I automatically get a query that already has all nested views "resolved" to their definitions, recursively?
For instance, consider the following sequence of (highly simplified) view definitions:
create view myItems as select itemCode, itemName from Items create view myCustomers as select customerCode, customerName from Customers create view myOrders as select o.orderId, o.orderNumber, c.* from Orders o join myCustomers c on c.customerCode = o.customerCode create view myOrderLines as select l.lineId, l.lineNumber, o.*, i.* from OrderLines l join myOrders o on o.orderId = l.orderId join myItems i on i.itemCode = l.itemCode
I want the ability to get the "flat version" of let's say
myOrderLines, which would be this:
select l.lineId, l.lineNumber, o.orderId, o.orderNumber, c.customerCode, c.customerName, i.itemCode, i.itemName from OrderLines l join Orders o on o.orderId = l.orderId join Customers c on c.customerCode = o.customerCode join Items i on i.itemCode = l.itemCode
Is such a thing possible?