Not sure what I'm doing wrong here....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Not sure what I'm doing wrong here....

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-09-02 : 16:33:19

Alright, let me explain the problem.

I have a table (#pattern_2 seen below) with:

Pattern Code
Block Index
Value Code

Some sample data from this Table might be like this:


pattern_Code     block_index    value_code
------------     -----------    ----------
1111		    0	            A
1111		    1 	    	    E
1111		    2		    I
1111		    2		    J
1111		    2 	            K
1111	            3		    Y
1111		    3		    Z

What I want to do is populate a table called "#hold1" with all possible permutations of the data above for each pattern_code. The index would determine the order in which the values are arranged and in which position they sit.

So, in the above data for that one pattern code, possible part numbers might be:

'AEIY',
'AEJY',
'AEKY',
'AEIZ',
'AEJZ',
'AEKZ'

I want to populate a table with all those possible partnumbers, one pattern code at a time and then check that table to see if the partnumber I'm searching for exists in the partnumber table I just populated. I'm currently trying the below code and am having various difficulties.

Perhaps someone has a better, more elegant solution than I?


Create table #hold1(partnumber varchar(50))
Create table #hold2(partnumber varchar(50))

Set @pattern=''

Declare patCursor Cursor for 
    select pattern, count(pattern) as hits
	from #pattern_2
	group by pattern
	order by hits desc

Open patCursor 
Fetch next from patCursor into @pattern, @dummyhits
while (@@fetch_status<>-1) 
Begin 
	set @x=0
	set @total=0
	select @Total = max(block_index) from #pattern_2 where pattern=@pattern
	
	WHILE @x <= @Total
		BEGIN
		if (select count(partnumber) from #hold1)<1 
			begin 
				insert into #hold1 
				  select value_code from #pattern_2
					where pattern=@pattern
					and 
					block_index=@x
			end
		else 
			begin
				insert into #hold2 
					select partnumber+(
				  	  select value_code 
						from #pattern_2
						where pattern=@pattern
						and   block_index=@x) 
					from #hold1
				Truncate table #hold1 
			end
					
			insert into #hold1 
				select partnumber from #hold2
			Truncate Table #hold2	
		
		SELECT @x = @x + 1
	End

Select @match=count(partnumber) from #hold1 where partnumber=@partnumber

if @match>0 
	Begin 
		print 'Mypat' + @partnumber
		break
	end 

Truncate table #hold1 
Fetch next from patCursor into @pattern, @dummyhits
End

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-02 : 17:21:52

question:

Does the "1" mean anything specific in the pattern code, or is it just a placeholder. i.e., are there any other possible characters in the "pattern" column?

Also: what is the length of the pattern column? how many characters are in the longest pattern?

- Jeff

label
Posting Yak Master

197 Posts

Posted - 2003-09-02 : 17:29:03

quote:
Originally posted by jsmith8858

question:

Does the "1" mean anything specific in the pattern code, or is it just a placeholder. i.e., are there any other possible characters in the "pattern" column?

If I understand your question, there are 2500 different pattern codes we're searching through, ranging from 1001$ to 8084X.

However, we eliminate all but 2-10% of them right of the bat by dumping all Pattern Codes that don't have any letters/numbers in the "0" "block_index" that match the first letter of the Partnumber.

So if the partnumber we're searching for is

"CDQ263-100DM-A75"

, we only search through pattern codes where there's a "C" of some kind in the "0" index.

quote:
Also: what is the length of the pattern column? how many characters are in the longest pattern?

Pattern? Most are 5 charachters in length. However, that's not what we're searching for.

We want to match a Partnumber (i.e. "CDQ263-100DM-A75") to the value's in the Value_Code column - using the block index to put the values in the right order - and then return a Pattern Code.

Hope that helps.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-03 : 11:00:27

Ok....It seems like you're making a "smart number" out of the data...

This is the problems you'll run into...plus the fact that you won't be able to use the database to constrain the data...it'll all have to be in code...

in other words, I believe the data should be normalized....

anyway I was hacking around with the following...it precludes that you need to "know" how many block indexes you have...


USE Northwind
GO

CREATE TABLE Patern_2 (pattern_Code int, block_index int, value_code char(1))
GO

INSERT INTO Patern_2 (pattern_Code, block_index, value_code)
SELECT 1111,		    0,	            'A' UNION ALL
SELECT 1111,		    1, 	    	    'E' UNION ALL
SELECT 1111,		    2,		    'I' UNION ALL
SELECT 1111,		    2,		    'J' UNION ALL
SELECT 1111,		    2, 	            'K' UNION ALL
SELECT 1111,	            3,		    'Y' UNION ALL
SELECT 1111,		    3,		    'Z'
GO

SELECT a.Value_Code+b.Value_Code+c.Value_Code+d.Value_Code
  FROM Patern_2 a, Patern_2 b, Patern_2 c, Patern_2 d
 WHERE a.Pattern_Code = b.Pattern_Code 
   AND b.Pattern_Code = c.Pattern_Code 
   AND c.Pattern_Code = d.Pattern_Code
   AND a.block_index = 0 
   AND b.block_index = 1 
   AND c.block_index = 2  
   AND d.block_index = 3
ORDER BY 1

GO

DROP TABLE Patern_2
GO

Is that what you're looking for?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

label
Posting Yak Master

197 Posts

Posted - 2003-09-04 : 11:36:19

quote:
Originally posted by X002548
Ok....It seems like you're making a "smart number" out of the data...

This is the problems you'll run into...plus the fact that you won't be able to use the database to constrain the data...it'll all have to be in code...

in other words, I believe the data should be normalized....

Unfortuantely I don't have control over the database design. I simply have to work with what's there because it's part of a bigger system.

quote:
anyway I was hacking around with the following...it precludes that you need to "know" how many block indexes you have...

Unfortuantely I don't know how many block indexes there will be for any given pattern code.

Some actual Sample data for 2 pattern codes (out of hundreds):


Pattern	     Value Code    Index     
2001@		C	    0
2001@		D	    1	
2001@		10	    4	
2001@		-	    5	
2001@		10	    6	
2001@		100	    6	
2001@		C	    7	
2001@		-	    9	
2001@		A	   10	
2001@		B	   10	
2001@		-	   11	
2002		C	    0	
2002		D	    1	
2002		B	    3	
2002		D	    3	
2002		6	    4	
2002		10	    4	
2002		-	    5	
2002		10	    6	
2002		D	    7	
2002		-	    8	
2002		B	    9	
2002		-	   10	
2002		3	   12

As you can see by these results, the number of indexes may change and it may even skip some indexes.

For the pattern code 2001 I'd only want the following partnumbers returned:

cd10-10c-a-
cd10-100c-a-
cd10-10c-b-
cd10-100c-b-

That's all the possible permutations of the given values and indexes.

quote:
SELECT a.Value_Code+b.Value_Code+c.Value_Code+d.Value_Code
FROM Patern_2 a, Patern_2 b, Patern_2 c, Patern_2 d
WHERE a.Pattern_Code = b.Pattern_Code
AND b.Pattern_Code = c.Pattern_Code
AND c.Pattern_Code = d.Pattern_Code
AND a.block_index = 0
AND b.block_index = 1
AND c.block_index = 2
AND d.block_index = 3
ORDER BY 1

Unfortuantely I won't know the amount of indexes or which ones I'll even have in the first place.

Any other thoughts?

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 12:02:13

You basically have a hierarchy thing going on...

can you create new tables?

Check out robs article....

http://www.sqlteam.com/item.asp?ItemID=8866

but basically you'll have to build a process that for every pattern you have to create, starting with the lowest index each family tree...

I'll give it a shot...but I think the "C" word might apply hear...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

label
Posting Yak Master

197 Posts

Posted - 2003-09-04 : 14:49:16

quote:
Originally posted by X002548
but basically you'll have to build a process that for every pattern you have to create, starting with the lowest index each family tree...

I'll give it a shot...but I think the "C" word might apply hear...

I'm a little ignorant here.....what's the "C" word? "Connect By" that MS SQL server doesn't support or "Cross Join" possibly?

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 16:39:49

quote:
Originally posted by label
I'm a little ignorant here.....what's the "C" word? "Connect By" that MS SQL server doesn't support or "Cross Join" possibly?

[shudder]
CURSOR
[/shudder]

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-04 : 17:05:12

I think I have it:


create table #tmp(Pattern varchar(10), ValueCode varchar(10), [Index] int)

insert into #tmp
select '2001@','C',0 union
select '2001@','D',1 union
select '2001@','10',	   4	union
select '2001@','-',	   5	union
select '2001@','10',   6	union
select '2001@','100',   6	union
select '2001@','C',   7	union
select '2001@','-',   9	union
select '2001@','A',  10	union
select '2001@','B',  10	union
select '2001@','-',  11	union
select '2002','C',   0	union
select '2002','D',   1	union
select '2002','B',   3	union
select '2002','D',   3	union
select '2002','6',   4	union
select '2002','10',   4	union
select '2002','-',   5	union
select '2002','10',   6	union
select '2002','D',   7	union
select '2002','-',   8	union
select '2002','B',   9	union
select '2002','-',  10	union
select '2002','3', 12

create table #result (Pattern varchar(10), [Index] int, Result varchar(200))

declare @i int

insert into #result 
select Pattern, 0, ValueCode
from #tmp
where [Index] = 0

set @i = 1

while (@i <= (select max([Index]) from #tmp))
	begin
	insert into #result
	select B.Pattern, @i, Result + ValueCode
	from #result A
	inner join #tmp B on
		B.Pattern = A.pattern AND
		B.[Index] = @i
	inner join
		(select pattern, max([index]) as MaxIndex
		from #result group by pattern) C
	on
		A.Pattern = C.Pattern AND
		A.[Index] = C.MaxIndex

	set @i = @i  + 1
	end

delete from #result
where [Index] <> (select max([Index]) from #result B where #result.Pattern = B.Pattern)

select * from #result

GO
drop table #result
drop table #tmp

It has to be recursive .... of course, my solution is tchinically iterative but who's counting.

It took a while to understand your requirements, and to see that "Pattern" has NOTHING to do with the final result !

- Jeff

label
Posting Yak Master

197 Posts

Posted - 2003-09-04 : 17:38:32

First off, "cursor"....got it.

Secondly thanks for all the great suggestions I got. They were definitely helpful in solving the problem.

Finally, for the morbidly curious, here's the final piece of Code that actually works as a search engine into this heircharchal nightmare of a database.


CREATE PROCEDURE dbo.zz_partnumber_to_PatternCode 

@partnumber varchar(50), 
@returnpattern varchar(10) output

AS

Begin 

Declare @patterncode varchar(10),
	@x int, 
	@Total int,
        	@value varchar(10), 
	@valuelen int, 
	@partlen int, 
	@updatedPartNum varchar(100), 
	@totalResults int, 
	@match int, 
	@dummyhits int

set @match=0

Create table #temp(value varchar(10)) 
Create table #pattern_1 (pattern varchar(10))
Create table #pattern_2 (pattern varchar(10), value_code varchar(20), block_index int, hits int)

--Step 1 Narrow down the list of possible matches by only using pattern codes where the first letter matches
Declare Cursid cursor for 
	select distinct(value_code) from cc_catalog_smc_3100.dbo.sdb_block_value bv
	join cc_catalog_smc_3100.dbo.sdb_pattern_block pb
	on bv.block_id=pb.block_id
	where block_index=0
	and value_code like left(@partnumber, 1)+'%'

Open cursid 

fetch next from cursid into @value
while (@@fetch_status<>-1) 
begin 

Set @valuelen=len(@value) 

if @value=left(@partnumber, @valuelen) 
	Begin 
		insert into #temp values (@value)
	end

fetch next from cursid into @value
End

--Insert all possible matches 
insert into #pattern_1
	select pattern_code from cc_catalog_smc_3100.dbo.sdb_pattern_block pb 
	join cc_catalog_smc_3100.dbo.sdb_block_value bv
	on 
		pb.block_id=bv.block_id
	where 
		block_index=0
		and 
		value_code in 
			(select * from #temp) 

deallocate Cursid


Declare @pattern varchar(10), 
	@curhits int, 
	@curValue varchar(20)

set @curhits=0

--Stick all the values for the blockindexs into a Temp table to increase performance
Create table #pattern_Values (pattern varchar(10), value_code varchar(20), block_index int) 
insert into #pattern_values 
	select pattern_code, value_code, block_index
	from cc_catalog_smc_3100.dbo.sdb_block_value bv
	join cc_catalog_smc_3100.dbo.sdb_pattern_block pb 
	on bv.block_id=pb.block_id 
	where pattern_code in 
		(select pattern from #pattern_1) 

--Determine the most likely matches and reorder the pattern codes accordingly
Declare curPattern Cursor for 
	select pattern from #pattern_1 order by pattern

Open curPattern

Fetch next from curPattern into @pattern
while (@@fetch_status<>-1) 
Begin 
	set @x=0
	set @total=0
	select @Total = max(block_index) from #pattern_values where pattern=@pattern
	
	WHILE @x <= @Total
		BEGIN
			Declare curValue Cursor for 
				Select value_code from #pattern_values
				where pattern=@pattern
				and
				block_index=@x
		
			Open curValue
			fetch next from curValue into @curValue
			While (@@fetch_status<>-1)
			Begin
				if patindex('%'+@curvalue+'%', @partnumber)>0 
					begin 
						insert into #pattern_2 values (@pattern, @curValue, @x, 1)
					end
	
				fetch next from curValue into @curValue
			End
			
			deallocate curValue
		    SELECT @x = @x + 1
		END

Fetch next from curPattern into @pattern
End


--Create tables to hold all possible permutations of a single pattern code and then check to see if the partnumber that was entered is in the #hold1 table
Create table #hold1(partnumber varchar(50), pattern varchar(10))
Create table #hold2(partnumber varchar(50), pattern varchar(10))
Create table #hold3(partnumber varchar(50), pattern varchar(10))

Set @pattern=''

Declare patCursor Cursor for 
    select pattern, count(pattern) as hits
	from #pattern_2
	group by pattern
	order by hits desc

Open patCursor 
Fetch next from patCursor into @pattern, @dummyhits
while (@@fetch_status<>-1) 
Begin 

	Declare indexCursor Cursor for
		(select distinct(block_index) from #pattern_2
			where pattern=@pattern)
	
	Open indexCursor
	fetch next from indexCursor into @x
	while (@@fetch_status<>-1) 
	BEGIN
		--if this is the first time through for this pattern code, just populate the table and move on
		if (select count(partnumber) from #hold1)<1 
			begin 
				insert into #hold1 
				  select value_code, @pattern from #pattern_2
					where pattern=@pattern
					and 
					block_index=@x
			end
		else 
			--if this is the second or later index in the pattern code, join it with all possible permutations and put in the temp #hold2 table
			begin
				insert into #hold3 
					select value_code, @pattern 
						from #pattern_2
						where pattern=@pattern
						and block_index=@x

				insert into #hold2 
					select a.partnumber + b.partnumber, @pattern from #hold1 a
						join #hold3 b
						on a.pattern=b.pattern

				Truncate table #hold1
				Truncate Table #hold3 
			end
					
			insert into #hold1 
				select partnumber, @pattern from #hold2
			Truncate Table #hold2	
		
		fetch next from indexCursor into @x
		End
	
		deallocate indexCursor

--Check to see if we have a match
--select partnumber, pattern from #hold1
Select @match=count(partnumber) from #hold1 where partnumber like @partnumber+'%'

--If we have a match, report that and exit out
if @match>0 
	Begin 
		set @returnpattern=@pattern
		print 'MyPattern = ' + @pattern
		select @returnPattern

		deallocate patCursor
		deallocate curPattern
		drop table #temp
		drop table #pattern_1
		drop table #pattern_values
		
		drop table #hold1
		drop table #hold2
		drop table #hold3
		drop table #pattern_2
	
		Return 
	end 

Truncate table #hold1 
Fetch next from patCursor into @pattern, @dummyhits
End

--Clean up if we don't find a part
deallocate patCursor
deallocate curPattern
drop table #temp
drop table #pattern_1
drop table #pattern_values

drop table #hold1
drop table #hold2
drop table #hold3
drop table #pattern_2

end
GO

Thanks again for your suggestions and help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-04 : 18:37:23

I highly recommend checking my solution out to make all the possible patterns -- it will be much faster than with a cursor.

- Jeff

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 08:53:47

quote:
Originally posted by jsmith8858
I highly recommend checking my solution out to make all the possible patterns -- it will be much faster than with a cursor.

I'd love to. In fact, I tried applying your logic and apparently I'm not getting exactly what your doing because I couldn't succesfully integrate that code into my Stored Proc to get it to generate all possible permutations for a given Pattern code.

If you've got a minute at some point, could you illustrate how you might modify my existing Stored Proc to get rid of those cursors and employ you're logic? I'm definitely looking to increase performance.

On most searches it's only taking about 0-1 seconds to return a partnumber, but occasionally on larger partnumbers it can take 10-20 seconds so any ideas you have are appreciated.

Thanks again.

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 09:03:48

To help explain where I'm having problems (as noted in my above post) I'll go through my questions with your code:

quote:

Originally posted by jsmith8858
I think I have it:

create table #tmp(Pattern varchar(10), ValueCode varchar(10), [Index] int)

insert into #tmp
select '2001@','C',0 union
select '2001@','D',1 union
select '2001@','10', 4 union
select '2001@','-', 5 union
select '2001@','10', 6 union
select '2001@','100', 6 union
select '2001@','C', 7 union
select '2001@','-', 9 union
select '2001@','A', 10 union
select '2001@','B', 10 union
select '2001@','-', 11 union
select '2002','C', 0 union
select '2002','D', 1 union
select '2002','B', 3 union
select '2002','D', 3 union
select '2002','6', 4 union
select '2002','10', 4 union
select '2002','-', 5 union
select '2002','10', 6 union
select '2002','D', 7 union
select '2002','-', 8 union
select '2002','B', 9 union
select '2002','-', 10 union
select '2002','3', 12

I'm not sure I understand what you're doing here. The above data that you used from my post was only 2 of 253 possible pattern codes. Since I've got hundreds of pattern codes that might be the suspect and for each index, multiple values that might match up with the part number, there's no way for me to do a select "union" on specific sets of pattern codes like this.

quote:

create table #result (Pattern varchar(10), [Index] int, Result varchar(200))

declare @i int

insert into #result
select Pattern, 0, ValueCode
from #tmp
where [Index] = 0

set @i = 1

while (@i <= (select max([Index]) from #tmp))
begin
insert into #result
select B.Pattern, @i, Result + ValueCode
from #result A
inner join #tmp B on
B.Pattern = A.pattern AND
B.[Index] = @i
inner join
(select pattern, max([index]) as MaxIndex
from #result group by pattern) C
on
A.Pattern = C.Pattern AND
A.[Index] = C.MaxIndex

set @i = @i + 1
end

delete from #result
where [Index] <> (select max([Index]) from #result B where #result.Pattern = B.Pattern)

select * from #result

The problem I see with this is that the indexes do not increment by the same amount every time. I might have 0,1,2,4,5,7,8 as my indexes for a given pattern code that have values that match and indexes 0,2,4,5,6,8,10,11 that match on a different pattern code.

This is why I used a cursor to iterate through each successive index for each pattern code. Originally I had something very similiar to that code where I was using a While counter to loop through the indexes.

quote:
It took a while to understand your requirements, and to see that "Pattern" has NOTHING to do with the final result !

I may be misunderstanding you, but the Pattern is what I'm trying to get as my final result.

The goal of the Stored Proc is to input a Partnumber and have that return a Pattern code.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-05 : 09:34:25

label --

I was just generating sample data based on what you gave me! Of course you would use your own table. I couldn't test this out if there was no data to use! I wrote it this way so you can cut and paste into query analyzer, make sure it works, and then replace #tmp with your actual table of patterns.

Cut and paste and TRY IT OUT! and then point it to your own table, instead of my #tmp table of sample data. Does that make sense?

quote:

The problem I see with this is that the indexes do not increment by the same amount every time. I might have 0,1,2,4,5,7,8 as my indexes for a given pattern code that have values that match and indexes 0,2,4,5,6,8,10,11 that match on a different pattern code.

Again, try it out. You will see that my code doesn't require indexes to increment by 1.

- Jeff

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-09-05 : 10:31:07

Jeff-
You've got my nomination for "most underappreciated SQLTeam contributor" for 2003.

Any seconds?

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 11:27:50

Second that...great job...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 11:41:31

quote:
Originally posted by jsmith8858
Again, try it out. You will see that my code doesn't require indexes to increment by 1.

As soon as this (rather lengthy) query I'm running finishes I'll give it another shot.

Thanks for your help and patience.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 11:46:31

Brian,

You should be able to just cut and paste Jeff's code...

Also, I'm curious...why do you have to wait?

Aren't you using Query Analyzer?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-05 : 14:51:15

Thanks guys!

just trying to help out ... and keep busy sometimes too!

- Jeff

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 15:00:42

quote:
Originally posted by jsmith8858

Thanks guys!

just trying to help out ... and keep busy sometimes too!

Ok, I got it working this time!

Thanks very much for your advice, that definitely increased performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources