"Dynamic" Views? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

"Dynamic" Views?

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-07 : 14:33:39

I doubt if what I am going to ask is possible, but just wondering if it is.

I have multiple databases for the same "DLM" system out in the field. They all contain the same tables, but some tables contain certain columns and others do not, based on program updates which change the table schema. I would like to write the Views based on the "db version" or existence of a column or not. So I wrote a Get_DLM_Version function that would return the database version (e.g. 2.0, 2.1, 2.2, etc.). I was hoping I could use the Get_DLM_Version Function in a view with an IF or CASE statement but I cannot get the syntax to work, probably because the following is not possible...

CREATE VIEW TEST AS
  IF dbo.Get_DLM_Version() >= 2.1 
  BEGIN
    Select OldCol1,
           MAX(OldCol2) as Group1,
           SUM(NewCol) AS Total
    FROM MyTable Group By OldCol1 Order by OldCol1
  ELSE
    Select OldCol1,
           MAX(OldCol2) as Group1,
           SUM(0) AS Total
    FROM MyTable Group By OldCol1 Order by OldCol1
  END

But it does not like IF in a View

I also tried this by using CASE Statements...

CREATE VIEW Test AS
    Select OldCol1,
           MAX(OldCol2) as Group1,
           SUM(CASE WHEN dbo.Get_DLM_Version() >= 2.1 THEN NewCol1 ELSE 0 END) AS Total
    FROM MyTable Group By OldCol1 Order by OldCol1

But it balks at the non-existence of NewCol1.

Am I trying to do something that cannot be done?

TIA,
Ken

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 02:35:21

Do you have the column NewCol1 in Mytable?

Madhivanan

Failing to plan is Planning to fail

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-08 : 08:17:16

No I do not, and that's the whole point.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 08:40:16

quote:
Originally posted by Ken Blum

No I do not, and that's the whole point.

Does it come from another table?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-06-08 : 10:11:49

We rename tables with each version.

So version 1 table would be called MyCustomers

Version 2 (when we realised we needed a backwards compatible solution

) would be MyCustomers_V2, which was the actual data - original table renamed, plus some columns added / changed.

We then create "MyCustomers" as a version-1 backwards compatible VIEW. That is basically a SELECT list of columns that match the old names for the version 1 MyCustomers


CREATE VIEW MyCustomers
AS
SELECT Col1 AS Col1,
       NewName2 AS OldName2,
       NewCol3 AS NewCol3
FROM MyCustomers_V2

BUT we usually also include any new columns that have been added (e.g. "NewCol3" above) (reason being: if we modify an old SProc or somesuch that used the V1 table-name and want to add a V2 column we either have to change all the column names / table names etc. in the Sproc to make it V2-compatible, OR just add in the "forwards compatibility" column name from the MyCustomers V1 View

Dunno if that approach would help you at all? (and even if it does it might be a bit late now)

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-08 : 10:31:02

Thanks Kristen, but that's not what I am looking for.

Madhivanan the column does not exist in the database at all. I have multiple databases on different servers. Some have the column and some don't. I want to have the same script to create views for all databases on all servers, regardless of whether the column exists or not. In my sample I want the "Total" column to be SUM(zero) if the "MyTable.NewCol1" does not exist, otherwise I want it to be the SUM(MyTable.NewCol1).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 10:46:03

This is what you can try


CREATE VIEW Test AS
    Select OldCol1,
           MAX(OldCol2) as Group1,
           SUM(CASE WHEN dbo.Get_DLM_Version() >= 2.1 and col_length('MyTable','NewCol1') is not null THEN NewCol1 ELSE 0 END) AS Total
    FROM MyTable Group By OldCol1 Order by OldCol1

Madhivanan

Failing to plan is Planning to fail

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-08 : 11:51:30

Nope...

Msg 207, Level 16, State 1, Procedure Test, Line 4
Invalid column name 'NewCol1'.

This is MyTable...

CREATE TABLE [dbo].[MyTable](
[Oldcol1] [int] NULL,
[oldcol2] [int] NULL
) ON [PRIMARY]

Kristen
Test

22859 Posts

Posted - 2010-06-08 : 12:04:44

OK, tangential thought ...

Can you not create MyTable_VIEW in all database versions that have the same column names (in effect a superset of all the column names that could be required)?

and then you use that, instead of MyTable, in your "dynamic view"?

Kristen
Test

22859 Posts

Posted - 2010-06-08 : 12:05:46

P.S. Ins't there some sort of "late binding" that can be induced in SQL 2005 (compared to SQL2000 I mean)?

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-08 : 12:33:46

Kristen:

Nice afterthought and I will keep that in mind. However that still means I would have to maintain separate scripts for all "Table_View" DB versions, and that's what I am trying to avoid.

Not too sure what you mean by "late binding". These DB's are on SQL2005. I am testing in SQL2008.

Ken

Kristen
Test

22859 Posts

Posted - 2010-06-08 : 13:50:45

I think in SQL 2000 columns had to be "visible" when Sproc / View was created, but in SQL2005+ they can not exist at that time, and then their existence is checked at runtime.

I wonder if this can leveraged to get Late Binding to not look at all for columns that are not required? Conditional nested derived query, for example?

Trouble is, for optimisation purposes, even unreachable (i.e. logically speaking) parts of the code are still included in the Query Plan - in case different parameters etc. might make them reachable.

No idea if this will work, just a thought:


CREATE VIEW Test AS
    Select OldCol1,
           MAX(OldCol2) as Group1,
           SUM(NewCol1) AS Total
    FROM MyTable 
    WHERE dbo.Get_DLM_Version() >= 2.1
    Group By OldCol1 
--  Order by OldCol1 -- ** ORDER BY not valid in VIEW, eh?!
UNION ALL
    Select OldCol1,
           MAX(OldCol2) as Group1,
           SUM(0) AS Total
    WHERE dbo.Get_DLM_Version() < 2.1
    Group By OldCol1 
--  Order by OldCol1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 02:54:51

Order by is allowed in a view as long as you use TOP Operator
I think it is better to apply the logic via store procedure where you can have IF statement with different select statements

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-06-09 : 02:59:57

"Order by is allowed in a view as long as you use TOP Operator"

Indeed - I should have said "not valid in this VIEW" ...

VIEWs can't be relied on to return rows in order either - e.g. SELECT TOP 100 PERCENT ... ORDER BY doesn't give guaranteed ordering - might do most-of-the-time though, which kinda makes it worse!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 04:02:18

<<
Indeed - I should have said "not valid in this VIEW" ...
>>

Yes.
I agree that it is not guaranted that Ordered view will give data in the specific order

Madhivanan

Failing to plan is Planning to fail

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-09 : 08:29:22

Nope. Still get...
Msg 207, Level 16, State 1, Procedure Test, Line 4
Invalid column name 'NewCol1'.

Guess this is not possible with a view. Oh well. Thanks for trying!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 08:37:05

Why dont you try stored procedure?

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-09 : 08:51:01

I'd suggest a table valued function even though it adds a bit of overhead. Or you can create the view using dynamic sql. Somewhat lame but it should work just fine:

DECLARE @SQL nvarchar(2000)
IF dbo.Get_DLM_Version() >= 2.1 
  SET @SQL = '
	CREATE VIEW TEST AS
	  BEGIN
		Select OldCol1,
			   MAX(OldCol2) as Group1,
			   SUM(NewCol) AS Total
		FROM MyTable Group By OldCol1 Order by OldCol1
	  END'
ELSE 		  
  SET @SQL = '
	CREATE VIEW TEST AS
	  BEGIN
		Select OldCol1,
			   MAX(OldCol2) as Group1,
			   SUM(0) AS Total
		FROM MyTable Group By OldCol1 Order by OldCol1
	  END'
EXEC sp_executesql @SQL

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-09 : 08:59:00

A SPROC would not work well with AD-Hoc queries. I know we could pass parameters as filters but that's limiting what the user can select.

Thanks Lumbago but the CREATE View is not the overall issue. I already have a "Create Views" script that will create the views based on Get_DLM_Version. What I really wanted was for the View itself to contain the proper script based on the existence of a column or not, but it appears that is not possible.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-09 : 09:21:16

A view (or any other object for that matter) is bound to the underlying table schema so any aggregations or selects or whatever you execute must be on existing physical entities in the database. As far as I know the only "exception" is when creating dynamic sql since the query optimizer never actually sees the non-existing columns.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-06-09 : 09:44:20

Yup, that's the conclusion! Thanks.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources