June 8, 2008 at 12:20 am
Dear All
CREATE TABLE [Inbox] (
[GlobalInboxId] [bigint] IDENTITY (1, 1) NOT NULL ,
[FormCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FormType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SenderId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipientId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeReceived] [datetime] NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FormNameCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Inbox] PRIMARY KEY CLUSTERED
(
[GlobalInboxId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
The Data will be
Inbox
GlobalInboxIdFormCodeFormTypeSenderIdRecipientIdCreatedByTimeReceivedDisplayNameFormNameCode
1CHR-1Crequestai.arcai.arcai.arc20/05/2005CHRRequestCHR,Issue
2Issue-2Crequestbi.birbi.birbi.bir20/05/2005CHRRequestCHR,Issue
3CHR-2Crequestai.arcai.arcai.arc20/05/2005CHRRequestCHR,Issue
4Sup-1Srequestsi.arcsi.arcsi.arc20/05/2005SURequestSup
i am using the following funtion
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fnDStringToTable]'))
DROP FUNCTION [dbo].[fnDStringToTable]
GO
--This UDF will split a delimited list into a single column table.
CREATE FUNCTION dbo.fnDStringToTable
(
@list NVARCHAR(4000)
, @delimiter NCHAR(1) = ',' --Defaults to CSV
)
RETURNS
@tableList TABLE(
value NVARCHAR(100)
)
AS
BEGIN
DECLARE @value NVARCHAR(100)
DECLARE @position INT
SET @list = LTRIM(RTRIM(@list))+ ','
SET @position = CHARINDEX(@delimiter, @list, 1)
IF REPLACE(@list, @delimiter, '') <> ''
BEGIN
WHILE @position > 0
BEGIN
SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))
IF @value <> ''
BEGIN
INSERT INTO @tableList (value)
VALUES (@value)
END
SET @list = RIGHT(@list, LEN(@list) - @position)
SET @position = CHARINDEX(@delimiter, @list, 1)
END
END
RETURN
END
GO
when i execute following query
i am getting this error 'Line 2: Incorrect syntax near '.'.'
select * from Inbox
inner join dbo.fnDStringToTable(Inbox.FormNameCode, ',') s on s.value=SubString(Formcode,0,CharIndex('-',Formcode))
Please help me to solve this
June 8, 2008 at 12:15 pm
it seems that you are trying to pass the FormNameCode of each row to the function and hoping than sql makes some kind of UNION of each result set to be able to do the JOIN with table Inbox.
sadly that cannot be done.
what i suggest you to do is to create a function that return directly a complete table with the value column already calculated for all rows in the Inbox table and then doing the join you want.
If what you want is to create a generic function that can acept a generic delimited list you also can create an sp that uses a temporal table as generic input parameter and fills another generic table with output calculated values.
pd: sorry for my bad english i hope you understand
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply