I have a question about the syntax of the IRR function in reporting services 2005. Basically I have a table with 5 rows. I'd like to plug a column of data into the IRR function as an array (which is what it takes). Not sure how to convert a column into an array in an expression.
Any help would be appreciated... thanks!
Ben Lieb
ben.lieb@tekelec.com
Hi,
Trying to join two tables together.
Select * from ...
inner join
on table1.ID = table2.ID;
this works fine, however i want to use the RIGHT function so that i get the last four digits of the ID. this causes a parse error:
inner join on table1.right([ID],4) = table2.right([ID],4);
searched a bit but couldn't find a way to do what im trying to accomplish. is there a proper syntax?
thanks!
In the script below is the DDL to create some tables and a UDF.What I'm interested in is the UDF at the end. Specifically, these fewlines:--CLOSE OTRate--DEALLOCATE OTRateELSE-- @NumRecords <= 0If I uncommment CLOSE and DEALLOCATE and check the syntax I get amessage:"Incorrect syntax near keyword ELSE"Being a good little footsoldier, I want to release resourcesexplicitly, but clearly I'm putting the CLOSE and DEALLOCATE statementsin the wrong place.Could someone please tell me where I ought to put them so that thecursor is CLOSEd and DEALLOCATEd correctly.By the way, I am not after negative comments on the data design, or thelogic (or lack of it) in the function, just why the syntax erroroccurs.Thanks as everEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Employee]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[PurchaseOrder]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[PurchaseOrder]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TimesheetItem]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[TimesheetItem]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Work]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Work]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[WorkOTRate]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[WorkOTRateDefaults]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[WorkOTRateDefaults]GOCREATE TABLE [dbo].[Employee] ([EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[DepartmentID] [int] NOT NULL ,[JobDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[StartDate] [smalldatetime] NOT NULL ,[EndDate] [smalldatetime] NULL ,[DefaultRatePerHour] [smallmoney] NULL ,[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[UserGroupID] [int] NOT NULL ,[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[LastLogon] [datetime] NULL ,[PasswordChange] [smalldatetime] NULL ,[PreviousPassword1] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword2] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword3] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword4] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword5] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[PurchaseOrder] ([WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,[WorkID] [int] NOT NULL ,[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[OrderDate] [datetime] NOT NULL ,[OrderValue] [money] NOT NULL ,[FixedPrice] [bit] NOT NULL ,[Prepaid] [bit] NOT NULL ,[AllocatedHours] [int] NULL ,[RatePerHour] [money] NULL ,[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[TimesheetItem] ([ItemID] [int] IDENTITY (1, 1) NOT NULL ,[EmployeeID] [int] NOT NULL ,[TypeID] [int] NOT NULL ,[Start] [smalldatetime] NOT NULL ,[DurationMins] [int] NOT NULL ,[WorkID] [int] NULL ,[WorkComponentID] [int] NULL ,[WorkItemID] [int] NULL ,[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[OffSite] [tinyint] NULL ,[TravelTo] [smalldatetime] NULL ,[TravelToMins] [int] NULL ,[TravelFrom] [smalldatetime] NULL ,[TravelFromMins] [int] NULL ,[TravelMileage] [int] NULL ,[NonChargeableMins] [int] NULL ,[OTAuthorisedID] [int] NULL ,[OTAuthorisedDate] [smalldatetime] NULL ,[Abroad] [bit] NULL ,[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ApprovalID] [int] NULL ,[AprovalDate] [smalldatetime] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Work] ([WorkID] [int] IDENTITY (1, 1) NOT NULL ,[WorkTypeID] [int] NULL ,[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Chargeable] [bit] NOT NULL ,[Complete] [bit] NOT NULL ,[ClientID] [int] NULL ,[ClientContactID] [int] NULL ,[Entered] [smalldatetime] NULL ,[ApprovalRequired] [tinyint] NULL ,[ColorCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[WorkOTRate] ([WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,[WorkID] [int] NOT NULL ,[WorkDay] [int] NOT NULL ,[TimeFrom] [datetime] NOT NULL ,[TimeTo] [datetime] NOT NULL ,[RateMultiplier] [float] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[WorkOTRateDefaults] ([PKID] [int] IDENTITY (1, 1) NOT NULL ,[WorkDay] [int] NOT NULL ,[TimeFrom] [datetime] NULL ,[TimeTo] [datetime] NULL ,[RateMultiplier] [float] NOT NULL) ON [PRIMARY]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGO/*Function to determine the actual cost, in minutes, of a particularsegment of work. This is what it does, or is supposed to do.1. From the PARAMETER WorkID, determine the conclusion of the workblock associated with the TimesheetID - i.e. StartTime + DurationMins2. Establish whether there are records in the WorkOTRate tablecorresponding to this particular WorkID, weekday and time period3. If there are, get the amount of minutes by which the work blockcoincides.4. If there are no such records, get the default values from theWorkOTRateDefaults table5. If the block doesn't cross any boundaries then it's just regularwork, so just count the minutes.25/08/2005 EC*/CREATE FUNCTION fnGetWorkCostPerTimesheetItem(@TimesheetID int)RETURNS floatASBEGINDECLARE@OTRateTimeFrom datetime,@OTRateTimeTo as datetime,@OTRateMultiplier as float,@EndTime datetime,@ReturnValue as float,@OrderRatePerHour as money,@EmployeeRatePerHour as smallmoney,@NumRecords as int,@WorkID as int,@EmployeeID as int,@StartTime as smalldatetime,@Duration as int,@Found as int,@Chargeable as bit-- Get the various bits and bobs needed for the calculationSET @ReturnValue = 0SET @Found = 0SELECT@WorkID = WorkID,@EmployeeID = EmployeeID,@StartTime = Start,@Duration = DurationMinsFROMTimesheetItemWHEREItemID = @TimesheetID-- If this work is NOT chargeable, return 0SELECT@Chargeable = ChargeableFROM[Work]WHEREWorkID = @WorkIDIF @Chargeable = 1BEGINSET @EndTime = DATEADD(mi, @Duration, @StartTime)-- Get the rate per hour for this workSELECT@OrderRatePerHour = RatePerHourFROMPurchaseOrderWHEREWorkID = @WorkID-- Get the rate per hour for the employeeSELECT@EmployeeRatePerHour = DefaultRatePerHourFROMEmployeeWHERE(EmployeeID = @EmployeeID)-- Find out if there's an OT Rate set up for this WorkIDSELECT@NumRecords = Count(*)FROMWorkOTRateWHERE((WorkID = @WorkID) AND(WorkDay = DATEPART(dd, @StartTime)))IF @NumRecords > 0BEGINDECLARE OTRate CURSOR FORSELECTTimeFrom,TimeTo,RateMultiplierFROMWorkOTRateWHERE((WorkID = @WorkID) AND(WorkDay = DATEPART(dw, @StartTime)))OPEN OTRateFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierWHILE (@@fetch_status=0)BEGIN-- Set the two time values so that they match the date underconsideration.SET @OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @OTRateTimeFrom,@StartTime) ,@OTRateTimeFrom)SET @OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @OTRateTimeTo ,@StartTime) ,@OTRateTimeTo)-- If the TimeTo part is < TimeFrom, then we know it crosses atime boundaryIF @OTRateTimeTo < @OTRateTimeFromSET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)-- If the time is between midnight and 8 a.m. it's the "next"dayIF CONVERT(datetime, @OTRateTimeFrom, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeFrom = DATEADD(dd, 1, @OTRateTimeFrom)IF CONVERT(datetime, @OTRateTimeTo, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)/*Ok, now we're in business. There are four possible scenariosthat we are interested in (ignoring when the Timesheet item period isentirely outside the OT rate period)NUMBER 1S EOT OTNUBMER 2S EOT OTNUMBER 3S EOT OTNUBMER 4S EOT OT*/-- NUMBER 1IF (@StartTime < @OTRateTimeFrom) AND (@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 2ELSE IF (@StartTime < @OTRateTimeFrom) AND (@EndTime BETWEEN@OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @EndTime)) * @OTRateMultiplier))SET @Found = 1END-- NUMBER 3IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo) AND(@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 4ELSE IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)AND (@EndTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@EndTime)) * @OTRateMultiplier))SET @Found = 1ENDFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierENDEND--CLOSE OTRate--DEALLOCATE OTRateELSE-- @NumRecords <= 0BEGINDECLARE OTRate CURSOR FORSELECTTimeFrom,TimeTo,RateMultiplierFROMWorkOTRateDefaultsWHERE(WorkDay = DATEPART(dw, @StartTime))OPEN OTRateFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierWHILE (@@fetch_status=0)BEGIN-- Set the two time values so that they match the date underconsideration.SET @OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @OTRateTimeFrom,@StartTime) ,@OTRateTimeFrom)SET @OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @OTRateTimeTo ,@StartTime) ,@OTRateTimeTo)-- If the TimeTo part is < TimeFrom, then we know it crosses atime boundaryIF @OTRateTimeTo < @OTRateTimeFromSET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)-- If the time is between midnight and 8 a.m. it's the "next"dayIF CONVERT(datetime, @OTRateTimeFrom, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeFrom = DATEADD(dd, 1, @OTRateTimeFrom)IF CONVERT(datetime, @OTRateTimeTo, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)/*Ok, now we're in business. There are four possible scenariosthat we are interested in (ignoring when the Timesheet item period isentirely outside the OT rate period)NUMBER 1S EOT OTNUBMER 2S EOT OTNUMBER 3S EOT OTNUBMER 4S EOT OT*/-- NUMBER 1IF (@StartTime < @OTRateTimeFrom) AND (@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 2ELSE IF (@StartTime < @OTRateTimeFrom) AND (@EndTime BETWEEN@OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @EndTime)) * @OTRateMultiplier))SET @Found = 1END-- NUMBER 3IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo) AND(@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 4ELSE IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)AND (@EndTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@EndTime)) * @OTRateMultiplier))SET @Found = 1ENDFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierENDENDCLOSE OTRateDEALLOCATE OTRate-- If there were no matching OT records, it's just a regular blockof work in normal hoursIF @Found = 0SET @ReturnValue = @DurationEND-- Finally we factor in the relation between the Employee's rate andthe Order's stated rate.RETURN (@ReturnValue * (@EmployeeRatePerHour / @OrderRatePerHour))ENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 3 Replies View RelatedSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION DetailedView
AS
BEGIN
Declare @fieldname varchar(10)
Declare @stmt varchar(4000)
Declare Fields Cursor For Select Amounttype From Amounttypes
Set @stmt = 'Select pono, myid, billtype'
Open Fields
Fetch Next From Fields Into @fieldname
While @@Fetch_Status = 0 Begin
Set @stmt = @stmt + ', sum(amountfc * Case When amounttype = ''' + @fieldname + ''' Then 1 Else 0 End) As ' + @fieldname
Fetch Next From Fields Into @fieldname
End
Close Fields
Deallocate Fields
Set @stmt = @stmt + ' From multiplebillsviewall Group By pono, myId,billtype '
return Exec(@stmt)
END
I am trying to access data from a database to print a report. The code I am using says I have a syntax error regarding the object person_info. Person_Info is a table value function on the SQL Server (2005). Does anybody see a problem with this line of code? Thanks for your help! Regards, Steve
"INNER JOIN (SELECT * Person_Info FROM (" & Session("current_project") & ")) ON pc.personID_fk=pe.personID_pk " & _
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
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
View 3 Replies View RelatedHi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
Hi,
I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )
i hope to have the answer because i need it so much.
Maylo
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
View 3 Replies View Related
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
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)
Hi All
Yesterday Peso was gracious enough to help me with creating function/views/sp's
I took those examples and extended what had from excel into function in SQL
however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?
Here are excerpts two functions I have:
We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)
and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)
As you can see the first part of the both query are simlar. In particular the:
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
and also the variables @StartDate and @EndDate.
In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?
Thank you!
Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG
View 5 Replies View RelatedWhat I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
Hi, i am trying to write a mulitple sql statement. basically i have 5 fields to search:
User ID
FirstName
LastName
Department
Site
i would like to search the records of the database with any of the fields above, so a user can specify a last name of "smith" and a department of "finance" which would return all the smiths in the finance department. or if a user enters "john" all the johns from any department or site would appear. How would the sql statement go like for this? and could i bind a tableadapter to a datagrid to view the results?
Any help would be appreciated. Thank you.
cmd.CommandText="Insert INTO personaldet(Firstname,Lastname,Username,password,dob,) values '('"+txtfname.Text.Replace("'", "''").ToString()+"','"+txtlname.Text+"','"+txtusername.Text+"','"+txtpassword.Text+"','"+txtdob.Text+"')'";
always says syntax error ')'.
check this plz
Hello, I have at sp that return a value:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getLastActivityDate]
(
@userid nvarchar(256)
)
AS
DECLARE @ret nvarchar(256)
SET @ret = (SELECT CAST(LastActivityDate AS nvarchar)
FROM aspnet_Users
WHERE UserName = @userid)
RETURN @ret
The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @ret?
// Tomas
I just cannot get the syntax right for this: (The problem is in my TSart and TEnd)
Dim SQL As String = "Select DateEntered FROM tblTasks Where [DateEntered] Between " '" & TStart & "'" And "'" & TEnd & "'" And [ID] = " & _
IDSent
Thank you for any help,
HI All,
I am writing some code where i want to get a list of all the company names that start with say, M...
here is what i have:
Dim M_company, companyID_M
Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )
but this is only returning the first company in the list that starts with M. Can anybody help with getting it to return more than one value???
thanks,
Leissa
I'm a 3GL guy not familiar with SQL that much. What I try to add a record to shopping cart and assign a line number for each record. If the same item already exist, instead of adding a new line I'd like to add to existing line. I use to following code and I got two lines created even I was only adding the very first first. The following are the code I wrote, would anyone please give some hints. Thx.
ALTER PROCEDURE dbo.AddItemToCart
(
@Cart nvarchar(50),
@ItmID int,
@Qty int,
@ItmTyp char
)
As
Declare @AllItmCount Int
SELECT
@AllItmCount = Count(ItmID)
FROM
ShoppingCart
WHERE
Cart = @Cart
IF @AllItmCount = 0 /*new shoppong cart*/
INSERT INTO ShoppingCart
( Cart, Qty, ItmID, ItmTyp, Line )
VALUES
( @Cart, @Qty, @ItmID, @ItmTyp, 1)
ELSE /*some item entered */
DECLARE @CountItems int
SELECT
@CountItems = Count(ItmID)
FROM
ShoppingCart
WHERE
ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
IF @CountItems > 0 /* same item entered before then add together */
UPDATE ShoppingCart
SET Qty = (@Qty + ShoppingCart.Qty)
WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
ELSE /* Find the last line number */
Declare @LastLine Int
SELECT @LastLine = Max(Line)
FROM ShoppingCart
WHERE Cart = @Cart
/*Add a new line */
INSERT INTO ShoppingCart
(Cart, Qty, ItmID, ItmTyp, Line)
VALUES
(@Cart, @Qty, @ItmID, @ItmTyp, @LastLine + 1)
Hi all,
I'm trying to build a simple forum. I want to display a list containing the forum names and last post date and last post author. I can view the date, but can't figure out a way to see the author. Here's the query i use:
SELECT ForumID, ForumTitle, ForumDesc, ForumTopics, ForumReplies, (SELECT MAX(AddedDate) FROM (SELECT ForumID, AddedDate, Author FROM dbo.Forums_Topic UNION ALL SELECT ForumId, AddedDate, Author FROM dbo.Forum_Reply) AS dates WHERE (ForumID = dbo.Forums.ForumID)) AS ForumLastPostDateFROM dbo.Forums
Any suggestion on how to show the author of the last message as well? Thanks in advance.
i want to find some repeatable data in a column,is there a sql syntax here?Thanks in advance.
View 1 Replies View RelatedHello,
I am trying to create a view, but I am having trouble with the syntax (go figure!). It seems that as I reference more and more tables, my result set becomes smaller. For example, I want to return the state for all my customers then everything is fine, but when I want the state plus the site type (home, office, etc.) then I return fewer results. What is would be the correct syntax to use to return all the rows even if some of the columns have NULL?
SELECT TimeZone.LongZone, SiteMain.SiteNumber,
SiteType.SiteTypeDescription
FROM SiteType INNER JOIN
TimeZone ON SiteType.id = TimeZone.id INNER JOIN
SiteMain ON TimeZone.id = SiteMain.TimeZone AND
SiteType.id = SiteMain.SiteType
Can someone help me with the syntax for an if else statement such as:
select (if column(a) > 1) then print '+' else print '-')
,column(b)
,column(c)
,etc.
from table...
BOL doesn't seem to be much help for this
Thanks in advance!
CP