Having problems w/ a view - SQL Server Forums

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)

Having problems w/ a view

Author

Topic

Bigced_21
Starting Member

9 Posts

Posted - 2003-09-25 : 11:45:04

ok, i have a view that extracting license # out of the field. well sometimes one record may have two license #. Is there a way I can split the records that have 2 or more license # into there own seperate record, so that there is one license# per record.

ok here's a design of what the vw does:

id License_number
12 8756
13 8757, 8758
14 8759
15 8760
16 8761, 8762

I was wondering is there a way i can take the id's that have 2 license # and seperate them so that every id has 1 license # instead of two. So after the comma i want that 2nd license # to have its own ID. Like ID's 12, 14, 15. Every ID should just 1 license # instead. is there a way I can design that.

here's the code i used to get what I have now:

CREATE VIEW dbo.vw_TransLog_Lookup
AS
SELECT ID, REPLACE(SUBSTRING(Information, CHARINDEX('|', Information) + 1, CHARINDEX('|', REPLACE(Information, ' ', ''), CHARINDEX('|',
REPLACE(Information, ' ', '')) + 1) - CHARINDEX('|', REPLACE(Information, ' ', ''))), ' ', '') AS License_Number
FROM HBC_Core.dbo.C_Payment_Transaction_Log

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-25 : 12:06:53

You will have to use a union to get the second one.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-25 : 12:28:58

Actually, it depends: is there at MOST two licenses in 1 column?

if not, use a tally table and a method like what Rob Volk describes in his article:
http://www.sqlteam.com/item.asp?ItemID=2652

give us more information about this column. are the license numbers a fixed length? is the spacing consistent? what's the most there can be? etc ...

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-25 : 14:23:04

Ok...Ok...not the most elegant of solutions...



USE Northwind
GO


CREATE TABLE myTable99 (myId int, License_numbers varchar(8000))
GO

INSERT INTO myTable99(myId, License_numbers)
SELECT 12, '8756' UNION ALL
SELECT 13, '8757, 8758' UNION ALL
SELECT 14, '8759' UNION ALL
SELECT 15, '8760' UNION ALL
SELECT 16, '8761, 8762'
GO


If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_WORDS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_WORDS]
GO

CREATE FUNCTION udf_WORDS
	(@str varchar(8000), @Del char(1)=' ')
RETURNS int
AS

/* Mimic REXX function */
BEGIN
	DECLARE @Words INT, @Pos INT, @x Int
	SELECT @Words = 0, @Pos = 1, @x = -1

	WHILE (@x <> 0)
		BEGIN
			SET @x = CHARINDEX(@Del, @str, @Pos)
			SET @Pos = @x + 1
			SET @Words = @Words + 1
		END

	RETURN @Words
END
GO


DECLARE @myId int, @License_numbers varchar(8000), @Pos INT, @x Int, @Del char(1), @Last int

SELECT * INTO #myTable99_Temp FROM myTable99 WHERE 1=0

DECLARE myCursor CURSOR
	FOR

		SELECT myID, License_numbers
		  FROM myTable99
		 WHERE Northwind.dbo.udf_WORDS(License_numbers,',') > 1

OPEN myCursor

FETCH NEXT FROM myCursor
	INTO 	  @myId
		, @License_numbers

WHILE @@FETCH_STATUS = 0
	BEGIN

	SELECT @Pos = 1, @x = -1, @Del = ',', @Last = -1

	WHILE (@x <> 0)
		BEGIN
			SET @Last = @x
			SET @x = CHARINDEX(@Del, @License_numbers, @Pos)
			IF @x <> 0 
			INSERT INTO #myTable99_Temp (myId, License_numbers) 
			     SELECT @myID
				, RTRIM(LTRIM(SUBSTRING(@License_numbers, @Pos, @x-1)))
			IF @x =  0 
			INSERT INTO #myTable99_Temp (myId, License_numbers) 
			     SELECT @myID
				, RTRIM(LTRIM(SUBSTRING(@License_numbers, @Last+1, LEN(@License_numbers)-@Last+1)))
			SET @Pos = @x + 1
		END

	FETCH NEXT FROM myCursor
		INTO 	  @myId
			, @License_numbers
	
	END
CLOSE myCursor
DEALLOCATE myCursor



SELECT myID, License_numbers
  FROM myTable99
 WHERE Northwind.dbo.udf_WORDS(License_numbers,',') = 1
UNION ALL
SELECT myID, License_numbers
  FROM #myTable99_Temp
GO


DROP TABLE #myTable99_Temp
GO
DROP TABLE myTable99
GO

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-25 : 14:36:02

Holy Shnikies Brett !!!

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-25 : 14:41:23

I'm bored....

We're moving today and everythings in boxes....

Did you cut and paste it? It all should work like a champ...

Kinda like a sledge hammer....

Was hoping to think of something more elegant....

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-25 : 14:53:05

the reason why I ask how the data is stored: if it is stored consistently and is consistent length, then the query is easy; something like:


SELECT 
  myID, substring(License_Numbers, Start, 4) as License
FROM
 myTable99
INNER JOIN
 (Select 1 as Start union select 7 as Start) B
ON
 Start < len(License_Numbers)

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-25 : 15:00:16

quote:
Originally posted by jsmith8858

if it is stored consistently and is consistent length, then the query is easy

Someone get the smelling salts for Jeff....

http://ask.yahoo.com/ask/20001206.html

quote:

SELECT
myID, substring(License_Numbers, Start, 4) as License
FROM
myTable99
INNER JOIN
(Select 1 as Start union select 7 as Start) B
ON
Start < len(License_Numbers)

But that is very clever....gotta play around with that...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources