Computed Columns in SQL

1. Member
Join Date
Dec 1969
Posts
96

Computed Columns in SQL

Hi...<BR><BR>Im trying to compute a column in SQL with the following options:<BR><BR>people , Cost<BR><BR>1-50 , 3<BR>51-80 , 5<BR>81-100 , 7.50<BR>100+ , 10.00<BR><BR>I thought of creating a trigger and allowing it to compute it that way but im having problems inserting the computed figure into the appropriate column with an error stating " No column was specified for column 1 of &#039;SubTotal&#039;."<BR><BR>the source is as follows:<BR><BR><BR>/** Creating a trigger for computed column**/<BR>Create trigger tri_SubTotal<BR>on tbl_Commission<BR>FOR INSERT, UPDATE AS<BR>declare @x int<BR>declare @y money<BR>select @x = C.NumberOfLILAs FROM tbl_Commission C, INSERTED I<BR>WHERE C.CommissionID = I.CommissionID<BR>BEGIN<BR>if @x &#060; 51<BR>select @y = @x * 3<BR>Else IF @x between 51 and 80<BR>Select @y = @x * 5<BR>ELSE IF @x between 81 and 100<BR>Select @y = @x * 7.50<BR>Else IF @x &#062; 101<BR>Select @y = @x * 10<BR>SELECT @y into SubTotal<BR>END<BR><BR><BR>/** end of trigger **/<BR><BR>any help would be greatly appreciated..<BR><BR>thanx<BR><BR>Marcus

2. Sreedharan Guest

RE: Computed Columns in SQL

Your @y is a formula/value. When you do that, you have to give a name for the column. Make the change in your second-last line. )Full sql is given)<BR><BR>/** Creating a trigger for computed column**/ <BR>Create trigger tri_SubTotal <BR>on tbl_Commission <BR>FOR INSERT, UPDATE AS <BR>declare @x int <BR>declare @y money <BR>select @x = C.NumberOfLILAs FROM tbl_Commission C, INSERTED I <BR>WHERE C.CommissionID = I.CommissionID <BR>BEGIN <BR>if @x &#060; 51 <BR>select @y = @x * 3 <BR>Else IF @x between 51 and 80 <BR>Select @y = @x * 5 <BR>ELSE IF @x between 81 and 100 <BR>Select @y = @x * 7.50 <BR>Else IF @x &#062; 101 <BR>Select @y = @x * 10 <BR>SELECT @y As YourFieldname into SubTotal <BR>END <BR><BR><BR>By the way, I think that this will also give you a problem, since now a table called "SubTotal" will be created, with a fieldname called "YourFieldName". Your entire logic is wrong. <BR>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•