Solved

Veiw - Creating a Column on the Fly

Posted on 2013-02-05
11
249 Views
Last Modified: 2013-02-05
Hey Crew
I looking at building a veiw with a new column(Atlas_Level) this new column will be based off the RoadJur column.

When RoadJur = Kings
Set Altas_Level = 9
When RoadJur = CNTY
Set Altas_Level = 15
When RoadJur = TWP
Set Altas_Level = 16
When RoadJur = PRIV
Set Altas_Level = 17

I'm think my first step should be to Alter View the existing Veiw (Code below) this creating the new column to populate correct?
The the Second Step will be to Populate the Column within the SQL Code.
Thoughts?

Alter View ATLAS_Segment
(Atlas_Level)
SELECT     dbo.Segment.ROADJUR, dbo.Segment.Street_Full_Name, dbo.Segment.L_F_ADD, dbo.Segment.L_T_ADD, dbo.Segment.R_F_ADD, dbo.Segment.R_T_ADD, 
                       
(SELECT     Ward_Name
                            FROM          dbo.Ward AS W
                            WHERE      (Ward_ID = dbo.Segment.Left_Mun_ID)) AS L_MUNAME,
                          (SELECT     Ward_Name
                            FROM          dbo.Ward AS W
                            WHERE      (Ward_ID = dbo.Segment.Right_Mun_ID)) AS R_MUNAME

FROM         dbo.Segment INNER JOIN
                      dbo.Ward ON dbo.Segment.Left_Mun_ID = dbo.Ward.Ward_ID AND dbo.Segment.Right_Mun_ID = dbo.Ward.Ward_ID

Open in new window

0
Comment
Question by:PtboGiser
  • 6
  • 5
11 Comments
 

Author Comment

by:PtboGiser
Comment Utility
I will only need the Atlas_Level column for this one application. So i don't really want to carry it around in my Segment Table as a computed column all the time. So i'm hoping to build it for only this view.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
Hi,

You may either alter the view :

Alter View ATLAS_Segment
(Atlas_Level)
SELECT	dbo.Segment.ROADJUR, 
		dbo.Segment.Street_Full_Name, 
		dbo.Segment.L_F_ADD, 
		dbo.Segment.L_T_ADD, 
		dbo.Segment.R_F_ADD, 
		dbo.Segment.R_T_ADD, 
        (
			SELECT	Ward_Name
			FROM	dbo.Ward AS W
			WHERE	(
						Ward_ID = dbo.Segment.Left_Mun_ID
					)
		) AS L_MUNAME,
		(
			SELECT	Ward_Name
			FROM	dbo.Ward AS W
			WHERE	(Ward_ID = dbo.Segment.Right_Mun_ID)
		) AS R_MUNAME,
		CASE 
			WHEN dbo.Segment.RoadJur = Kings THEN Altas_Level = 9
			WHEN dbo.Segment.RoadJur = CNTY THEN Altas_Level = 15
			WHEN dbo.Segment.RoadJur = TWP THEN Altas_Level = 16
			WHEN dbo.Segment.RoadJur = PRIV THEN Altas_Level = 17
		END
FROM	dbo.Segment INNER JOIN
        dbo.Ward ON dbo.Segment.Left_Mun_ID = dbo.Ward.Ward_ID AND dbo.Segment.Right_Mun_ID = dbo.Ward.Ward_ID

Open in new window


or consider the view (if it already exists) as atable and query it as follows:

SELECT	*,
		CASE 
			WHEN dbo.Segment.RoadJur = Kings THEN Altas_Level = 9
			WHEN dbo.Segment.RoadJur = CNTY THEN Altas_Level = 15
			WHEN dbo.Segment.RoadJur = TWP THEN Altas_Level = 16
			WHEN dbo.Segment.RoadJur = PRIV THEN Altas_Level = 17
		END
FROM	ATLAS_Segment       

Open in new window


Giannis
0
 

Author Comment

by:PtboGiser
Comment Utility
Msg 102, Level 15, State 1, Procedure ATLAS_Segment, Line 22
Incorrect syntax near '='.

With the Alter View Option.
My syntax is incorrect i'll see what i can figure out.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
I am really sorry, it should be:

Alter View ATLAS_Segment
(Atlas_Level)
SELECT	dbo.Segment.ROADJUR, 
		dbo.Segment.Street_Full_Name, 
		dbo.Segment.L_F_ADD, 
		dbo.Segment.L_T_ADD, 
		dbo.Segment.R_F_ADD, 
		dbo.Segment.R_T_ADD, 
        (
			SELECT	Ward_Name
			FROM	dbo.Ward AS W
			WHERE	(
						Ward_ID = dbo.Segment.Left_Mun_ID
					)
		) AS L_MUNAME,
		(
			SELECT	Ward_Name
			FROM	dbo.Ward AS W
			WHERE	(Ward_ID = dbo.Segment.Right_Mun_ID)
		) AS R_MUNAME,
		CASE 
			WHEN dbo.Segment.RoadJur = Kings THEN  9
			WHEN dbo.Segment.RoadJur = CNTY THEN 15
			WHEN dbo.Segment.RoadJur = TWP THEN 16
			WHEN dbo.Segment.RoadJur = PRIV THEN 17
		END AS Altas_Level 
FROM	dbo.Segment INNER JOIN
        dbo.Ward ON dbo.Segment.Left_Mun_ID = dbo.Ward.Ward_ID AND dbo.Segment.Right_Mun_ID = dbo.Ward.Ward_ID

Open in new window


or

SELECT	*,
		CASE 
			WHEN dbo.Segment.RoadJur = Kings THEN 9
			WHEN dbo.Segment.RoadJur = CNTY THEN 15
			WHEN dbo.Segment.RoadJur = TWP THEN 16
			WHEN dbo.Segment.RoadJur = PRIV THEN 17
		END AS Altas_Level 
FROM	ATLAS_Segment       

Open in new window


Giannis
0
 

Author Comment

by:PtboGiser
Comment Utility
Getting Closer, I was just about to do the same thing you responded about ha-ha.
Msg 207, Level 16, State 1, Procedure ATLAS_Segment, Line 23
Invalid column name 'KINGS'.
Msg 207, Level 16, State 1, Procedure ATLAS_Segment, Line 24
Invalid column name 'CNTY'.
Msg 207, Level 16, State 1, Procedure ATLAS_Segment, Line 25
Invalid column name 'TWP'.
Msg 207, Level 16, State 1, Procedure ATLAS_Segment, Line 26
Invalid column name 'PRIV'.
Msg 8158, Level 16, State 1, Procedure ATLAS_Segment, Line 1
'ATLAS_Segment' has more columns than were specified in the column list.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:PtboGiser
Comment Utility
Alter View ATLAS_Segment
(Atlas_Level)
AS
SELECT      dbo.Segment.ROADJUR,
            dbo.Segment.Street_Full_Name,
            dbo.Segment.L_F_ADD,
            dbo.Segment.L_T_ADD,
            dbo.Segment.R_F_ADD,
            dbo.Segment.R_T_ADD,
                        
        (SELECT      Ward_Name
                  FROM      dbo.Ward AS W
                  WHERE      (
                                    Ward_ID = dbo.Segment.Left_Mun_ID
                              )
            ) AS L_MUNAME,
            (
                  SELECT      Ward_Name
                  FROM      dbo.Ward AS W
                  WHERE      (Ward_ID = dbo.Segment.Right_Mun_ID)
            ) AS R_MUNAME,
            CASE
                  WHEN dbo.Segment.ROADJUR = KINGS THEN 9
                  WHEN dbo.Segment.ROADJUR = CNTY THEN 15
                  WHEN dbo.Segment.ROADJUR = TWP THEN 16
                  WHEN dbo.Segment.ROADJUR = PRIV THEN 17
            END AS Altas_Level
FROM      dbo.Segment INNER JOIN
        dbo.Ward ON dbo.Segment.Left_Mun_ID = dbo.Ward.Ward_ID AND dbo.Segment.Right_Mun_ID = dbo.Ward.Ward_ID
0
 

Author Comment

by:PtboGiser
Comment Utility
Put in single Quotes
CASE
                  WHEN dbo.Segment.ROADJUR = 'KINGS' THEN 9
                  WHEN dbo.Segment.ROADJUR = 'CNTY' THEN 15
                  WHEN dbo.Segment.ROADJUR = 'TWP' THEN 16
                  WHEN dbo.Segment.ROADJUR = 'PRIV' THEN 17
            END AS Atlas_Level

Now
Msg 8158, Level 16, State 1, Procedure ATLAS_Segment, Line 1
'ATLAS_Segment' has more columns than were specified in the column lis
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
Comment Utility
Can you try this:

Alter View ATLAS_Segment 
(ROADJUR,
Street_Full_Name,
L_F_ADD,
L_T_ADD,
R_F_ADD,
R_T_ADD,
L_MUNAME,
R_MUNAME,
Atlas_Level)
AS
SELECT      dbo.Segment.ROADJUR, 
            dbo.Segment.Street_Full_Name, 
            dbo.Segment.L_F_ADD, 
            dbo.Segment.L_T_ADD, 
            dbo.Segment.R_F_ADD, 
            dbo.Segment.R_T_ADD, 
                        
        (SELECT      Ward_Name
                  FROM      dbo.Ward AS W
                  WHERE      (
                                    Ward_ID = dbo.Segment.Left_Mun_ID
                              )
            ) AS L_MUNAME,
            (
                  SELECT      Ward_Name
                  FROM      dbo.Ward AS W
                  WHERE      (Ward_ID = dbo.Segment.Right_Mun_ID)
            ) AS R_MUNAME,
            CASE 
                  WHEN dbo.Segment.ROADJUR = KINGS THEN 9
                  WHEN dbo.Segment.ROADJUR = CNTY THEN 15
                  WHEN dbo.Segment.ROADJUR = TWP THEN 16
                  WHEN dbo.Segment.ROADJUR = PRIV THEN 17
            END AS Altas_Level
FROM      dbo.Segment INNER JOIN
        dbo.Ward ON dbo.Segment.Left_Mun_ID = dbo.Ward.Ward_ID AND dbo.Segment.Right_Mun_ID = dbo.Ward.Ward_ID

Open in new window


Giannis
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
To be more precise, it sems that in the parenthesis after the alter view statement you have to list all the columns returned by the view
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
You could also ommit that.

Giannis
0
 

Author Closing Comment

by:PtboGiser
Comment Utility
Thx
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now