Computed Columns And Constant
Aug 22, 2006
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
View 1 Replies
ADVERTISEMENT
Oct 4, 2007
Is there a perf difference between:
create function dbo.zzz
returns uniqueidentifier
return '0000-0000-0000-00000000'
select dbo.zzz
vs.
se;ect '0000-0000-0000-00000000'
Thanks,
J
View 4 Replies
View Related
Jul 4, 2007
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
View 3 Replies
View Related
May 20, 2008
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 Related
Oct 31, 2003
I 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
View 6 Replies
View Related
Nov 6, 2006
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?
View 3 Replies
View Related
Jul 20, 2005
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 Related
Sep 25, 2006
Hi,
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
View 9 Replies
View Related
Jul 8, 2004
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
View 1 Replies
View Related
Oct 8, 2007
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!!
View 12 Replies
View Related
Nov 13, 2001
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
View 1 Replies
View Related
Jun 25, 2004
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!
View 5 Replies
View Related
Jul 25, 2012
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...
View 9 Replies
View Related
Apr 1, 2015
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
View 2 Replies
View Related
Mar 19, 2008
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
View 1 Replies
View Related
Jul 23, 2005
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 Related
May 26, 2006
I 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 Related
Mar 19, 2008
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?
View 4 Replies
View Related
Nov 12, 2007
Does tsql allow sth like
Select col1*col2 as ComputedColumn, ComputedColumn + 2 as NewColumn
From T_Table
THis is possible in Access.
View 5 Replies
View Related
Aug 30, 2007
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
View 3 Replies
View Related
Oct 31, 2002
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
View 3 Replies
View Related
Jul 20, 2005
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 Related
Aug 2, 2006
Hi All,
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
Thanks a lot in advance!
Danny
View 2 Replies
View Related
Jul 15, 2005
In an effort to automate a process, I am trying to populate a csv textfile with data from a SQL Server 2000 database that will be imported by a proprietary database; however, not all of the data required to go into the textfile is available in the source db. Fortunately, the data I'm needing has constant values for the fields that I want to populate, i.e Lab Name. Whereas, the Destination database will receive data from other labs but not via this source.
Is it possible to use a constant rather than a db field within the SQL query to populate one of the textfile fields. (I placed "LABNAME" in where I would like it to go) A portion of my present SQL statement is:
Select LEFT([SAMPNAME], 4) AS IUNUM, RIGHT(LEFT([SAMPNAME], 8), 3) AS SITENUM, convert(varchar,[SAMPDATE], 112) as SAMPDATE, [BDL] AS "SAMPNUM", [ANALYTE], (CASE [STARTDATE]-[SAMPDATE] WHEN 0 THEN '2' ELSE '1' END) AS METHOD, convert(varchar,[STARTDATE], 112) as STARTDATE, [FINAL], [BDL], "LABNAME", [NOTES1], [SAMPLER], [ORDNO], [UNITS]
From [CUSTOMER]
As you can see, I have already done a lot of formatting within the statement but would appreciate someone's SQL expertise to tell me if using a constant is possible or not.
Thanks,
Al
View 4 Replies
View Related
Jan 28, 2008
Hi,
I want to bcp data file generated by other system into a table maintened by our system. While generating the bcp data file they leave data value for id column empty (because they are not aware of the value in our system).
I want to specify a constant value for id column during bcp. Is there any way i can specify a constant value in bulk insert command? Can i specify it in format file?
Although i can read the bcp file and do transformation in front end (C#) it is time consuming. I can bcp data and then fire a update statement to update id column, this is also found to be time consuming task.
Any help would be appriciated.
Many thanks,
Rishi
When solution is simple, God is answering….
View 10 Replies
View Related
Aug 24, 2005
We have a database that on a daily basis suffers from poor performance.
View 4 Replies
View Related
May 30, 2000
How can I use constant path define in SP,
The below is my conde which gets me error, any1 knows about that ?
Thanks
Declare path nvarchar(500)
@Path = 'H:HN2000AMASTEREPPma01.mdb'
EXEC sp_addlinkedserver
@server = 'PUA01', --Server Name
@provider = 'Microsoft.Jet.OLEDB.4.0', --Provider Name
@srvproduct = 'OLE DB Provider for Jet',--Product
@datasrc = @Path
View 1 Replies
View Related
Aug 10, 2006
hello
I want to get output like this if i give the strings:
output:
====
25813/3
25813/115
25813/208
here "25813/" must be constant and remaining part must come in orderly way.
but in MS SQL the first inserted value is getting first i.e if we give value 25813/115 is getting first.
I will show in example:
create table number(num varchar(20))
insert into number values('25818/115')
insert into number values('25818/208')
insert into number values('25818/3')
The output coming is:
color
1 25818/115
2 25818/208
3 25818/3
BUT I REQUIRE THE OUTPUT AS:
color
1 25818/3
2 25818/115
3 25818/208
since 3 comes first than others.
If anyone knows please tell.
View 3 Replies
View Related
Feb 15, 2005
Or, is the only way to use "declare" and "set" only ?
View 1 Replies
View Related
Apr 15, 2007
I know this may be in the wrong forum, but I have a question. I am working on a system for a video store. I have rentals and sales for videos. I have set the ID fields for rentals and sales to be autonumbers and increment by 1, but I would like to have an S in front of sales IDs and R in front of rental IDs at all times. It is kind of like a constant in all autonumber ID fields. I want the S and R to be in every ID field, but the number to change. Thanks.
View 2 Replies
View Related
Feb 9, 2007
Hi, thanks for taking a minute to help me out.
I've got a bunch of reports that have very specific formatting requirements (not mine, industry standards). Basically I need to have different headers and footers on each page, so I don't think I can use the report's headers and footers. Instead I'd like to use each table's (one for each page) set of headers and footers. This is fine for getting the data in there, but I'd like the table footer to be forced to be at the bottom of the page because having a footer halfway down just looks bad.
To summarize, is it possible to have a table footer be in the same place on a page (almost) regardless of how many rows there are returned.
pg1 pg2
header header
data data
data
footer footer
I'd prefer not to split each page into a different report and use those footers, so if there are any other hacks out there I'd really appreciate it
Thanks, Jeff
View 1 Replies
View Related
Sep 5, 2007
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.
View 1 Replies
View Related
Jan 29, 2008
I want to run some sql code from Query Analyzer. The sql code uses parameters.How do I define the parameter before setting it to a value? I don't want to hardcode the input parameteres.
Ex:
create @Prim -- This is the line I need help on. Instead of "create", what is -- the proper way of setting this value ?
set @Prime=13
SELECT Prime.Quaternion, Gaussian.RegionId, Laplace.Primer,
View 1 Replies
View Related
