I need to find the highest of two numbers and lowest of the two numbers.
I read on the web that Greatest and least functions in sql can help me do that.
but when i use
greatest in my query it gives me this error
GREATEST is not a recognized built-in function name.
all I have is
declare @first int, @second int
Can somebody point me in the right direction.
Thanks so much
Hi, I need some pointers, I am trying to create a SQL function which will check to see if a column in a table will allow null values to be inserted. I've tried searching the net but to no avail. Kind Regards
Hey guys, I have been trying to get a function to work but have been having a hard time. The tables that are used are a translation and validation tables. But the translation can be from different tables. What i would like the function to do is take in a 'description of a type' and a language code and send back the translated version. The issue is that the table is dynamic and there is my issue with my function.
ALTER FUNCTION [dbo].[Trans] ( @Trans_description AS NVarchar(200), @languageid AS nvarchar(10) ) RETURNS nvarchar(50) AS BEGIN DECLARE @return AS nvarchar(50), @TABLENAME AS nvarchar(50), @sql AS nvarchar(100) set @TableName = (select top(1) listtable from validationType where validationtypeDesc = @Trans_description)
SET @return = (SELECT TOP (1) ValidationDesc FROM [translationvalidationlist] WHERE validationpartyid IN (SELECT validationpartyid FROM @TableName WHERE validationtypePartyid IN (select validationtypepartyid FROM validationtype WHERE validationtypeDesc = @Trans_description ))AND LANGUAGEID = @languageid)
Is there anyway to do a @return = exec(@sql) of course sql would be the current @return. Right now i get the error Must declare the table variable "@TableName".
I am using the LIKE function combined with a CASE WHEN to change a long list of words, but the list is too long... Is there any posibility to insert more than one argument into one like function...? Any other good ideas? Below an example of the code I am using..
Thanks in advance, Aldo.
Code Snippet Case WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument01%' THEN 'Result' WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument02%' THEN 'Result' WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument03%' THEN 'Result' WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument04%' THEN 'Result' ELSE '' END AS 'Result'
Hi all. I'm looking for assistance to get the greatest value from 3 ormore different columns. I'm assuming that the best way is to put thecolumns into a temp table and use 'max' function to return the greatestvalue but don't know how to code it. Thanks in advance.*** Sent via Developersdex http://www.developersdex.com ***
Hi all. I'm looking for assistance to get the greatest value from 3 or more different columns. I'm assuming that the best way is to put the columns into a temp table and use 'max' function to return the greatest value but don't know how to code it. Thanks in advance.
Here is my SQL statementSELECT a.name, b.meterno, c.meterstatus, d.accountno, d.creation_dateFROM fmdata.location a, cisdata.service_meters b, cisdata.meter_master c,cisdata.Account_Master dWHERE a.location_id = b.location_id AND b.meterno IS NOT NULL AND b.meterno= c.meterno AND a.location_id = d.location_idORDER by meterstatus, nameHere is my resultsNAME METERNO METERSTATUS ACCOUNTNO CREATION_DATE01010004 20512944 Active 101000402 7-Feb-200201010004 20512944 Active 101000401 8-May-199701010005 25917180 Active 101000501 27-May-200301011001 13646231 Active 101100102 17-Mar-199901011002 18389246 Active 101100201 29-Apr-199401011003 84473845 Active 101100301 24-Apr-199701012002 47511850 Active 101200202 26-Jan-199601013001 35653963 Active 101300101 28-Feb-1979If you notice I'm getting two 01010004 under the NAME column. I would likethe SQL statement to select the greatest CREATION_DATE if there areduplicate NAME'S. So in this case, it would select the top 01010004 since ithas the greatest CREATION_DATE.thanks in advancebart
I have table1 and table2.In table1 I have a column of numbers, numbers1.In table2 I have a column of numbers, numbers2.I'd like to select the highest number represented in either column.Example:table1:column1--------------345565643656555676table2:column2--------------3456564556456456456456The number I would want would be 56456 since it's the largest numberout of all combined.How can I get that number with one select statement?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
Hello, Here's one way to sum only the top 5 (greatest 5) values per group. I assume there is a table called IdValues that contains two columns: id int, value int.
declare @lastId int declare @value int declare @count int declare @idList varchar(5000) declare @valuelist varchar(5000) set @count=0 set @lastId = -1 set @value = 0 set @idList='' set @valuelist=''
select @count=(case when @lastId<>id then 1 else @count+1 end), @value=(case when @lastId<>id then value when @count<=5 then @value+value else @value end), @idList=(case when @lastId<>id then cast(id as varchar)+','+@idList else @idList end), @valuelist=(case when @lastId<>id then cast(@value as varchar)+','+@valuelist else cast(@value as varchar)+','+right(@valuelist,len(@valuelist)-charindex(',',@valuelist)) end), @lastId=id from IdValues order by id desc, value desc
It's a funny approach. I'd be interested to see a better method. In MySQL it is possible to do this much better and have it produce an actual resultset (since MySQL allows you to assign variables and product a resultset in the same query).
I also noticed something interesting. If you do any operation on the order-by columns, the query doesn't work. For example, if I do: order by id+0 desc, value desc something funny happens and you only get one id and one value in the list variables. Maybe someone else who actually some idea of how SQL Server works can explain this.
What happens when autoincrement reaches its last value? Does my application crash? Will autoincrement wrap?
Let us assume I figure that my database table will be much smaller than 2^16 records. Since it will be nowhere near the limit, I select smallint (2 bytes wide) as my ID field. I set the seed value to -32,767. I set the increment value to 1. Over the course of actual useage, many records are added and deleted many times. Although I correctly anticipated that the number of records in my database is far less than 65,535, autoincrement has reached 65,535. What will happen next? Ideally I would like autoincrement to wrap and take the next unused value. But does it do that? Or does it freeze at the highest value? What happens?
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library. I get this error when i used into storeprocedure : ROW_NUMBER() function is not recognized in store procedure. i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library. I need to add that function into MS SQL SERVER 2005 database library. Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
Hello Friends, I am not sure if this is the right place to post this question but if not please suggest me when it can be posted. I have been thinking of writing Stored procs in SQL CLR/ changing all of my Stored Procs to SQL CLR. Is there any thing that I need to keep in mind about the size of the sotred proc (like calculation intensive) before I do that? I mean can even change a TSQL stored proc which is relatively small Stored Proc, that simply said Select * from Customers to SQL CLR? or the SQL CLR does only useful/makes difference with calculation intensive stored procs and big stored procs? When I talked to our Architects they said every small sized stored proc can be written using SQL CLR and more over forget about the classic TSQL stored procs and get used to write SQL CLR when ever writing any database related stuff. And also there are so many articles that discussed about the advantages of SQL CLR over the TSQL but I would appreciate if some one could put few bulletted points why do you think SQL CLR is more powerful. Please advise.Thanks in advance,-L
Hi I am creating some dynamic sql by passing variouse parametrs to my Stored Procedure. One of my Parameters is string has lots of values seperated by a comma to help build an 'IN' statement. SET @SQL = 'SELECT * FROM areas'SET @p1 = '10,20'If @p1 IS NOT NULLBEGINSET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'END The above query runs perfecly well in Query Analyser, however when I put it into my ASP.NET application I get an error of "Error converting data type varchar to numeric." So I think I need to do some sort of casting or Converting but im not sure how to do it. Or do I need to use a INSTRING? I did manage to work out a method by using the follwoing SELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0 But I cant seem to convert the above line into coherent dynamic statement. My feeble attempt is below but I keep getting errors SET @sql = @sql + ' WHERE PATINDEX(''%,'' + CAST(ArType AS VARCHAR) + '',%'','','' + @p1 + '','') > 0' IM strugging to understand all the '''. My TSQL is pretty basic, any help would be much appreciated Many thanks in advance