Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Veiw - Creating a Column on the Fly

Posted on 2013-02-05
11
Medium Priority
?
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 

Author Comment

by:PtboGiser
ID: 38855018
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
ID: 38855098
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
ID: 38855131
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38855189
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
ID: 38855216
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
 

Author Comment

by:PtboGiser
ID: 38855221
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
ID: 38855234
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 2000 total points
ID: 38855385
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
ID: 38855394
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
ID: 38855400
You could also ommit that.

Giannis
0
 

Author Closing Comment

by:PtboGiser
ID: 38855590
Thx
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

722 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