merging data - 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

General SQL Server Forums

New to SQL Server Programming

merging data

Author

Topic

maglock
Starting Member

2 Posts

Posted - 2007-01-12 : 11:53:57

I have a function that returns a range of numbers and a query that returns a varchar field and an int field. I would like to replace the int field in the query with unique ranged numbers from the function(one per row).

Any ideas?

Thanks,
Mark

-- use ifms

select *
from dbo.fnRange(5101,5152)

select --distinct s.OrgKeyId, 10420 TableNumber, 5100 DetailCodeNumber, s.NewStratalabel CodeDescription, s.NewStratalabel CodeAbbreviation, 1 CodeActiveFlag, Getdate(), 0
distinct s.NewStratalabel, 5100
from _StrataLabel_xref s
Left Outer Join(select * from org_Detail_Code where tablenumber = 10420 and orgkeyid in (Select distinct OrgkeyId from _StrataLabel_xref)) o
on o.OrgKeyId = s.OrgKeyId and o.CodeDescription = s.NewStratalabel
where o.Detailcodenumber is null
order by s.NewStratalabel

5101
5102
5103
5104
5105
5106
5107
5108
5109
5110
5111
5112
5113
5114
5115
5116
5117
5118
5119
5120
5121
5122
5123
5124
5125
5126
5127
5128
5129
5130
5131
5132
5133
5134
5135
5136
5137
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
5150
5151
5152

H3Bt 5100
H3Cr 5100
H4Bt 5100
HS1Ar 5100
HS1Cr 5100
HS2Air 5100
HS2Ar 5100
HS2Br 5100
HS2Cr 5100
HS3Ai 5100
HS3Bi 5100
HS3Bit 5100
HS3Cr 5100
HS4Am 5100
HS4Bi 5100
HS4Bm 5100
HS4Bt 5100
HS4Ct 5100
HS4Dr 5100
NFrd 5100
S2Ap 5100
S2Ar 5100
S2Ax 5100
S2Bp 5100
S2Br 5100
S2Bw 5100
S2Bx 5100
S3Ap 5100
S3Aw 5100
S3Ax 5100
S3Bw 5100
S3Bx 5100
SH1Api 5100
SH1Br 5100
SH1Cr 5100
SH2Air 5100
SH2Ap 5100
SH2Ar 5100
SH2Ax 5100
SH2Bir 5100
SH2Bix 5100
SH2Bp 5100
SH2Br 5100
SH2Bw 5100
SH3Ai 5100
SH3Ap 5100
SH3Api 5100
SH3Bw 5100
SH3Cr 5100
SH3Di 5100
SH4Bm 5100
SH4Bwi 5100

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-12 : 14:30:38

You don't really need the function, you can do what you want like this:

create table #test (vcField varchar(10), intField int)
INSERT #test values ('H3Bt', 5100)
INSERT #test values ('H3Cr', 5100)
INSERT #test values ('H4Bt', 5100)
INSERT #test values ('HS1Ar', 5100)
INSERT #test values ('HS1Cr', 5100)
INSERT #test values ('HS2Air', 5100)
INSERT #test values ('HS2Ar', 5100)
INSERT #test values ('HS2Br', 5100)
INSERT #test values ('HS2Cr', 5100)
INSERT #test values ('HS3Ai', 5100)
INSERT #test values ('HS3Bi', 5100)
INSERT #test values ('HS3Bit', 5100)
INSERT #test values ('HS3Cr', 5100)
INSERT #test values ('HS4Am', 5100)
INSERT #test values ('HS4Bi', 5100)
INSERT #test values ('HS4Bm', 5100)
INSERT #test values ('HS4Bt', 5100)
INSERT #test values ('HS4Ct', 5100)
INSERT #test values ('HS4Dr', 5100)
INSERT #test values ('NFrd', 5100)
INSERT #test values ('S2Ap', 5100)
INSERT #test values ('S2Ar', 5100)
INSERT #test values ('S2Ax', 5100)
INSERT #test values ('S2Bp', 5100)
INSERT #test values ('S2Br', 5100)
INSERT #test values ('S2Bw', 5100)
INSERT #test values ('S2Bx', 5100)
INSERT #test values ('S3Ap', 5100)
INSERT #test values ('S3Aw', 5100)
INSERT #test values ('S3Ax', 5100)
INSERT #test values ('S3Bw', 5100)
INSERT #test values ('S3Bx', 5100)
INSERT #test values ('SH1Api', 5100)
INSERT #test values ('SH1Br', 5100)
INSERT #test values ('SH1Cr', 5100)
INSERT #test values ('SH2Air', 5100)
INSERT #test values ('SH2Ap', 5100)
INSERT #test values ('SH2Ar', 5100)
INSERT #test values ('SH2Ax', 5100)
INSERT #test values ('SH2Bir', 5100)
INSERT #test values ('SH2Bix', 5100)
INSERT #test values ('SH2Bp', 5100)
INSERT #test values ('SH2Br', 5100)
INSERT #test values ('SH2Bw', 5100)
INSERT #test values ('SH3Ai', 5100)
INSERT #test values ('SH3Ap', 5100)
INSERT #test values ('SH3Api', 5100)
INSERT #test values ('SH3Bw', 5100)
INSERT #test values ('SH3Cr', 5100)
INSERT #test values ('SH3Di', 5100)
INSERT #test values ('SH4Bm', 5100)
INSERT #test values ('SH4Bwi', 5100)

select vcField, (select count(*) from #test T1 where T1.vcField < T.vcField) + 5101
from #test T

Result:

vcField                
---------- ----------- 
H3Bt       5101
H3Cr       5102
H4Bt       5103
HS1Ar      5104
HS1Cr      5105
HS2Air     5106
HS2Ar      5107
HS2Br      5108
HS2Cr      5109
HS3Ai      5110
HS3Bi      5111
HS3Bit     5112
HS3Cr      5113
HS4Am      5114
HS4Bi      5115
HS4Bm      5116
HS4Bt      5117
HS4Ct      5118
HS4Dr      5119
NFrd       5120
S2Ap       5121
S2Ar       5122
S2Ax       5123
S2Bp       5124
S2Br       5125
S2Bw       5126
S2Bx       5127
S3Ap       5128
S3Aw       5129
S3Ax       5130
S3Bw       5131
S3Bx       5132
SH1Api     5133
SH1Br      5134
SH1Cr      5135
SH2Air     5136
SH2Ap      5137
SH2Ar      5138
SH2Ax      5139
SH2Bir     5140
SH2Bix     5141
SH2Bp      5142
SH2Br      5143
SH2Bw      5144
SH3Ai      5145
SH3Ap      5146
SH3Api     5147
SH3Bw      5148
SH3Cr      5149
SH3Di      5150
SH4Bm      5151
SH4Bwi     5152

(52 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-12 : 15:53:36

Or simply create a table variable with an identity that starts with 5102.
And then insert all values ordered by name and last select everything from the table variable...

Peter Larsson
Helsingborg, Sweden

maglock
Starting Member

2 Posts

Posted - 2007-01-17 : 12:33:31

Thanks all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources