| Author |
Topic |
|
OBINNA_EKE
Posting Yak Master
234 Posts |
 Posted - 2006-07-21 : 06:27:24
|
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
76 Posts |
Posted - 2006-07-21 : 06:36:50
|
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))
|
 |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-07-21 : 07:21:22
|
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. |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-21 : 07:48:09
|
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 |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-07-21 : 08:15:28
|
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. |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-21 : 09:11:35
|
Ok I want to loop ir
Start Loop
Serial = GetSerialNumber() ' How do I get Individual Serial number ?
Insert Into SerialNumber Table
End Loop |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-07-21 : 09:16:51
|
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. |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-21 : 09:46:31
|
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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-21 : 10:05:28
|
Aha! A cross join between output 1 and output 2 ?
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-21 : 10:18:42
|
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 withexec dbo.TestSp1 '16001485401060132700000013C54E51;4400000013CF5951;C00000001401EE51;A600000013C64451;' and the output isData 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 |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-24 : 03:54:10
|
Somebody pls help me
I just want to retrieve the serial number individually and insert into my serialnumber table |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-24 : 04:17:28
|
Use Ryan's solution 
Peter Larsson
Helsingborg, Sweden |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-24 : 05:34:42
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-24 : 07:04:33
|
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 |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-24 : 09:00:44
|
Where do I put the code pls ?
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-24 : 10:24:58
|
In your stored procedure which accepts the string in the first place.
Peter Larsson
Helsingborg, Sweden |
|
|
|
OBINNA_EKE
Posting Yak Master
234 Posts |
Posted - 2006-07-24 : 11:04:28
|
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 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-07-24 : 11:14:08
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-24 : 11:34:15
|
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 |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2006-07-25 : 08:32:45
|
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 |
|
|
|
Next Page
|
Help with Algorithm