SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Help with Algorithm
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/21/2006 :  06:27:24  Show Profile
Can somebody help with writing an SPROC that accept the input below then display the 2 output below

This is the input

16001485401060132700000013C54E51;4400000013CF5951;C00000001401EE51;A600000013C64451;


Output 1

Commercial Part no.: 160
Product Part no.: 1485
Quantity 4
Revision 01
Date Code 601
Cell code 3



OUTPUT 2
Number SerialNo

1 2700000013C54E51
2 4400000013CF5951
3 C00000001401EE51
4 A600000013C64451

Q
Yak Posting Veteran

Netherlands
76 Posts

Posted - 07/21/2006 :  06:36:50  Show Profile
You could do something with the length of a record I think.
Something like
case (len(data)>16) then do something to create Output 1 and Output 2
else create Output 2 (right(data, 16))

Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/21/2006 :  07:21:22  Show Profile
Something along these lines...

--stored procedure
create proc dbo.TestSp1 @v varchar(1000) as

select name + ' ' + value from (
          select 'Commercial Part no.:' as name, substring(@v, 1, 3) as value
union all select 'Product Part no.:', substring(@v, 5, 4)
union all select 'Quantity', substring(@v, 9, 1)
union all select 'Revision', substring(@v, 10, 2)
union all select 'Date Code', substring(@v, 13, 3)
union all select 'Cell code', substring(@v, 16, 1)) a

set @v = substring(@v, 17, len(@v)-17)
select * from dbo.Split(@v, ';')
go

--calculation
exec dbo.TestSp1 '16001485401060132700000013C54E51;4400000013CF5951;C00000001401EE51;A600000013C64451;'

/*results
------------------------- 
Commercial Part no.: 160
Product Part no.: 1485
Quantity 4
Revision 01
Date Code 601
Cell code 3

----------- ---------------------------------------------------------------------------------------------------- 
1           2700000013C54E51
2           4400000013CF5951
3           C00000001401EE51
4           A600000013C64451
*/
Note that the 'split' function is available here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648



Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/21/2006 :  07:48:09  Show Profile
Well Ryan your solution works but the problem is that
I can not individually get the Serialnumbers for me to insert it on my table.
How do I get it individual including the Quantity etc
Thanks
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/21/2006 :  08:15:28  Show Profile
Okay - it sounds like you are saying that you didn't provide the full story in your original post.

Please provide the full story of what you're trying to achieve...


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/21/2006 :  09:11:35  Show Profile
Ok I want to loop ir

Start Loop
Serial = GetSerialNumber() ' How do I get Individual Serial number ?
Insert Into SerialNumber Table
End Loop
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/21/2006 :  09:16:51  Show Profile
quote:
Originally posted by OBINNA_EKE

Ok I want to loop ir

Start Loop
Serial = GetSerialNumber() ' How do I get Individual Serial number ?
Insert Into SerialNumber Table
End Loop

No. That's not your bigger picture. What is your bigger picture? What table do you have with what data? And what table do you want create with what data? Give examples (plural) of your data and how you want to manipulate it.


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/21/2006 :  09:46:31  Show Profile
The truth is that
(1)I just want to retrieve the serial number individualy and insert into my serialnumber table
(3)Then get all those headers eg Commercial, Product Part no, Quantity ,Revision ,Date ,Cell and put it inside a table
That's the whole truth

My Table Structure is

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SerialNoTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SerialNoTable]
GO

CREATE TABLE [dbo].[SerialNoTable] (
[SerialNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Commercial] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Part no] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/21/2006 :  10:05:28  Show Profile  Visit SwePeso's Homepage
Aha! A cross join between output 1 and output 2 ?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/21/2006 :  10:18:42  Show Profile  Visit SwePeso's Homepage
Editing Ryan's solution to this maybe?
create proc dbo.TestSp1
(
	@s varchar(1000)
)
as

set nocount on

declare @t varchar(1000)

select	@t = substring(@s, 17, len(@s) - 17)

select		s.Data,
		z.CommPartNo,
		z.ProdPartNo,
		z.Quantity,
		z.Revision,
		z.DateCode,
		z.CellCode
from		(
			select	substring(@s, 1, 3) CommPartNo,
				substring(@s, 4, 5) ProdPartNo,
				substring(@s, 9, 1) Quantity,
				substring(@s, 10, 2) Revision,
				substring(@s, 12, 4) DateCode,
				substring(@s, 16, 1) CellCode
		) z
cross join	dbo.Split(@t, ';') s
call with
exec dbo.TestSp1 '16001485401060132700000013C54E51;4400000013CF5951;C00000001401EE51;A600000013C64451;'
and the output is
Data              CommPartNo  ProdPartNo  Quantity  Revision  DateCode  CellCode
----------------  ----------  ----------  --------  --------  --------  --------
2700000013C54E51  160         01485       4         01        0601      3
4400000013CF5951  160         01485       4         01        0601      3
C00000001401EE51  160         01485       4         01        0601      3
A600000013C64451  160         01485       4         01        0601      3

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 07/21/2006 10:39:35
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/24/2006 :  03:54:10  Show Profile
Somebody pls help me
I just want to retrieve the serial number individually and insert into my serialnumber table
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/24/2006 :  04:17:28  Show Profile  Visit SwePeso's Homepage
Use Ryan's solution


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/24/2006 :  05:34:42  Show Profile
What do you mean by "Use Ryan's solution"
I want to pick the Serial numeber, work on it, spit on it then insert into my table
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/24/2006 :  07:04:33  Show Profile  Visit SwePeso's Homepage
For picking serial number 1 use SUBSTRING(parameter, 17, 16).
For picking serial number 2 use SUBSTRING(parameter, 34, 16).
For picking serial number 3 use SUBSTRING(parameter, 51, 16).
For picking serial number 4 use SUBSTRING(parameter, 68, 16).


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/24/2006 :  09:00:44  Show Profile
Where do I put the code pls ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/24/2006 :  10:24:58  Show Profile  Visit SwePeso's Homepage
In your stored procedure which accepts the string in the first place.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 07/24/2006 :  11:04:28  Show Profile
Well It looks like no real help is coming
Thanks everybody for your help (80%)
I will just figure out the remaining 20% quite new to SQL 2000 thou
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/24/2006 :  11:14:08  Show Profile  Visit jsmith8858's Homepage
quote:
Originally posted by OBINNA_EKE

Well It looks like no real help is coming



Wow. Nice to see that you appreciate the efforts put in by several people to help you out. The "no real help" is what you are providing to help them help you.

- Jeff

Edited by - jsmith8858 on 07/24/2006 11:15:29
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/24/2006 :  11:34:15  Show Profile  Visit SwePeso's Homepage
Well, sometime no matter how hard you try to help, you realize the original poster is way over his/her head in the current situation anyway, and they are so stressed that they don't take any help in at all.
Maybe because they don't understand what they are trying to do, or they don't understand SQL at all.

I wonder what will happen if they actually tried one or two of the solutions provided?
Maybe they will have some insight of what the solution is doing?

I often dissect other people's solutions here at SQL Team, just to learn new way of thinking.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 07/25/2006 08:42:17
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 07/24/2006 :  11:36:37  Show Profile
If 80% is "no real help" wtf does 20% count as? No F* work at all?

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 07/25/2006 :  08:32:45  Show Profile  Visit Merkin's Homepage
quote:
Originally posted by OBINNA_EKE


I will just figure out the remaining 20% quite new to SQL 2000 thou



By "figure out the remaining 20%", do you mean "do your job" ?



Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000