Solved

Veiw - Creating a Column on the Fly

Posted on 2013-02-05
11
252 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

832 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