January 5, 2011 at 8:28 am
Hi,
I wanted to assign the column names for a particular view of which the column names are in a different table. I have a sample code which can be used.
I tried using dynamic sql for the creation of the views but realised that the view would get created for a particular store ID.
I want to address a situation wherein the views would get created independent of store ID.
Is there any work around for the following situation, or a generic view which would be created independent of the store ID.
I hope I am clear with my question,
P.S. I have used temp table just for illustration and to depict the effect; I am not trying to create any views on temp table.
CREATE TABLE #Function(
DepartmentID [int] NOT NULL,
DisplayName VARCHAR(50) NOT NULL,
FieldName varchar (250) NOT NULL,
StoreID INT,
Type CHAR(1) )
INSERT INTO #Function (DepartmentID,DisplayName,FieldName,StoreID,Type)
VALUES (1,'A1','Engineering',2221,'A')
INSERT INTO #Function (DepartmentID,DisplayName,FieldName,StoreID,Type)
VALUES (2,'A2','Administration',2221,'B')
INSERT INTO #Function (DepartmentID,DisplayName,FieldName,StoreID,Type)
VALUES (3,'A3','Sales',2221,'A')
INSERT INTO #Function (DepartmentID,DisplayName,FieldName,StoreID,Type)
VALUES (4,'A4','Marketing',2222,'B')
INSERT INTO #Function (DepartmentID,DisplayName,FieldName,StoreID,Type)
VALUES (5,'A5','Finance',2221,'A')
INSERT INTO #Function (DepartmentID,DisplayName,FieldName,StoreID,Type)
VALUES (1,'A1','EngineMachine',2223,'A')
--DROP TABLE #Function
--DROP TABLE #Department
CREATE TABLE #Department( StoreID INT , Type CHAR(1),
A1 varchar (250) ,
A2 varchar (250) ,
A3 varchar (250) ,
A4 varchar (250) ,
A5 varchar (250) )
INSERT INTO #Department (StoreID,Type,A1,A2,A3,A4,A5)
SELECT 2221,'A','Materials1','Paper1','record1','qtr1','Fin1' UNION ALL
SELECT 2221,'A','Materials2','Paper2','record2','qtr2','Fin2' UNION ALL
SELECT 2221,'A','Materials3','Paper3','record3','qtr3','Fin3' UNION ALL
SELECT 2221,'A','Materials4','Paper4','record4','qtr4','Fin4' UNION ALL
SELECT 2223,'A','Materials5','Paper5','record5','qtr1','Fin5' UNION ALL
SELECT 2222,'B','Materials6','Paper6','record6','qtr2','Fin6'
SELECT * FROM #Function
SELECT * FROM #Department
DECLARE @sql varchar(max)
SET @sql='CREATE VIEW MyView AS SELECT A1 as'
SET @sql=@SQL+' '+(SELECT FieldName FROM #Function WHERE DisplayName = 'A1' AND StoreID = 2221)
SET @sql=@SQL+' FROM #Department D'
EXEC (@SQL)
January 5, 2011 at 10:49 am
The closest you could get to what you're trying to do would be some sort of pivot operation. T-SQL does those, but not very well.
Views can't be dynamic. They're just stored select statements that you can re-use as a shorthand for having to write out the whole view each time.
You'd get closer to what you're trying to achieve in some sort of OODB than in any relational DB. Relations really aren't meant to work that way.
Try to ignore Joe's arrogance. He's right about how relational databases work, but his approach to communication through the Internet is based on a false understanding of how the human mind works in a learning environment. He's stuck in teaching methodologies that predate literacy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 11:29 am
Thanks Joe for you kind sarcastic words, I am doing my bit by explaining a scenario which is there in our systems and redefining those which is totally not under my control. Its a situation where you need to work around and not reason its existence in production systems.
@GSquared; I am figuring out the Pivot approach; which however I am not certain of its accuracy and the way I would like to implement it, And this is just a root node of the problem which is going to spiral up in the hierarchy.
Thanks once again for your Inputs.
January 5, 2011 at 11:34 am
Is this something you can do with a stored procedure, instead of a view? If so, stored procedures can do dynamic SQL and could build a select statement each time from the metadata you have in your table.
That's got drawbacks, in that the output from the proc won't be the same each time, which will make coding any sort of application against it nearly impossible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 11:53 am
The approach to this issue was to add a trigger to the table wherein these values are getting inserted, and once this values are inserted, Build up the view ( which is fine to this point ) but now the issue is I have multiple Store ID's , which would result in one view per Store. This is were this whole theory is breaking off. ( Not to create multiple views per store, but have one view generic.)
January 5, 2011 at 12:35 pm
I'm assuming that different stores have different columns. If that's the case, you won't be able to have one view with all stores. Unless you include ALL columns for it, and some stores will have empty columns because they don't use that data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 12:55 pm
Yes you are right; Different stores will have different vlaues configured in the DB; and hence different values for the column aliases. All combinations to the views could be included, however reports would be generated based on these views and would be confusing for the end user, as to which belongs to which store ID.
I was thinking of a generic way to figure out if it could be done.
January 6, 2011 at 7:29 am
Go with one view per store, even if two stores are identical in their columns. That's not a good general database practice, but it sounds like the only practical way to solve the problem you've run into.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2011 at 10:26 am
That figures out to be one of the solution, but it is not much practical as of now, since we have multiple stores in the DB and reports would be potentially run on multiple such stores simultaneously.
I am currently verifying the PIVOT approach, to see its feasibility and to check if that could get implemented in the long run.
Thanks for your inputs.
January 6, 2011 at 10:37 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2011 at 3:32 pm
If the application developers want the data pivoted in some funky way, then grant them select permission on the tables, and tell them to create the SQL dynamically. There is no need to create a view on the fly like this. The whole idea with a view is that you have a simple query that looks like a table and can be re-used.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 7, 2011 at 3:48 pm
One alternative to this you might want to approach is the use of schemas on a per store basis. This way the front end code/proc/view calls don't have to be written differently for each store, but each store would have a different login user and thus default to a different schema.
You're still looking at a view/proc per store, but it will allow for an alternative approach, and one of the few valid uses of schema I've come across.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply