Simply put: I have a table with 2 columns: one is username and other is nickname. I have another table where I have 2 columns: one is username and other is countNicknames. I want countNicknames to contain the number of nicknames each user has (and whenever I insert a new nickname to the table1, the value under table2.countNicknames will...
Is there any way of adding custom text in an computed column? For example this formula works great ([Duration] + '12') Could i have a result, from a computed column, similar to this one? ([Duration] & ' MyCustomText') Can i add custom text in a computed column? Or am i asking too much? ...
My current view produces this kind of output... Product1 Product2 Jet Ski Water Polo Could i use a computed column in a view, in order getting results like these? Product1 Product2 Computed Jet Ski Jet Ski Water Polo Water Polo ...
Does a SQL Server Computed Column require "persistence" in order for it to be indexed efficiently? It's a Name and a LoweredName (computed) column combination. So I don't believe it deals with the precision stuff mentioned in a MSDN article. ...
I have a computed column created with the following line: alter table tbPedidos add restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 then 1 else 0 end as bit)) But, now I need to change this column for something like: alter table tbPedidos alter column restricoes as (cast(case when restricaoLicenca = 1 or res...
We have a few tables with persisted computed columns in SQL Server. Is there an equivalent of this in Teradata? And, if so, what is the syntax and are there any limitations? The particular computed columns I am looking at conform some account numbers by removing leading zeros - an index is also created on this conformed account number...
Here is a snap of my database. Both col1 and col2 are declared as int. My ComputedColumn currently adds the Columns 1 and 2, as follows... col1 col2 ComputedColumn 1 2 3 4 1 5 Instead of this, my ComputedColumn should join the columns 1 and 2 (includimg the '-' character in the middle) as follows... col1 col2 C...
Table A has a computed field called Computed1. It's persisted and not null. Also, it always computes to an expression which is char(50). It's also unique and has a unique key constraint on it. Table B has a field RefersToComputed1, which should refer to a valid Computed1 value. Trying to create a foreign key constraint on B's RefersT...
Using the Visual Studio Data Generation Plan, I have two columns, FirstName and Lastname, populated from a Data Bound Generator (referencing a database seeded with actual valid First and Last Names). I want to generate another column for email with the format @test.com. For example: Fred, Smith => [email protected] Right now I am us...
SQL Server Beginner question: I'm trying to introduce a computed column in SQL Server (2008). In the table designer of SQL Server Management Studio I can do this, but the designer only offers me one single edit cell to define the expression for this column. Since my computed column will be rather complicated (depending on several databa...
Hi, I'm trying to rename a column using sp_rename but it's referenced in a computed column. I'm getting the following error: 'Table.Column' cannot be renamed because the object participates in enforced dependencies. As far as I can tell the (persisted) computed column is the only place this is referenced. I guess I can drop and re...
I have table in sql as User | Account -----+--------- 1 | 25 1 | 31 1 | 35 1 | 44 1 | 50 1 | 59 and output need in as three columns 1 | 25 | 31 1 | 35 | 44 1 | 50 | 59 ...
Consider this table: c_const code | nvalue -------------- 1 | 10000 2 | 20000 and another table t_anytable rec_id | s_id | n_code --------------------- 2 | x | 1 The goal is to have s_id be a computed column, based on this formula: rec_id*(select nvalue from c_const where code=ncode) This produc...
In my SQL table: Period| Brand A small Bags| Brand A big bags| Brand D Shoes| ...| Brand X Shoes 2010 | 10 | 20 | 30 | ...| 200 How do I sum columns that contains certain words (e.g shoes) in the column names ? Expected results: Period | Sum of Bags | Sum of Shoes | .. 2010 | 30 ...
Say I have a basic table, products, with 3 fields, productname, cost, and costnotax. Is it possible to automatically fill in the costnotax field by subtracting a percentage from the cost field? ...
Dear Colleagues, I want to alter a view as follows: ALTER VIEW [dbo].[ViewOne] as SELECT columnOne, -- not null columnTwo, --not null (convert(decimal(2,0), columnOne)) as columnThree -- I want this not to be NULL FROM DBOne.TableOne Since columnOne is "not null" I want to force columnThree to be "not null" also. Is...
I'm having a hard time phrasing this question in such a way that doesn't turn up results for persisted, indexed computed columns. My question is, if I have a table such as: CREATE TABLE Customers ( ID int, Name nvarchar(50), Balance money, HasBalance AS CONVERT(bit, CASE WHEN Balance > 0 THEN 1 ELSE 0 END) ) Assuming ...
I want to dynamically create a database using my DataContext's CreateDatabase Method. I have manually created mapping classes and tested them. But as soon as I add the Expression Column (see below) the creation fails with an SqlCeException and I am unable to find out the exact reason. /// <summary> /// The sum of ratings for thi...
I am trying to create a persisted computed column using CASE expression: ALTER TABLE dbo.Calendar ADD PreviousDate AS case WHEN [Date]>'20100101' THEN [Date] ELSE NULL END PERSISTED MSDN clearly says that CASE is deterministic, here However, I am getting an error: Msg 4936, Level 16, State 1, Line 1 Computed column 'Previo...
Consider the following table and rows: Listing A. ID, name, event, type 1, 'John Doe', '2010-09-01 15:00:00.000', 'input' 1, 'John Doe', '2010-09-03 11:00:00.000', 'input' 1, 'John Doe', '2010-09-04 17:00:00.000', 'input' 1, 'John Doe', '2010-09-02 15:00:00.000', 'output' 1, 'John Doe', '2010-09-03 16:00:00.000', 'output' 1, 'John Doe'...