December 28, 2011 at 2:41 am
Hello all,
i am building views in sql |Management studio 2008 and joining them
one to each other, in a result creating a new view based on other views.
at last i would like to generate a native select staetament that combines all the view queries
that "beyond" the views ( the "raw" select-from-where that buikds the actual view's|)
hope that my explenation is well delivered
thank you very much...
December 28, 2011 at 3:39 am
What is your question?. Can you use select statement in the underlying views? The answer is yes.
I can have view 1 and view 2. They combine to form view 3.
Then You use select * from view3.
If this is not your requirement. Can you elaborate the requirement?
--- Babu
December 28, 2011 at 3:57 am
hi and thanks for your reply.
let me try to explain it in this way :
say that view1 and view2 are both views that using standard tables from our DataBase. each one of it uses 3 tables
creating a view 3 by joining view 1 and 2 will generate the final view we need to deliver nad uses a total of 6 standrat tables...
i would like to generate the code that refering to our standard tables AS IT was applied by the views
and not the one the referes to the Views itself( "select * from view3") ,
the goal is to have a standrat raw sql code that will run in each and every system that has the same database as the one the views where built in BUT do not have the actual views.
thanks again
Nadav
December 28, 2011 at 4:10 am
This is an extremely bad idea - for very large values of bad. :w00t:
Please review this article: http://www.sqlmag.com/article/sql-server/what-are-your-nested-views-doing-
Then review this forum discussion: http://www.sqlservercentral.com/Forums/Topic1224077-391-1.aspx
You are making a serious mistake.
December 28, 2011 at 4:54 am
still didnt made any mistake... dont know how 😀
if i undestand correctlly, nesting views is not a good idea performance wise
if so , i still want to experiance with that and still dont know how to check the code on nested views...
as said, the goal is to make a kind of "building blocks" from well built queries that combines together and can be run from any implemantation of the same database
thanks
Nadav
December 28, 2011 at 5:03 am
You seem to be trying to embody the good coding practices of encapsulation and reuse in your SQL code.
I won't say it can't be done, but that is very hard to do with SQL. The language does not have the proper structures to implement these practices. Also, the Query Optimizer can't effectively optimize T-SQL code written this way.
My advice is to get a database administrator at your company to help you.
December 28, 2011 at 5:10 am
thank for yout advice,
my question is still hanging,
Can i generate the native code from the nested views - and can it be done using SQL MANAGEMENT studio 2008 ( as script genrator or dumping a report)
Thanks again
December 28, 2011 at 6:24 am
Sorry if I'm misunderstanding you, but I think you just want to see if you can generate the SQL used to create the view? In which case you can in SSMS, you just right click on the view and look for the "Script View as" and select Create or Alter. Within these statements you will have your source SQL. Now if you are looking at writing some SQL that could be used across database instances, and you want to have better code organization look on MSDN for Common Table Expressions (CTEs). CTEs would allow you to organize your SQL a bit if you have complicated expressions that you are joining together.
Hope this helps,
Jim
December 28, 2011 at 6:35 am
You can create a view like this...
CREATE VIEW VW_XYZ
AS
SELECT COL1, COL2
FROM (VIEW1_DEF) V1 -- SELECT query for View1
INNER JOIN (VIEW2_DEF) V2 -- SELECT query for View2
ON V1.SOMW_ID = V2.SOME_ID
It will give you a base definition (skeleton) of view. Then you need it to rewrite for better format & removing duplicate table joins.
December 28, 2011 at 6:35 am
hi jim,
i get the sql staememnt regourding the views itself - check my example :
this is VIEW3 sql scripted ( with script - alter to )
SELECT dbo.InvoicesSalesPerCustomer.category, dbo.InvoicesSalesPerCustomer.value AS value1, InvoicesSalesPerCustomer_1.value AS value2
FROM dbo.InvoicesSalesPerCustomer FULL OUTER JOIN
dbo.InvoicesSalesPerCustomer AS InvoicesSalesPerCustomer_1 ON dbo.InvoicesSalesPerCustomer.category = InvoicesSalesPerCustomer_1.category
the bolded statements above are VIEWS nested in the VIEW3 view.
i need the wraw t-sql native code of the query above - say that dbo.InvoicesSalesPerCustomer uses the tables : invoices,customers and other - i need that in the final query these tables will also be stated
December 28, 2011 at 6:43 am
As i Mentioned Above
the goal is to create a so called querit generator.
say there is a standard database for an ERP system (of any sort) that installed on varios customers locations.
I want to be able to uses a VIEW like bulding blocks to create all sort of DATA manipulation reports.
If i use this reports without a detailed SQL code derived from the nested views, i will need to install and create this views in every customer i reach - and i dont want to do that....
instead i would like to have a code that can be use in any installed location no matter the views in it
December 28, 2011 at 6:47 am
You would have to do the same thing for the views you are selecting against, since SSMS will not convert a query like yours automatically into a single SQL statement that doesn't utilize any views. I would recommend looking at the article I mentioned above. As an aside, I noticed in your example that you are basically joining the same view back to itself. A CTE would help this be more readable I think.
Jim
December 28, 2011 at 6:55 am
@nadav: Let me say it again (what David has already said)
David Moutray (12/28/2011)
This is an extremely bad idea - for very large values of bad. :w00t:
If ERP application is pulling data from million rows tables, your views (without any filters) will kill the system.
December 28, 2011 at 6:55 am
Hi jim...
i am joining the same view only for the favour of my example-
If i understand you correctly, in any way i will have to create the views at the targeted database prior to my query execution ...
December 28, 2011 at 7:17 am
nadav (12/28/2011)
Hi jim...i am joining the same view only for the favour of my example-
If i understand you correctly, in any way i will have to create the views at the targeted database prior to my query execution ...
No,
You could implement all your queries as normal SQL (with or without using the CTE syntax). Using nested views is, as Dev and David have mentioned, not a good thing to do. Here[/url] is one example and here[/url] is another.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply