Adding two fields together
-
I am trying to add two fields together to get a new field. Actually I am doing this twice. I am trying to replicate the table in the attached pic. What is wrong with my syntax?
CREATE TABLE
FloorEx
(
Rank char(1),
Gymnast varchar(50),
Compulsory DECIMAL (5,3)
Optionals DECIMAL (5,3),
Compulsory + Optionals AS [Compul and Optionals],
Prelims DECIMAL (5,3),
Finals DECIMAL (5,3),
Prelims + Finals AS Total
)
The column called "Compul and Optionals" is the sum of Compulsory and Optionals. The column called Total is the sum of Prelims and Finals. The error message says "Incorrect syntax near 'Optionals'."
-
You're missing a comma after Compulsory DECIMAL (5,3). Then, for your computed columns, the correct syntax is [Compul and Optionals] AS Compulsory + Optionals, ({Column Name} AS {Expression}). You'll need to apply that to syntax to both of your computed columns.
I also, however, would avoid using special characters (which include White Space) in object Names. You might want to consider CamelCase.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, October 3, 2018 2:11 AM
Awesome. It worked. I totally overlooked the missing comma.
I thought 'AS' was used only to designate an alias. Does it have additional uses besides adding the values of two columns together? Do you know of any websites that discuss the other uses of the keyword 'AS?'
- michael.leach2015 - Wednesday, October 3, 2018 8:04 PM
You'll most likely see AS used for Aliasing with the syntax {Expression} AS {Alias}, however, it does appear in other places. For example, when declaring a computed column the syntax is {Column Name} AS {Expression}, and when declared a CTE the syntax is WITH {CTE Name} AS. AS doesn't have a specific function, it's just part of the syntax more that often (maybe to make T-SQL appear more like English?). For Aliasing, you don't even need the AS. {Expression} {Alias} and {Expression} AS {Alias} as synonyms.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, October 4, 2018 1:59 AM
When using AS with an alias, for example in a SELECT statement, first you list the column name, then AS, then the alias such as
SELECT expense_cost AS ExpenseBased on your syntax above, this would imply that a column is an expression. Is this correct? Can a column count as an expression?
- michael.leach2015 - Thursday, October 4, 2018 7:27 PM
See the following for the answer to that and more...
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/expressions-transact-sql?view=sql-server-2017--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Thursday, October 4, 2018 7:37 PM
Great. Thank you. Good link.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply