Beginner Question: Find Or Create Function
Jan 18, 2007
I have a table with two columns: siteID (int primary key) and siteName
(varchar(50) unique constraint).
I am completely new to databases and UDFs and would like to write a
function that looks for a particular siteName and returns the siteID.
If the siteName is not found then it would create a record and return
that record's siteID.
I am pretty sure there is a standard way of doing it and have been
looking for examples, but have yet to find anything on Google.
If anyone could point me in the right direction I would be very
grateful - I am still looking and will reply if I find anything.
Many thanks
Jon
View 6 Replies
ADVERTISEMENT
Jan 19, 2008
I'm an Absolute beginner to SQL
I have a table of baseballTeams with columns baseballTeamName and a baseballTeamID
A have another table (baseballGames) which consists of baseball games which includes a homeTeamID and a visitingTeamID.
Both of these columns are linked to the baseballTeamID from the baseballTeams table
I have been successful at joining the two tables and Selecting the baseballTeamName from the baseballTeams table for either the hometeam or
visiting team as shown below:
SELECT gameID, homeTeamID, baseballTeamID,baseballTeamName
FROM baseballGames, baseballTeams
WHERE baseballGames.homeTeamID=baseballTeams.baseballTeamID
...but I cannot figure out how to select BOTH the homeTeam name and the visitingTeam name from the baseballTeam table
Any help would be greatly appreciated
Thanks in advance
Larry
View 6 Replies
View Related
Oct 27, 2006
I've learn DotNetNuke.I don't know how to create passID and userID for database sqlserver2005 express.someone help me.Thanks!thanks!
View 4 Replies
View Related
Apr 3, 2007
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
View 3 Replies
View Related
Oct 18, 2006
Hi,
I found rtrim and ltrim functions, but I didn't find trim, so should I use rtrim(ltrim(' a ' ))? Am I right?
cheers,
Alessandro Camargo
View 5 Replies
View Related
Oct 14, 2007
Hi, I have a stored procedure that calls a scalar function. It works fine on my development database, but on my production database, I get this error when trying to execute the procedure: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MyFunction", or the name is ambiguous. However, I am able to execute the function outside of the procedure like this: select dbo.MyFunction(0, 0) Anyone have idea? I can't figure it out.
View 6 Replies
View Related
Apr 17, 2014
I'm looking for a way to pull off a complex find+replace within some code, as follows:
@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'
We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...
Rules:
1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).
2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).
View 2 Replies
View Related
Mar 3, 2008
Dear all hi,
I would like to create a function that will return to me the width of the column of the table without giving as a parameter the table name. I need it, as I want to exceed my data in this column to the column length. I want to fill the column data with trailing blanks until I reach the column width. The function len returns the length of the data in the column.
I know that If I have the table name I can find the column width through the system tables. I don€™t want this.
Sincerely,
Hellen
View 10 Replies
View Related
Jul 23, 2005
Hi,I m searching for some Script / Function ... to find difference in datab/w 2 similar tables (exactly same fields structure) in sql 2000.plz update me asap !thanks in advance !
View 2 Replies
View Related
Oct 15, 2007
hi,
i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files.
the file are named as:
ff_inbound_20071008_1.csv
ff_inbound_20071008_2.csv
where for file ff_inbound_20071008_1.csv:
ff => flat file
inbound => dataset of this csv
20071008 => date
1=> file count
having in mind that they are store in the variable as a full path:
z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv
i need to extract the dataset and the date for each file.
how can i extract these terms from the file name?
many thanks,
nicolas
View 4 Replies
View Related
Nov 3, 2015
I'm trying to run the query given below but iFile.Pathname is underlined in red.
Query: SELECT top 1 iFile.Pathname AS 'PathName' FROM Videos
Error: Cannot find either column "iFile" or the user-defined function or aggregate "iFile.Pathname", or the name is ambiguous.
Do I need to run a procedure?
View 9 Replies
View Related
May 4, 2006
Hello i have this query and I havent been able to solve it
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_NIVELCOMPETENCIACARGO", or the name is ambiguous.
I have tried with dbo and withou dbo, specifyng columns inside it,
Thanks
select a.nmempresa,a.nmtipocompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,
b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia,
g.DSOPCIONESCALA, g.PESO
from
tgsc_tiposcompetencias a, TGSC_TIPOSCOMPETENCIASxcargo b, tgsc_cargos c, tgsc_ciclos d,
tgsc_competenciasxcargo e, tgsc_competencias f,
(select
dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia)) as g
where a.nmempresa=72 and a.nmciclo=9
and b.nmtipocompetencia=a.nmtipocompetencia
and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9
and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9
and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72
and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia
and f.nmtipocompetencia=b.nmtipocompetencia
View 7 Replies
View Related
Apr 28, 2015
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
      RETURNS date
AS
BEGIN;
 DECLARE @Result date;
 SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
   RETURN @Result;
END;
View 11 Replies
View Related
Feb 18, 2008
Is there a way to find out what the datatypes of a temp table are?
Example:
select cust_code, cust_name, cust_state
into #customers
where cust_state = 'TX'
I would like to know what datatypes SQL used when creating #customers.
Thank you for all the help.
View 2 Replies
View Related
Mar 21, 2006
Hello!How can I make this function in MS SQL:CREATE FUNCTION id_name() RETURNS INTEGER AS 'SELECT MAX(ID)+1 FROM Test;'Thanks!
View 2 Replies
View Related
Mar 15, 2008
I have the following function that I was able to put together with the help of the following article http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx but I'm having some problems with it any help would be greatly appreciated.
USE database1
GO
CREATE FUNCTION dbo.Concatdwg_Seq (@prt_Mark CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output =CASE @Output
WHEN '' THEN dwg_Seq
ELSE @Output + ', ' + dwg_Seq
END
FROM dbo.Un_Combined
WHERE prt_Mark = @prt_Mark
ORDER BY dwg_Seq
RETURN @Output
END
GO
prt_Mark | dwg_Seq
12 | 12a,23b
25c | 1b,5e,8d,100as
I get the following errors
Msg 325, Level 15, State 1, Line 2
Incorrect syntax near 'FUNCTION'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@prt_Mark".
Msg 178, Level 15, State 1, Line 17
A RETURN statement with a return value cannot be used in this context.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'To'.
View 5 Replies
View Related
Jul 26, 2007
I want to divide day of month as
example: month is July
week1: 02-07
week2: 09-14
week3: 16-21
week4: 23-28
week5: 30-31
--========Sunday is not including
example: month is August:
week1: 01-04
week2: 06-11
week3: 13-18
week4: 20-25
week5: 27-31
but if December:week1: 01
week2: 03-08
week3: 10-15
week4: 17-22
week5: 24-29week6:31 please help me!thanks so much!
View 1 Replies
View Related
Aug 5, 2002
how do create function in sql server 7.0
View 1 Replies
View Related
Mar 28, 2002
Hi All,
I'm running SQL Server 2002 and trying to create a User Defined Function. However, everytime I try to save the script I get Error 170 Incorrect Syntax near 'FUNCTION'.
This happens if I create the Function from Code or use the Enterprise Manager. I'm logged in with 'sa' privs, so I don't think it's a privilege issue. I'm well confused.
Anyone help?
TIA
William.
View 1 Replies
View Related
Dec 5, 2007
Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said
'CREATE VIEW' must be the first statement in a query batch.
I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece.
CREATE Function GetCommonFailurs()
AS
BEGIN
IF OBJECT_ID(N'CommonFailures') IS NOT NULL
DROP VIEW CommonFailures
DECLARE @Run1Result as char(4), @Run2Result as char(4);
SET @Run1Result='Fail';
SET @Run2Result='Fail';
CREATE VIEW CommonFailures
AS
SELECT Run1Failures.RunID as Run1ID,
Run2Failures.RunID as Run2ID,
@Run1Result as 'Run1Result',
@Run2Result as 'Run2Result',
Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath,
Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath,
Run1Failures.SDET as SDET,
Run1Failures.CommandLine as CommandLine,
Run1Failures.OutputFilePath as OutputFilePath
FROM Run1Failures
INNER JOIN Run2Failures
ON Run1Failures.TestID = Run2Failures.TestID
END
View 4 Replies
View Related
Sep 20, 2007
How do I give a Windows group complete rights (including create) to allstored procedures and user defined functions without giving them dbo accessin SQL Server 2005? If I have to I can do it from the Management Console,but I would also like to know the commands.ThanksMatthew WellsJoin Bytes!
View 1 Replies
View Related
Aug 1, 2007
Greetings.
I'm having trouble creating a recursive function in T-SQL (SQL Server 2000).
I've got a table that has an ID column and a ParentID column. Each row can have a value in the ParentID column that references the ID column of another record - I'll call such rows "child records". I'll cal the row referenced by the ParentID the "parent record".
Each child record can itself have another child record.
I need a function that will take an ID column value as a parameter, and walk up the chain of parent records until I get the first record in the series and return that record's ID value. I'll call that record the "UrParent record".
I'm trygin to create a recursive function called ufunc_ST_GetUrParentCertNum. In the function, there is of course a recursive call to itself - GetUrParentCertNum. However, when I try to run the CREATE FUNCTION script, I get the error:
Server: Msg 195, Level 15, State 10, Procedure ufunc_ST_GetUrParentCertNum, Line 26
'ufunc_ST_GetUrParentCertNum' is not a recognized function name.
I tried the same thing with a Stored Procedure, and that worked fine. However, I really want this to work as a function.
Does anyone have advice on how I can achieve this?
Thanks in advance.
- will f
View 3 Replies
View Related
Feb 24, 2008
Code Snippet
Declare @DBName as varchar(100)
Declare @Query as varchar(8000)
SELECT @DBName = AccountDBName FROM Config Where SomeID=SomeValue
Set @Query ='
SELECT
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice),
a.MoeenName_L1
FROM
ReciptItems LEFT OUTER JOIN
' + @DBName + '.dbo.Categories AS a
ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
a.MoeenName_L1'
Exec (@Query)
View 10 Replies
View Related
Nov 14, 2005
In the table, there is a record which has several field. every month, the function will create a same record. that means, the first month, one record. the secord month, two reocrds, ..... for a years. will have same 12 record. so what function can do this? Thanks.
View 2 Replies
View Related
Jan 22, 2005
Hi,
i created a cube that has 2 measures. I created the measures by selecting the columns from my fact table, but the function that applied in the measures was the sum function. I need to apply the count function in my measure. How can i do that?
Thanks in advance.
View 2 Replies
View Related
Aug 24, 2004
I created a function that will return
from OpenDataSource('.....') tablename
where ... is fully populated.
However, I can't figure out how to use it?
For example
select functiona (parameter) as data_src
this returns the "from" statement above
I then try to run
select * data_src
So how do I reference the contents of data_src in the select?
Thanks for any help
View 1 Replies
View Related
Jan 7, 2007
Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:create function DBO.NumValue
-- This function will get the numbers from the front of a field
-- and return the value of those numbers in a numeric data type
(@mNumInput as charvar(100))
RETURNSnumeric
AS
BEGIN
declare @x as tinyint
declare @x1 as tinyint
SET @x = 1
WHILE IsNumeric(SubString(@mNumInput, @x, 1))
BEGIN
SET @x1 = @x
SET @x = @x + 1
CONTINUE
END
If @x1 > 0
BEGIN
RETURN CAST(LEFT(@mNumInput, @x1), Numeric
END
END
Here are the two error messages I'm getting from this function.Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 12
Incorrect syntax near the keyword 'BEGIN'.
Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 20
Incorrect syntax near the keyword 'END'. I have no idea what these two error messages mean.
TIA,
View 7 Replies
View Related
Mar 3, 2015
I have select to split FullName on LastName and FirstName columnselect
Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1
Is it possible to create function based on that select? If yes. How it to do?
View 12 Replies
View Related
Jul 23, 2005
I keep getting an error message "incorrect syntax near keyword case"when trying to run this:USE DEDUPEGOCREATE FUNCTION fnCleanString(@mString varchar (255))RETURNS varchar(255)ASBEGINDECLARE@mChar char(1),@msTemp varchar(255),@miLen int,@i int,@iAsc intBEGINset @mChar = ''set @msTemp = ''set @miLen = Len(@mString)set @i = 1while @i <= @miLenbeginset @mChar = substring(@mString,@i,1)set @iAsc = Ascii(@mChar)casewhen @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mCharwhen iAsc >= 65 And iAsc <= 90 Then set @mChar = @mCharwhen iAsc >= 49 And iAsc <= 57 Then set @mChar = @mCharelse @mChar = ""endset @msTemp = @msTemp & @mCharset @i = @i + 1endENDRETURN @msTempENDCan anybody point out what I'm doing wrong?Thanks.Randy
View 3 Replies
View Related
Feb 13, 2008
Greetings,
I need to create a function that is available across all databases. This function is for exchange rate conversions and will be used extensively. I'd prefer not having to call it by it's full four-part name and just make it available everywhere on the server.
Is there a way to create such a function? Where is it stored?
Rob
View 5 Replies
View Related
Sep 12, 2007
Hello. I'm a real newbie - using Access 2003 front end and connecting to SQL Server 2005 ODBC.
I'm having trouble accessing functions through access. I've built the following function:
CREATE FUNCTION fnSTR_LEASESTATUS(@TRS nvarchar(12))
RETURNS TABLE
AS
RETURN
(
SELECT dbo.tblTRACT.STR, dbo.tblTRACT.[TRACT_#], dbo.tblMIN_OWNERS.Min_Owner_Name AS [OWNER OF RECORD], dbo.tblLEASE_TRACTS.LOC_ID, dbo.tblLOCATION.LPR_No, dbo.tblLOCATION.Lease_ID, dbo.tblLEASE_LOG.Date_Mailed, dbo.tblLEASE_LOG.Scan_Lease_Received, dbo.tblLEASE_LOG.Orig_Lease_Recd, dbo.tblLPR_INVOICES.Invoice_No, dbo.tblLPR_PAY.CHECK_DRAFT_No, dbo.tblLESSORS.Name AS [Lease Name]
FROM dbo.tblTRACT LEFT JOIN ((dbo.tblMIN_OWNERS RIGHT JOIN dbo.tblTRACT_OWNER ON dbo.tblMIN_OWNERS.Min_Owner_ID = dbo.tblTRACT_OWNER.Owner_Lease) LEFT JOIN ((((((dbo.tblLPR RIGHT JOIN dbo.tblLOCATION ON dbo.tblLPR.LPR_No = dbo.tblLOCATION.LPR_No) LEFT JOIN dbo.tblLESSORS ON dbo.tblLPR.Lessor_Number = dbo.tblLESSORS.Lessor_Number) RIGHT JOIN dbo.tblLEASE_TRACTS ON dbo.tblLOCATION.LOC_ID = dbo.tblLEASE_TRACTS.LOC_ID) LEFT JOIN dbo.tblLEASE_LOG ON dbo.tblLPR.LPR_No = dbo.tblLEASE_LOG.LPR_No) LEFT JOIN dbo.tblLPR_INVOICES ON dbo.tblLPR.LPR_No = dbo.tblLPR_INVOICES.LPR_No) LEFT JOIN dbo.tblLPR_PAY ON dbo.tblLPR.LPR_No = dbo.tblLPR_PAY.LPR_No) ON dbo.tblTRACT_OWNER.TRACT__Owner_ID = dbo.tblLEASE_TRACTS.Tract_Owner_Id) ON (dbo.tblTRACT.[TRACT_#] = dbo.tblTRACT_OWNER.[TRACT_#]) AND (dbo.tblTRACT.STR = dbo.tblTRACT_OWNER.STR)
WHERE (((dbo.tblTRACT.STR)=@TRS))
)
GO
I understand now I can create a view of the function Simply by using the function name in my FROM statement. However I get an error that arguments provided do not match parameters required. However, I'm not getting the prompt to enter my criterion. Is my error in my function statement? I can't save the view. I also understand I could use a pass-through query. Is there some sort of guidance or tutorial on that to which you could point me?
Thanks for your time.
View 9 Replies
View Related
Apr 22, 2008
Can anyone help me to create a URL decode user defined function in SQL Server 2005?
I want to use the method [System.Web.HttpUtility.UrlDecode] in .net framework, and I try to add it as a CLR function to SQL Server but always fail. It depends on [System.Web.dll], and when I try to create assembly [System.Web] using following scripts, it will fail:
CREATE ASSEMBLY [System.Web] FROM 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
WITH PERMISSION_SET = UNSAFE
Error:
CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Web' is not authorized for PERMISSION_SET = UNSAFE.
The assembly is authorized when either of the following is true:
the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on;
or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
I have searched the MSDN and then add following scripts before mine:
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
CREATE ASYMMETRIC KEY SystemWebKey FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY SystemWebKey
GRANT UNSAFE ASSEMBLY TO CLRLogin
But unfortunately it fails again with same error.
View 5 Replies
View Related
Oct 20, 2014
I would like to create a table valued function using the following data:
create table #WeightedAVG
(
Segment varchar(20),
orders decimal,
calls int
);
insert into #WeightedAVG
[code].....
I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,
CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
(
col1 varchar(25),
WeightedAVG float
[Code] .....
View 4 Replies
View Related
