Hello,
does anyone know a website, where I can read something about the syntax of Computed Columns?
I don't know how to enter the following expression in the computed columns field of MS SQL Server:
When x-y < 0 Then 0 else x-y
Thank you
M-l-G
Is it possible to get a Computed column in one table to carry out a SELECT & SUM from a column in another table? I have tried a SELECT / FROM / WHERE construct but SQL complains about that.
Regards
Clive
I am working with data that has a lot of records that get updated and inserted frequently, and to avoid having to create formulas through code all over the place, I am experimenting with computed column formulas. I have a question though.. It works well for any addition or subtraction (columnA+columnB), however, when I try to use division (columnA/columnB), it only returns integers, no decimals. I would like to have decimals, particularly with a specific scale and precision and I would really like to attempt this without any coding. Any suggestions?
View 3 Replies View RelatedI have a table with fields called fname (First Name) and lname (Last Name). I need the userīs email thai is compose from lname and fname:
LOWER(LEFT (fname,1) + lname)
Is there any difference between creatig this computed column ia a table or in a view in SQL Server 2000?
I can do:
1. CREATE TABLE Users(
fname varchar(20),
lname varchar(20),
email as LOWER(LEFT (fname,1) + lname) )
Or
2. CREATE TABLE Users (
fname varchar(20),
lname varchar(20))
CREATE VIEW Vw_users (fname, Lname ,
email)
AS
SELECT fname, Lname ,
LOWER(LEFT (fname,1) + lname) )
Is one of them is better?
Paulo
Dear all,
Pls help me with this
I have 2 tables
Trip (TripID, Duration)
Reserve(ReserveID, TripID, StartDay, EndDate)
in which EndDate = StartDay + Trip.Duration
How can I do this?
I have a table with fields called fname (First Name) and lname (LastName). I need the userīs email thai is compose from lname and fname:LOWER(LEFT (fname,1) + lname)Is there any difference between creatig this computed column ia a tableor in a view in SQL Server 2000?I can do:1. CREATE TABLE Users(fname varchar(20),lname varchar(20),email as LOWER(LEFT (fname,1) + lname) )Or2. CREATE TABLE Users (fname varchar(20),lname varchar(20))CREATE VIEW Vw_users (fname, Lname ,email)ASSELECT fname, Lname ,LOWER(LEFT (fname,1) + lname) )Is one of them is better?Paulo*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi,
Consider the following example
create table sample
(col1 int,
col2 int ,
col3 AS col1 + col2) PERSISTED NOT NULL)
basically col3 is a computed column. Now when ever a row in col1 or col2 is updated the computed column will reflect the new value. how does this happen in the background. does this use row level triggers or what other mechanism is used to maintain col3 - computed column
Hi,
What is the difference between a computed column and a UDF?
Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager?
Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?
thanks,
Frank
Hi, I have a small problem with my database. I've got following situation: I have a computed column, which value is base on currency rate: rent * rate. Users have to have possibility to change currency rate easily (maybe another table or constant). Is there any way to create formula, which would compute value properly, via constant or something like this? Or the easiest workaround would be load data into dataset (I'm building asp.net application - database will be very small - couple of hundreds of records) and make calculations programmatically?
Przemek
Hi,
I have a table with 4 columns let us say A,B,C,D.
column D is computed column with formula A + '-' + B
Now, i want to add one more condition to the formula which looks like "A + '-' + B + '-' + C".
Please let me know how to do this using T-SQL as i cannot open the table in design mode in production server.
Thanks in Advance!!
i have a table containing the column "current month" and "current day" as smallint which contains the number of months since 1900-01-01 and the day of this month. now i want to trnslate this column in a smalldatetime ( not datetime !) value using a computed column and then create an index on that column.
the formula should be:
dateadd(d,[current day]-1,
dateadd(m,[current month],convert(smalldatetime,'1900-01-01'))
)
trying to create an index on this column results in an error message saying
that the formula is nondeterministic or imprecise
removing the convert statement leaving only the date results in a column of type datetime and creating the index works fine
replacing convert(smalldatetime,'1900-01-01') with a column name which has the type smalldatetime also allows to create an index but thats not what i want to do.
it seems that sql2000 thinks a convert from a string to a date is nondeterministic. Is there any possibility to create a const of type smalldatetime without using convert?
Any idea?
(besides this, datediff(d,'yyyy-mm-dd',anydate) is nondeterministic but datediff(d,dateadd(d,0,'yyyy-mm-dd'),anydate) is deterministic. strange...)
and
I am having a problem with using UDF as part of a temp table computed column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_testGO
CREATE FUNCTION dbo.fn_test( @x int, @y int)
RETURNS INT AS
BEGIN
DECLARE @z INT
SET @z = @x + @y
RETURN @z
END
GO
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.
I do not get this error if I use a regular table.
HELP!
Suppose a table being
Create table myTable (ID int, col1 int, col2 int)
I know how to make a computed column being the sum of other column for the same ID e.g. "computed_column = col1 + col2".
Getting the average would be "computed_column = (col1 + col2)/2" But how to get the Max, Min?
Even "Sum(col1,col2)" or AVG(col1, col2) does not work as the formula for a computed column...
I have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?
the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.
I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"
create view ViewSumReport as
select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted
from dbo.fielddata
GO
I have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
-Jeremy
___________________________
Geek At Large
In SQL Sever, do the size of computed columns gets added to the totalsize of the tables? Does SQL server stores the actual values incomputed columns?Thanks_GJK
View 1 Replies View RelatedI need to create an function similar to the "MATCH" function in Excelthat evaluates a number within a set of numbers and returns whetherthere is a match. I have put the example of what I see in excel in thecheck column. The "0" answer in the result column is in the fourthaccount in the list. Somehow I need to loop through the accountscomparing the result to the total and indicate a match in the checkcolumn. It wouldn't even need to tell me the row number; it could be a0 or 1.account total result check123770266.84124.2112377026131.050 412377026164.38-33.33123770260131.051237702678.7152.3412377167-31.34221.891237716731.34159.211237716738.55152 51237716731.34159.211237716715238.5512377167490.91-300.36123771670190.55123771670190.5512377167-31.3443.341237716731.34-19.341237716738.55-26.551237716731.34-19.3412377167152-14012377167490.91-478.9112377167012123771670121237736347.058412377363131.05012377363-45.38176.4312377363-47.05178.11237736347.0484.0112377363-47.04178.091237736347.058412377363541.11-410.06123773630131.0512377363672.15-541.11237750737.64152.91
View 3 Replies View RelatedI have a checksum calculation as a persisted, indexed computed column on a temporary table that I used to compare against original records to detect changes.
It seems that the update/ insert statements in my procs get out of sync on larger tables (500,000 rows +) with the checksum calculations. The only thing I can think of is that the column calculations are performed asynchronously in relation to the updates/ inserts. This is a problem for me.
Is my assumption correct? If it is, how can I adjust for this, i.e., force the computations to be performed synchronously or wait for the computations to complete before running comparisons?
Does tsql allow sth like
Select col1*col2 as ComputedColumn, ComputedColumn + 2 as NewColumn
From T_Table
THis is possible in Access.
Instead of using Full-Text indices, which I don't like to manage, we've tried to use seperate tables that contain recordID, the word, a count of the word in the parent field and computed column which is the CHECKSUM() of the word column. I indexed the checksum column with a clustered index.
Works great in Query Analyser. But when the ASP page calls it, I get this message:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.
Same for updates and deletes. The question is how should these SET settings be done? Any ideas would be greatly welcomed.
Thanks
Jason
Using SS2K, I'm getting the following error while bulk inserting:Column 'warranty_expiration_date' cannot be modified because it is acomputed column.Here is my bulk insert statement:BULK INSERT dbo.TestDataFROM 'TestData.dat'WITH (CHECK_CONSTRAINTS,FIELDTERMINATOR='|',MAXERRORS = 1,FORMATFILE='TestData.fmt')The computed column is not referenced in the format file and the data filedoes not contain the computed data.Thanks
View 2 Replies View RelatedKairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?
Thank-you very much for your time and expertise,
Kairn"
Hello,
I am new to SSIS.
I am trying to write a simple package to export data from some SQL 2005 tables and into a flat file.
In my data flow, I am using the OLE-DB data source and then the flat file destination.
This all works fine except that I cant get the package to write the columns out in the order I want. Even when I drive the OLE-DB source by a query, they columns are getting written to the flat file in a different order than I want.
How is SSIS determining what order to write the columns in and, more importantly, how can I change it to do it in the order I want?
Please help if you can. As mentioned I am new to SSIS so please give clear+simple answers.
Thanks
Mgale1
Hi all,
I have a table with 3 computed columns, the first two reference a function for there value. The last computed column should be total of the other computed columns, however when trying to write the formula for this column SQL Server rejects it.
Am i correct in thinking it is not possible to reference other computed columns in a computed column's forumla.
My current work around is to call the functions again that the other computed columns use to generate the total, though this seems to me like a performance issue.
Could anyone offer some advise on my situation.
Any input apreciated.
found it
View 3 Replies View RelatedHello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Hey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
Maybe I am missing something very obvious, but I couldn't do it: begin trangocreate table foo (f1 int not null,f2 int not null,f3 as (f1 + f2) not null primary key clustered)gorollback trango This returns:Server: Msg 8183, Level 16, State 1, Line 8Only UNIQUE or PRIMARY KEY constraints are allowed on computed columns.
View 4 Replies View Relatedi have a table that contains 2 columns
A B (B values are only 1 and 2)
-- --
x 1
y 2
z 1
x 2
z 1
j 1
k 2
i want to make a query that will check for dinstinct x what B values it has Ex. It will show result like this
result
A B
-- --
X 1 and 2
Y 2
z 1
K 2
how to make please help
I want to create computed column in table.
Suppose I have three physical column A,B and C
I want to create compute column with computed column.
A+B= X
X+C=Z
Is it possible.
I created a index on a computed column. I did not see any improvementin performance with a join to this column and also my inserts andupdates to this table are failing. Any ideas?Chender
View 2 Replies View Related
Hi,
i have to use a datetime field in all the tables in a database as a updated timestamp.
i.e. : whenever an update happens to a row in a table, this column called LASTUPDATED has to be updated with current date time.Is there any way to implement this without using the trigger ?
can i use COMPUTED column to acheive this ?
Please help me..thnks in advance..
I have a SQL table that maintains a field on the status of a report being completed.
I have in the record the date the report is due (DateDue)
I also have a field called DaysLate which I have set to be a calculated field with formula:
DATEDIFF(dd, DateDue, GETDATE())
Thsi works but when the report is *not* late I'd like this to be null is there I way I can do this conditional calculation in a calculated field?
Regards
Cvive