OPTIMIZER LOCK HINTS option - 1

[stnkyminky]

I read the posts at thread183-731431, but I don't really understand the problem.

I have a udf call fn_Split2 that accepts a INT. The function returns a table (key,value).

I'm attempting to the do following:

select distinct rl.Log_ID
from tblReqLog rl
inner join tblReqProd rp 
on rp.LogID = rl.Log_ID
inner join tblRepZip_fg rz 
on rz.zipCode=149  [COLOR=green]/*This will reference the requestors zip code */[/color]
where rz.product in (select value from fn_split2(Log_ID))

tblReqLog - contain information about a literature request
tblReqProd - contains references to the products made with the request.
tblRepZip - contains information about a Rep's area of responsiblity and the products they provide
Ex.

zipcode | RepCode | Products
----------------------------
242     | Rep001  |    1
242     | Rep001  |    2
242     | Rep001  |    3
242     | Rep002  |    1
242     | Rep002  |    2

As you can see I'm trying to determine all the reps that are in the requestors area and provides the products that the requestor is interested in.

This was my attempt to gather that data in one query. I'm new to creating UDF's, so please be gentile in you explanation.

If anyone can provide a better way of accomplishing this
please feel free to share....

Thanks



Scott
Programmer Analyst
<{{><

 

[zhavic]

You can only pass column value to the function if a function is in select list and if it is returning value, not a table, for example:

SELECT some_value, dbo.fn_split2( Log_ID )
FROM tblReqLog

In your case you can pass to the function only direct value ( dbo.fn_split2( 5 ), or variable ( dbo.fn_split2( @ID ) ).

If you can, post there your function fn_split2, and we can write your query another way.

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[stnkyminky]

Thanks for you help. This problem is holding up my project and I would appreciate any help.


Modified Version, Original Version found at

http://www.winnetmag.com/SQLServer/Article/ArticleID/21071/21071.html

CREATE FUNCTION fn_Split2(@_ID int)
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
	@value varchar(8000),
	@bcontinue bit,
	@iStrike smallint,
	@iDelimlength tinyint,
	@sText varchar(8000),
	@sDelim varchar(20)

IF @sDelim = 'Space'
	BEGIN
	SET @sDelim = ' '
	END

[COLOR=green]/*MOD*/
/*Set @sText to a comma separated value
ex. 1,2,3,4
*/
[/color]

Select @sText = COALESCE(@sText, '') + Cast(rp.prodid as varchar(200)) + ','
	    FROM tblReqProd rp with (NOLOCK)
		where rp.logid=@_ID
		order by rp.prodid

[COLOR=green]
/*End Mod*/
[/color]

Set @sDelim = ','
SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
	BEGIN
	WHILE @bcontinue = 1
		BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
		IF CHARINDEX(@sDelim, @sText)>0
			BEGIN
			SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			
--Trim the element and its delimiter from the front of the string.
			--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
			SET @idx = @idx + 1
			SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
		
			END
		ELSE
			BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			--Exit the WHILE loop.
SET @bcontinue = 0
			END
		END
	END
ELSE
	BEGIN
	WHILE @bcontinue=1
		BEGIN
		--If the delimiter is an empty string, check for remaining text
		--instead of a delimiter. Insert the first character into the
		--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
		IF DATALENGTH(@sText)>1
			BEGIN
			SET @value = SUBSTRING(@sText,1,1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			SET @idx = @idx+1
			SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
			
			END
		ELSE
			BEGIN
			--One character remains.
			--Insert the character, and exit the WHILE loop.
			INSERT @retArray (idx, value)
			VALUES (@idx, @sText)
			SET @bcontinue = 0	
			END
	END

END

RETURN
END

Scott
Programmer Analyst
<{{><

 

[zhavic]

At first, the best way will be if you change you database design
( create new table for storing values that you has coma delimited - one row for each of values and join them by some key )

But maybe you can't do this, so there is some solution which may work.

Try to change your function to test, if rz.product is in needed values. To do that, change begin and end of your function:

CREATE FUNCTION fn_Split2(@_ID int, @product varchar(1000) )
RETURNS bit 
AS
BEGIN

DECLARE @return bit
DECLARE @retArray TABLE (idx smallint Primary Key, value varchar(8000))

-- your original code
DECLARE @idx smallint,
    @value varchar(8000),
    @bcontinue bit,
    ... 
    ...

and at end of your function replace row with RETURN statement with this:

IF ( SELECT MIN(idx) FROM @retArray WHERE value = @product ) IS NOT NULL
	SET @return = 1
ELSE
	SET @return = 0

RETURN @return

Then try to run this query:

select distinct rl.Log_ID
from tblReqLog rl
    inner join tblReqProd rp on rp.LogID = rl.Log_ID
    inner join tblRepZip_fg rz on rz.zipCode=149  /*This will reference the requestors zip code */
where dbo.fn_split2( rl.Log_ID, rz.product ) = 1

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[stnkyminky]

At first, the best way will be if you change you database design
( create new table for storing values that you has coma delimited - one row for each of values and join them by some key )

Is this what you're talking about? If so then I already have that table.

IDKEY  | LogID  | ProdID
--------------------------
1      |   174  |    1
1      |   174  |    2
1      |   174  |    3

The split2 function attempted to take the values above, by logid, and put them into the table to be returned. Hence passing the LogID from the query into the function. I thought this would allow the IN to work.

Using these values I could determine the reps in a given area that provide the products requested.


Scott
Programmer Analyst
<{{><

 

[zhavic]

Ok, so I don't understand why you are using function that splits some coma ( or with another delimiter ) delimited values to table, if have one.

What value you get in that statement in your function for some LogID ?

Select @sText = COALESCE(@sText, '') + Cast(rp.prodid as varchar(200)) + ','
FROM tblReqProd rp with (NOLOCK)
where rp.logid=@_ID
order by rp.prodid

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[stnkyminky]

After staring at problem for awhile..sometimes you miss the answer right in front of you.
I guess I was trying to be too eloquent with my solution. I've solved the problem now. Thanks for you help

Scott
Programmer Analyst
<{{><

 

[zhavic]

No problem,

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.