I would like to know how to write a function that will go through data
in a column and change it. For example, I have a column of ISBN's for
books, and the ISBN's have a period in them randomly distributed. I'd
like to pull the period out.
Any help I can get will be appriciated.
Thanks,
Bill
Hi.Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I writemy own variation of a such a function. If I can appreciate how to dothis,then I hopefully I can write a MEDIAN(COLUMN_NAME) type function or amore general function like a 10% percentile function with syntax suchasPERCENTILE(COLUMN_NAME,25).Regards JC......
View 2 Replies View RelatedI was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
View 9 Replies View RelatedCould someone please help me out? I need to write a sql stored proc to query the following table.My SQL experience is very week. If someone can help me with this, I will be happy to pay you $40 foryour help.
I need the proc to do the following:1.) For every Superintendent in a region, country state and county; return the state name, superintendent name, the county name and and a string which is a comma delimited list of schools they supervise. See the sample output italicised and bold.
So the big challenge here is to also return a string that is a concatenation of school names for a particularSuperintendent in a given state and county. For example: East,Kennedy,Apolo,Morrison.
So basically the stored proc should accept input parameters of the Region, Country, State, and County
Here is the data table:
REGION COUNTRY STATE SUPER_INTENDENT PHONE_NO SCHOOL County
NA USA Texas Mike Andrews 789-3614 East LakeNA USA Texas Mike Andrews 789-3614 Kennedy LakeNA USA Texas Mike Andrews 789-3614 Apolo LakeNA USA Texas Mike Andrews 789-3614 Morrison LakeNA USA Texas Amy Markson 789-2134 Anderson MaylorNA USA Texas Amy Markson 789-2134 Molina MaylorNA USA Texas Amy Markson 789-2134 Polima MaylorNA USA Ohio Terry Ellis 966-8314 Kingston KeelNA USA Ohio Terry Ellis 966-8314 Martin KeelNA USA Ohio Terry Ellis 966-8314 Eastmore KeelNA USA Ohio Terry Ellis 966-8314 Canondale Keel
Here is the sample output the way it will appear on a web form:
State:Texas County:Lake
Mike Andrews East,Kennedy,Apolo,Morrison 789-3614
County:Maylor
Amy Markson789-2134 Anderson,Molina,Polima
State:Ohio County:Keel
Terry Ellis Kingston,Martin,Eastomore,Keel
Hi,
I am working on a project where data is stored remotely in a Postgres database. I need to download some of the postgres data and store it in the SQL Server. The data in PG is in UTF-8. I use another application to write the data to the PG database. To talk to the PG database I am using the npgsql data provider (http://gborg.postgresql.org/project/npgsql/projdisplay.php). The data I am trying to download is arabic.
Everything seems to work fine except when I get the data from the PG DB and write it to SQL Server. I've done lots of debugging and can see that the data is correctly in arabic write until I do the update on the local dataset which saves it in SQL server. For some strange reason it makes the data into jibberish (just question marks).
I am using SQL Server express 2005. If anyone can help me with this I'd be extremely grateful as this has become a big problem and I've tried to find a solution without any success.
Thanks in advance.
Ziad
Does anyone know
if the following sql view is possible to write and execute as a view script?
**********
find employee matching the given UserID************* SELECT * FROM Employees WHERE EmployeeID=@UserID
***********
find client matching the given ClientID********** SELECT *
FROM Clients WHERE ClientID=@ClientID
**********find
all contacts and events associated with ClientID********* SELECT *
FROM Contacts WHERE Contact.ClientID=@ClientIDSELECT *
FROM Events WHERE Event.ClientID=@ClientID
*********select
all audits with Key values matching the primary keys of each client, contact or
event*********SELECT *
FROM Audit Where Key In (Client.ClientID, Contact.ContactID, Event.EventID)
I basically
need to find a employee based on its ID. Then I need to find any records from the table Audit
with Key values matching the given fields in the results of any clients, contacts events that were returned from the previous select statements. Is this possible?
hi,
DataBase i am using is Sql Server6.5.
In a trigger i had written code to transfer updated records from one table to
other table.These updated records needs to be written into a text file.
I had used xp_cmdshell but it is taking time.Is there a way to write data to
flat file.
thanks in advance
karuna
Hi everyone, is there any way to turn off SQL server re-writing the syntax of certain queries? An example would be if in the where you set primary keys equal to foreign keys and then it converts it to inner-joins. Thanks for your help.
Jon
I am having a problem with an ASP program that inserts data into a table on
SQL Server 2000.
No error msg is returned upon submission and the confirmation msg that
displays after the commit command is sent to the server displays, but when
we go to the DB, the data sent isn't there. This is an occassional
occurance and usually the data is there, just some times, it isn't. Other
forms function just fine, using the *exact* same file to perform the submit
function (all the forms "include" the same submit page). The only
difference we can find is a trigger on the table having problems which
executes upon update, capturing the information about who updated the record
when. From what we can see, this is the only programmatic difference. The
other thought tickling our minds was the possiblity of a simultaneous
submission, since all the users submit with the same db user name via the
form, if user 1's data gets written but not yet commited, user 2's data is
submitted, then the commit transaction is submitted by user 1 as the program
steps run in sequence, would the commit by user one cause eiither of the
records inserted but not commited to be lost? If so, why wouldn't that be causing problems on other forms ...
Hello all,
Recently I changed over a ASP script from our old Access 97 database to our new SQL database. When I changed it over, some of my SQL pulls on my Active Server Page started to give me erros. One of them is the function
date(). When I used it pulling from Access like this :
strSQLQ = "SELECT * FROM cocoitem WHERE CustNum = '" & strcustnum & "' AND stat = 'C' AND [due-date] > DateAdd('yyyy', -1, Date()) Order By [cust-po], [due-date] ASC ;"
Then it worked fine. When I redirected the ASP to the new SQL server I recieved an error like this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
'Date' is not a recognized function name.
/scripts/order/shippingstatsclose.asp, line 45
So my question is, what is the SQL server equivalent of the function Date()?
Hi,
I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0.
Please refer some good tutorial for it.
Regards,Imran Ghani
I have a project were I will have it so that users can sign in and change information on an SQL server. The catch is that this site will be from a different domain name and from a different hosting company then where the SQL database is located. Sorry if this is a dumb question but how can I utilize asp.net to change and view an SQL database that is located else-where.
For example: a user logs into www.something.com and he/she can view and edit SQL tables from www.somethingelse.com's database.
Thanks in advance.
Dear All,Please suggest some of the best practices for writing SQL serverstored procedures?I'm writing a business function (stored procedure), which callsmany-stored procedure one after another.I want this to be best optimized, so that speed can be very good.Suggestion in this regard will be appreciated.Thanks in advance,T.S.Negi
View 1 Replies View RelatedHi I have a problem which I’m not sure how to resolve!
I have a aspx with two drop down list;
1st one has (annual salary, daily salary, hourly rate)
2nd one has ( 0-4999, 5000-9999......)
The second one is generated by the value selected in the first one.
I have stored the values in a table (as nvarchar) and used sqldatasource to run a query, which matches the entry in the first box and fill the second drop down list accordingly.
How ever I have a problem, when I want some one to search for example; an average salary of 5000-9999, it should output entry's that have a similar daily rate, and hourly rate...
But I’m not sure how I can accomplish this, does any one have any ideas!
Many thanks
Hi,the Soundex search words that sounds similar.Does MS SQL Server has some function to make some intuitive search?For example, for search term database, it should return rows that contains: "database" word, but also rows that contains "Oracle", "MySQL", "MS SQL" etc. terms.
View 1 Replies View RelatedCan someone tell me if this is a SQL Server bug? I tried this in both
version 7 and 2000, the results are the same.
DECLARE @timeA DATETIME
DECLARE @timeB DATETIME
DECLARE @msDiff INT
SET @timeA = GETDATE()
SET @msDiff = 0
WHILE @msDiff <= 10
BEGIN
SET @timeB = DATEADD(ms,@msDiff,@timeA)
PRINT 'If adding ' + CONVERT(VARCHAR,@msDiff) + ' milliseconds to Time
B, then Time B is ' + CONVERT(VARCHAR,DATEDIFF(ms,@timeA,@timeB)) + '
millisecond greater than Time A'
SET @msDiff = @msDiff + 1
END
This seems like a serious bug if an application depends heavily on
milliseconds comparison.
Thanks,
Aiden
I am sorry to continue bothering this forum with the
continuation of this question but here it is. And thank you to Craig for
giving me the equivalent of the function Date() in SQL. Now when I pull from
the SQl Server with the old ASP pull with this statement using GETDATE()
strSQLQuery1 = "SELECT * FROM cocoitem WHERE CustNum = '" & strcustnum & "' AND (stat = 'O' OR stat = 'F') AND [due-date] > DateAdd('yyyy', -1, GETDATE()) Order By [cust-item], [due-date] ASC;"
I get this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid parameter 1 specified for dateadd.
/scripts/order/shippingstatsopen.asp, line 28
So I guess I need to also know the equivalent of DateAdd . Also, does anyone
know of a Access Function to Sql 7 function comparison chart so I can write
for the new database comprehendingly?
Thank you very much for your help in advance.
Hi,
I am new to this, SQL Server. I hv worked in Oracle. Now I am learning 'SQL Server'. In Oracle, it has features like Packages and functions (PL/SQL), like that in SQL Server, is there any facility available?.
thanks in advance.
srini
Do we have any builtin functions in SQL Server for certain financial calculations. For eg., like the PV, FV functions in VB
If not, how else do we achieve this thru' a SQL Server stored procedure?
Thanks in advance,
Siva
Hi,
I am still learning the bells and whistles of SQL Server and was wondering if I can find out the query that caused my trigger to fire, so that I can log this in another audit table.
I have an If Update ( My_Column ) trigger set up, where once an update happens to My_Column much information from the updated row along with , Host_Name and App_Name is sent. I also want to send the exact query used to update it, any ideas?
Any comments, suggestions will be greatly appreciated.
Thanks,
Kartik
I'm moving some queries out of an Access front end and creating views out ofthem in SQL Server 2005 express. In some of the numeric fields, I use nzquite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.Is there anything equivalent to this in SQL Server? Right now I'm usingCASE WHEN ... but it seems like an awful lot of script to write just toreplace null with a zero.Any help would be greatly appreciated.Thanks!
View 16 Replies View RelatedHi,Do any versions of SQL Server support the following functions, asthey appear in the Oracle Database:-1) XMLElement2) XMLAttributes3) XMLForestThanks in Advance for your replyByeAmardeep Verma
View 4 Replies View RelatedHi everybody,
Does anyone have financial functions to be run in SQL Server 2000? For example, future value, interest rate, payments, and so on. Or where can I find them on Internet?
Thanks in advance
Jaime
I'm upsizing MS-Access to SQL Server 2005.
I need to convert the following functions:
TRANSFORM
PIVOT
FORMAT
MID
Are there any similar functions in SQL Server?
Also I have a query as follows:
SELECT Mid$([AccountNumber],3,8) AS [Account#], Format([checkamount]*100,"000000000") AS Amount, IIf(IsNull([statusdate])," ",Format([statusdate],"yyyymmdd")) AS [Date]
FROM tblResult;
How do I convert this in SQL Server query?
Hi Everybody,
I am trying to update a column Percentage in a table named Critical Doctors with a column named
PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from
tblPercent.
I am getting an error message for the following query.
Have Two tables
1.CriticalDoctors
2.tblPercent
update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=
, >, >= or when the subquery is used as an expression.
The statement has been terminated.
Pls give me reply on how to write a stored procedure so that I can equate the percentage column
with one value and also check the condition with one value.
Thanking you guys in advance.
madhav
Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
I have an asp drive web page that writes a row to a table on sqlserver 2000. The web site is set to use windows authenication and thesql server is set to use windows authentication.This process works fine on windows xp sp 1 machines but on win2k sp4machines logged in as the same user i get the errorAn error occurred making the change -2147217843 Error connection toSQL Server: [Microsoft][ODBC SQL Server Driver][SQL Server]Loginfailed for user '(null)'. Reason: Not associated with a trusted SQLServer connection.can anyone explain why win2k client would have this issue and notwinxp clients?Glenn
View 1 Replies View Relatedhai,
I have 3 fields in my table say (F1, F2, F3). I want to get the max value out of the three fields for each row. I can create a user-defined function which accepts 3 arguments and then return the max value if i am using SQL Server 2000. But now i am using only SQL Server 7.0 (it does not support user-defined functions :confused: )
So any one could kindly let me know how could i do it in SQL Server 7.0
Thnks in advance
Hi,
I saw in some websites that there are functions freeze and thaw in SQL server.I want to freeze the SQL server for some time and then use the thaw to unfreeze.I want to know how it could be done in SQL server 2005
Thanks in Advance
Hi all!!Does anybody know how I can create a function in SQL 7.0?? I have tocreate functions that return a value that can be used in a selectstatement. I think SQL Server version 7.0 doesn't support CREATEFUNCTION, does it?Ex:Select MyFunction(Parameter)From MyTableThanks a lot,
View 2 Replies View RelatedHi
I am trying to build a tree similar to the one in SQL Server Management Studio for the system functions in the SQL language.
I would like to group them by type (e.g. string functions) and display information about the parameters and return types, etc.
Is there a way to get these programmatically? I would like to avoid typing them out by hand.
Thanks
Chris
Hi ,
I have a question about calling functions in SQl Server 2005.
Let's say that I have created as a dbo a function called Calculations.
If I want to call it from T-SQL I will write Select dbo.Calculations (arguments if any) etc.
My question is If I can skip the "dbo" part. Call the function without using the dbo .
Can I do that ?
Should I create the function as supervisor ? Does Sql Server has a property or something which will allow me to call the function without using the "dbo." ?
Thank you
zkar
writing a cross join query with one table:
Cities(City_name, X_coordinate, Y_coordinate)
the result should be all combinations without reverse column returns
SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???
for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)