Veiw - Creating a Column on the Fly

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

PtboGiserAsked:
Who is Participating?
 
Ioannis ParaskevopoulosConnect With a Mentor Commented:
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
 
PtboGiserAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
PtboGiserAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
PtboGiserAuthor Commented:
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
 
PtboGiserAuthor Commented:
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
 
PtboGiserAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
Ioannis ParaskevopoulosCommented:
You could also ommit that.

Giannis
0
 
PtboGiserAuthor Commented:
Thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.