How to create a calculated column in a SQL Server 2008 table

Question

I really need a calculated column on a table with simple sum.

Please see below:

SELECT   key3
         ,SUM(UTOTALWBUD)
FROM     CONTACT1
         INNER JOIN CONTACT2
            ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
WHERE    KEY1 = 'Client'
GROUP BY KEY3

I have tried to create a calculated column by adding following

ALTER TABLE ManagerTaLog
ADD         WeeklyBudget as (   SELECT
                                        key3
                                        ,SUM(UTOTALWBUD)
                            FROM        CONTACT1
                                        JOIN CONTACT2
                                            ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
                            WHERE       KEY1 = 'Client'
                            GROUP BY    KEY3)

I got the error message:

Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Please advise what can I do about it.

Many thanks

Part 2

I have create a function; however, i get null values please advise.

CREATE FUNCTION [dbo].[SumIt](@Key3 varchar)
RETURNS TABLE 
AS
RETURN
(
SELECT      SUM(UTOTALWBUD)
FROM        CONTACT1
            JOIN CONTACT2
                ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
            JOIN Phone_List
                ON CONTACT1.KEY3 = Phone_List.[Manager ]
WHERE       KEY1 = 'Client'
            AND Phone_List.[Manager ] = @Key3
GROUP BY [Manager ]

)
END

GO

Just select statment that returns values I wish to add to Phone_list table

SELECT      [Manager ]
            ,SUM(UTOTALWBUD)
FROM        CONTACT1
            JOIN CONTACT2
                ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
            JOIN Phone_List
                ON CONTACT1.KEY3 = Phone_List.[Manager ]
WHERE       KEY1 = 'Client'
GROUP BY    [Manager ]

Table definitions

CREATE TABLE [dbo].[CONTACT1](
    [ACCOUNTNO] [varchar](20) NOT NULL,
    [COMPANY] [varchar](40) NULL,
    [CONTACT] [varchar](40) NULL,
    [LASTNAME] [varchar](15) NULL,
    [DEPARTMENT] [varchar](35) NULL,
    [TITLE] [varchar](35) NULL,
    [SECR] [varchar](20) NULL,
    [PHONE1] [varchar](25) NOT NULL,
    [PHONE2] [varchar](25) NULL,
    [PHONE3] [varchar](25) NULL,
    [FAX] [varchar](25) NULL,
    [EXT1] [varchar](6) NULL,
    [EXT2] [varchar](6) NULL,
    [EXT3] [varchar](6) NULL,
    [EXT4] [varchar](6) NULL,
    [ADDRESS1] [varchar](40) NULL,
    [ADDRESS2] [varchar](40) NULL,
    [ADDRESS3] [varchar](40) NULL,
    [CITY] [varchar](30) NULL,
    [STATE] [varchar](20) NULL,
    [ZIP] [varchar](10) NOT NULL,
    [COUNTRY] [varchar](20) NULL,
    [DEAR] [varchar](20) NULL,
    [SOURCE] [varchar](20) NULL,
    [KEY1] [varchar](20) NULL,
    [KEY2] [varchar](20) NULL,
    [KEY3] [varchar](20) NULL,
    [KEY4] [varchar](20) NULL,
    [KEY5] [varchar](20) NULL,
    [STATUS] [varchar](3) NOT NULL,
    [NOTES] [text] NULL,
    [MERGECODES] [varchar](20) NULL,
    [CREATEBY] [varchar](8) NULL,
    [CREATEON] [datetime] NULL,
    [CREATEAT] [varchar](5) NULL,
    [OWNER] [varchar](8) NOT NULL,
    [LASTUSER] [varchar](8) NULL,
    [LASTDATE] [datetime] NULL,
    [LASTTIME] [varchar](5) NULL,
    [U_COMPANY] [varchar](40) NOT NULL,
    [U_CONTACT] [varchar](40) NOT NULL,
    [U_LASTNAME] [varchar](15) NOT NULL,
    [U_CITY] [varchar](30) NOT NULL,
    [U_STATE] [varchar](20) NOT NULL,
    [U_COUNTRY] [varchar](20) NOT NULL,
    [U_KEY1] [varchar](20) NOT NULL,
    [U_KEY2] [varchar](20) NOT NULL,
    [U_KEY3] [varchar](20) NOT NULL,
    [U_KEY4] [varchar](20) NOT NULL,
    [U_KEY5] [varchar](20) NOT NULL,
    [recid] [varchar](15) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



CREATE TABLE [dbo].[Phone_List](
    [Manager ] [nvarchar](255) NULL,
    [SalesCode] [nvarchar](255) NULL,
    [Email] [nvarchar](255) NULL,
    [PayrollCode] [nvarchar](255) NULL,
    [Mobile] [nvarchar](255) NULL,
    [FName] [nchar](20) NULL,
    [idd] [tinyint] NULL,
    [OD] [varchar](20) NULL,
    [WeeklyBudget]  AS ([dbo].[SumIt]([manager]))
) ON [PRIMARY]

Your subquery returns two values - how is this going to be stored in a single computed column?!?!?!? — marc_s
– marc_s, Commented Jan 4, 2012 at 10:57
Show us the table definition. Are you certain that Key3 has a value? — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Jan 4, 2012 at 13:05
Not sure if it's relevant but you call the function with ([dbo].[SumIt]([manager])) instead of ([dbo].[SumIt]([manager ])) (note the extra space). — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Jan 4, 2012 at 19:03

Oleg Dok · Accepted Answer · 2012-01-05 15:16:47Z

8

You can wrap your query into the function like this (it HAS to return one value):

CREATE FUNCTION dbo.SumIt(@Key1 varchar(max))
returns float
as
begin
  return (select sum(UTOTALWBUD) from
   CONTACT1 inner join
   CONTACT2 on
   CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO
   where KEY1=@key1
   group by KEY3)
END

And use this function instead with calc field - something like this:

alter table ManagerTaLog add WeeklyBudget as dbo.SumIt(Key1)

NOTE

that it will be the performance killer for queries like that:

select * from ManagerTaLog

You should change your function in such a way, that is accept NOT varchar value, but NVARCHAR(255) - the same type as Manager column. Try it.

edited Jan 5, 2012 at 15:16

answered Jan 4, 2012 at 11:02

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lieven Keersmaekers · Accepted Answer · 2012-01-04 11:02:20Z

1

If you resolve the issue of returning two values, one solution would be to implement the calculation in a function and use this function for the calculated column.

Some considerations

I have assumed that it is Key3 that needs to be passed to the function and have added a where clause to return the weekly budget for the given Key3 value.
The function will get executed for every record in a resultset where you add the calculated column.

Script

CREATE FUNCTION dbo.fn_ManagerTaLogWeeklyBudget(@Key3 INTEGER) RETURNS INTEGER AS 
BEGIN
  select sum(UTOTALWBUD) from
  CONTACT1 inner join
  CONTACT2 on
  CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO
  where KEY1='Client'
        AND KEY3 = @Key3
  group by KEY3)

END
GO

ALTER TABLE dbo.ManagerTaLog
ADD WeeklyBudget AS dbo.fn_ManagerTaLogWeeklyBudget(Key3)

answered Jan 4, 2012 at 11:02

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

2 Comments

BI Dude Over a year ago

Thanks for the post. I have created function and altered table; however, result gives me null values

Lieven Keersmaekers Over a year ago

@user995506 - What about the comment I posted on your question 20 hours ago?

marc_s · Accepted Answer · 2012-01-04 11:04:27Z

1

You cannot have a subquery that returns multiple values to be used as the computed column expression.

You can have an expression that returns a single value - or you can wrap your code in a stored function - or you can create a view (with a JOIN or subquery) that combines this logic into something you can use

answered Jan 4, 2012 at 11:04

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Comments

Daniel · Accepted Answer · 2012-01-09 12:15:18Z

1

IMHO It is a wrong way. You have to use trigger on CONTACT tables and update WeeklyBudget in these triggers.

answered Jan 9, 2012 at 12:15

Daniel

1336 bronze badges

Comments

Las Ten · Accepted Answer · 2012-01-04 21:41:54Z

0

You can have calculated columns in a table but that will be present (and calculated) in all rows of the table. The thing what you're trying to do in your select is called "aggregate". Try this:

select key3, sum(UTOTALWBUD) as WeeklyBudget from
CONTACT1 inner join CONTACT2 on CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO
where KEY1='Client' 
group by key3

answered Jan 4, 2012 at 21:41

Las Ten

1,17510 silver badges22 bronze badges

Comments

Gopakumar N.Kurup · Accepted Answer · 2015-11-03 13:25:25Z

CREATE FUNCTION [dbo].[SumIt](@Key3 varchar)
RETURNS TABLE 
AS
RETURN
(
SELECT      SUM(ISNULL(UTOTALWBUD,0))
FROM        CONTACT1
            JOIN CONTACT2
                ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO
            JOIN Phone_List
                ON CONTACT1.KEY3 = Phone_List.[Manager ]
WHERE       KEY1 = 'Client'
            AND Phone_List.[Manager ] = @Key3
GROUP BY [Manager ]

)
END

GO

Hope this will help. Please note that this is not a proper solution, but this may help your particular scenario. Since the function is returning multiple values, you can either user a table valued function or just modify the query in a way that it will return only one value. But if the column is nullable please don't forget to add ISNULL.

Collectives™ on Stack Overflow

How to create a calculated column in a SQL Server 2008 table

Part 2

Table definitions

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Part 2

Table definitions

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related