creating new column in a VIEW - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

creating new column in a VIEW

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-04 : 14:24:39

I have created a view from the union of different tables. I would like to know how I can add a column to the view for all unioned tables and do some calculation there.

Thx

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-04 : 15:03:44

ALTER VIEW

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-04 : 15:36:59

Thanks,
My View looks like below:

ALTER view [dev].[GAMS_Init_Var]
as
SELECT geo_orig O
,'001' D
,mode_dom M
,'$0000' C
,val V0
,val_f Vf
,Val_cv Vcv
,ton T0
,ton_f Tf
,ton_cv Tcv
,tmile TM0
,tmile_f TMf
,tmile_cv TMcv
FROM [bts].[cf07ur00].[a01]
UNION
SELECT geo_orig O
,'001' D
,mode_dom M
,'$0000' C
,CAST(NULL as int) V0
,CAST(NULL as CHAR) Vf
,CAST(NULL as int) Vcv
,CAST(NULL as int) T0
,CAST(NULL as CHAR) Tf
,CAST(NULL as int) Tcv
,tmile TM0
,tmile_f TMf
,tmile_cv TMcv
FROM [bts].[cf07ur00].[a02]

Now I want to add a column let say "variance" to this view, and do some calculation for example "V0+T0" on that. How should I do it?

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-04 : 16:23:32

ALTER view [dev].[GAMS_Init_Var]
as
SELECT geo_orig O
,'001' D
,mode_dom M
,'$0000' C
,val V0
,val_f Vf
,Val_cv Vcv
,ton T0
,ton_f Tf
,ton_cv Tcv
,tmile TM0
,tmile_f TMf
,tmile_cv TMcv
,val + ton variance
FROM [bts].[cf07ur00].[a01]
UNION
SELECT geo_orig O
,'001' D
,mode_dom M
,'$0000' C
,CAST(NULL as int) V0
,CAST(NULL as CHAR) Vf
,CAST(NULL as int) Vcv
,CAST(NULL as int) T0
,CAST(NULL as CHAR) Tf
,CAST(NULL as int) Tcv
,tmile TM0
,tmile_f TMf
,tmile_cv TMcv
,NULL variance
FROM [bts].[cf07ur00].[a02]

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-04 : 16:34:45

First, is it possible that you can get all NULL values from A01 for these columns: val, val_f, val_cv, ton, ton_f, ton_cv? If not, change the UNION to a UNION ALL which will perform better.

Second, to add a column - modify the query and add your column.


ALTER view [dev].[GAMS_Init_Var]
as
SELECT geo_orig O
,'001' D
,mode_dom M
,'$0000' C
,val V0
,val_f Vf
,Val_cv Vcv
,ton T0
,ton_f Tf
,ton_cv Tcv
,tmile TM0
,tmile_f TMf
,tmile_cv TMcv
,(val + ton) AS variance
FROM [bts].[cf07ur00].[a01]
UNION
SELECT geo_orig O
,'001' D
,mode_dom M
,'$0000' C
,CAST(NULL as int) V0
,CAST(NULL as CHAR) Vf
,CAST(NULL as int) Vcv
,CAST(NULL as int) T0
,CAST(NULL as CHAR) Tf
,CAST(NULL as int) Tcv
,tmile TM0
,tmile_f TMf
,tmile_cv TMcv
,(val + ton) AS variance
FROM [bts].[cf07ur00].[a02]

Jeff

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-04 : 19:32:34

True about the union all. The "val + ton) AS variance" in the second query should just be explicitly nulled I assume, since OP is explicitly nulling the values combined.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-04 : 21:00:14

Yes, it probably should just be null - missed that part of it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources