Solved

Veiw - Creating a Column on the Fly

Posted on 2013-02-05
11
255 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 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