Hi,
I have a serial numbers like follow and each of number is a record.
000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
000010
000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
000020
000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
000030
000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
000040
000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
000050
000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
000066
000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
000080
000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
000103 ...
Now, I use store procedure as follow and print the result
SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
73@.74@.76@.80@.85@.87@.89@.90@.92@.95
96@.100@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1X51@.55@.57X58@.60X64@.66X68@.71@.
73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
100@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1X51@.55@.57-58@.60X64@.66X68@.71@.
73-74@.76X80@.85X87@.89-90@.92@.95-96@.
100@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
> 100@.103
> Any advice, thanks!
> Angi
>|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.
000006@.000007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.
000016@.000017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.
000026@.000027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.
000036@.000037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.
000046@.000047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.
000061@.000062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.
000076@.000077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.
000092@.000095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X
96@.
> 100@.103
> Any advice, thanks!
> Angi
>|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~
000001000051@.000055@.000057000058@.
000060000064@.
000066000068@.000071@.000073000074@.
000076000080@.
000085000087@.000089000090 ....
"wei xiao" <weix@.online.microsoft.com> glsD
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1X51@.55@.57-58@.60X64@.66X68@.71@.
> 73-74@.76X80@.85X87@.89-90@.92@.95-96@.
> 100@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
>
Showing posts with label numbers. Show all posts
Showing posts with label numbers. Show all posts
Tuesday, March 6, 2012
About this store procedure..
Hi,
I have a serial numbers like follow and each of number is a record.
000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
000010
000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
000020
000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
000030
000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
000040
000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
000050
000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
000066
000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
000080
000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
000103 ...
Now, I use store procedure as follow and print the result
SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
73@.74@.76@.80@.85@.87@.89@.90@.92@.95
96@.100@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1X51@.55@.57X58@.60X64@.66X68@.71@.
73X74@.76X80@.85X87@.89X90@.92@.95X96@.
100@.103
Any advice, thanks!
Angi
Do you mean you want the result to look like this?
1X51@.55@.57-58@.60X64@.66X68@.71@.
73-74@.76X80@.85X87@.89-90@.92@.95-96@.
100@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X96@.
> 100@.103
> Any advice, thanks!
> Angi
>
|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X96@.
> 100@.103
> Any advice, thanks!
> Angi
>
|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001000051@.000055@.000057000058@.00006000 0064@.
000066000068@.000071@.000073000074@.00007600 0080@.
000085000087@.000089000090 ....
"wei xiao" <weix@.online.microsoft.com> glsD
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1X51@.55@.57-58@.60X64@.66X68@.71@.
> 73-74@.76X80@.85X87@.89-90@.92@.95-96@.
> 100@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
>
I have a serial numbers like follow and each of number is a record.
000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
000010
000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
000020
000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
000030
000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
000040
000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
000050
000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
000066
000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
000080
000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
000103 ...
Now, I use store procedure as follow and print the result
SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
73@.74@.76@.80@.85@.87@.89@.90@.92@.95
96@.100@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1X51@.55@.57X58@.60X64@.66X68@.71@.
73X74@.76X80@.85X87@.89X90@.92@.95X96@.
100@.103
Any advice, thanks!
Angi
Do you mean you want the result to look like this?
1X51@.55@.57-58@.60X64@.66X68@.71@.
73-74@.76X80@.85X87@.89-90@.92@.95-96@.
100@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X96@.
> 100@.103
> Any advice, thanks!
> Angi
>
|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001@.000002@.000003@.000004@.000005@.000006@.00 0007@.000008@.000009@.
> 000010
> 000011@.000012@.000013@.000014@.000015@.000016@.00 0017@.000018@.000019@.
> 000020
> 000021@.000022@.000023@.000024@.000025@.000026@.00 0027@.000028@.000029@.
> 000030
> 000031@.000032@.000033@.000034@.000035@.000036@.00 0037@.000038@.000039@.
> 000040
> 000041@.000042@.000043@.000044@.000045@.000046@.00 0047@.000048@.000049@.
> 000050
> 000051@.000055@.000057@.000058@.000060@.000061@.00 0062@.000063@.000064@.
> 000066
> 000067@.000068@.000071@.000073@.000074@.000076@.00 0077@.000078@.000079@.
> 000080
> 000085@.000086@.000087@.000089@.000090@.000092@.00 0095@.000096@.000100@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1@.51@.55@.57@.58@.60@.64@.66@.68@.71@.
> 73@.74@.76@.80@.85@.87@.89@.90@.92@.95
> 96@.100@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1X51@.55@.57X58@.60X64@.66X68@.71@.
> 73X74@.76X80@.85X87@.89X90@.92@.95X96@.
> 100@.103
> Any advice, thanks!
> Angi
>
|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001000051@.000055@.000057000058@.00006000 0064@.
000066000068@.000071@.000073000074@.00007600 0080@.
000085000087@.000089000090 ....
"wei xiao" <weix@.online.microsoft.com> glsD
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1X51@.55@.57-58@.60X64@.66X68@.71@.
> 73-74@.76X80@.85X87@.89-90@.92@.95-96@.
> 100@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
>
About this store procedure..
Hi,
I have a serial numbers like follow and each of number is a record.
000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
000010
000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
000020
000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
000030
000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
000040
000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
000050
000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
000066
000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
000080
000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
000103 ...
Now, I use store procedure as follow and print the result
¡°SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
¡°The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
96¡@.100¡@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
100¡@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
100¡@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
--
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '¡@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '¡ã' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '¡@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001¡ã000051¡@.000055¡@.000057¡ã000058¡@.000060¡ã000064¡@.
000066¡ã000068¡@.000071¡@.000073¡ã000074¡@.000076¡ã000080¡@.
000085¡ã000087¡@.000089¡ã000090 ....
"wei xiao" <weix@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
> 100¡@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have a serial numbers like follow and each of number is a record.
> >
> > 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> > 000010
> > 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> > 000020
> > 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> > 000030
> > 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> > 000040
> > 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> > 000050
> > 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> > 000066
> > 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> > 000080
> > 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> > 000103 ...
> >
> > Now, I use store procedure as follow and print the result
> >
> > ¡°SQL Store Procedure
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > DECLARE @.front INT
> > DECLARE @.frontsav INT
> > DECLARE @.frontbk INT
> > DECLARE @.key INT
> >
> > SET @.key = 0
> >
> > DECLARE spDashCursor CURSOR FOR
> > SELECT DISTINCT srno
> > FROM all
> > WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> > AND pqty IN ('Q','X') AND cate = 'IV'
> > ORDER BY srno
> > OPEN spDashCursor
> >
> > FETCH NEXT FROM spDashCursor INTO @.front
> > WHILE @.@.FETCH_STATUS = 0
> >
> > BEGIN
> >
> > IF @.key = 0
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > PRINT CAST(@.front AS VARCHAR(6))
> > SET @.key = @.key + 1
> > END
> > ELSE
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > END
> >
> > IF @.frontsav - @.front > 1
> > BEGIN
> > IF @.frontbk = @.front
> > BEGIN
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> > ELSE
> > BEGIN
> > PRINT CAST(@.front AS VARCHAR(6))
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> >
> > SET @.frontbk = @.frontsav
> > END
> >
> > SET @.front = @.frontsav
> > END
> >
> > CLOSE spDashCursor
> > DEALLOCATE spDashCursor
> > GO
> >
> > ¡°The Result
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> > 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> > 96¡@.100¡@.103
> >
> > So, How could I make result like follow..
> > It's mean when numbers were continuous then use "DASH" to link.
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> > 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> > 100¡@.103
> >
> > Any advice, thanks!
> > Angi
> >
> >
>
I have a serial numbers like follow and each of number is a record.
000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
000010
000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
000020
000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
000030
000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
000040
000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
000050
000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
000066
000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
000080
000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
000103 ...
Now, I use store procedure as follow and print the result
¡°SQL Store Procedure
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT srno
FROM all
WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
AND pqty IN ('Q','X') AND cate = 'IV'
ORDER BY srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
PRINT CAST(@.front AS VARCHAR(6))
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
PRINT CAST(@.frontsav AS VARCHAR(6))
END
ELSE
BEGIN
PRINT CAST(@.front AS VARCHAR(6))
PRINT CAST(@.frontsav AS VARCHAR(6))
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
¡°The Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
96¡@.100¡@.103
So, How could I make result like follow..
It's mean when numbers were continuous then use "DASH" to link.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
100¡@.103
Any advice, thanks!
AngiDo you mean you want the result to look like this?
1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
100¡@.103
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||See if this helps:
-- Finding Range
declare @.tb table(i int)
insert @.tb select 1
union all select 2
union all select 3
union all select 5
union all select 6
union all select 7
union all select 8
union all select 21
union all select 22
union all select 50
select min(i) s, max(i) e
from (
select t1.i, count(t2.i) cnt
from @.tb t1 join @.tb t2 on t1.i >= t2.i
group by t1.i
)derived
group by cnt-i
order by s
--
-oj
"angi" <enchiw@.sanrong.com.tw> wrote in message
news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a serial numbers like follow and each of number is a record.
> 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> 000010
> 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> 000020
> 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> 000030
> 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> 000040
> 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> 000050
> 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> 000066
> 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> 000080
> 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> 000103 ...
> Now, I use store procedure as follow and print the result
> ¡°SQL Store Procedure
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DECLARE @.front INT
> DECLARE @.frontsav INT
> DECLARE @.frontbk INT
> DECLARE @.key INT
> SET @.key = 0
> DECLARE spDashCursor CURSOR FOR
> SELECT DISTINCT srno
> FROM all
> WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> AND pqty IN ('Q','X') AND cate = 'IV'
> ORDER BY srno
> OPEN spDashCursor
> FETCH NEXT FROM spDashCursor INTO @.front
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF @.key = 0
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> PRINT CAST(@.front AS VARCHAR(6))
> SET @.key = @.key + 1
> END
> ELSE
> BEGIN
> FETCH NEXT FROM spDashCursor INTO @.frontsav
> END
> IF @.frontsav - @.front > 1
> BEGIN
> IF @.frontbk = @.front
> BEGIN
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> ELSE
> BEGIN
> PRINT CAST(@.front AS VARCHAR(6))
> PRINT CAST(@.frontsav AS VARCHAR(6))
> END
> SET @.frontbk = @.frontsav
> END
> SET @.front = @.frontsav
> END
> CLOSE spDashCursor
> DEALLOCATE spDashCursor
> GO
> ¡°The Result
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> 96¡@.100¡@.103
> So, How could I make result like follow..
> It's mean when numbers were continuous then use "DASH" to link.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> 100¡@.103
> Any advice, thanks!
> Angi
>|||Yes, and now I complete it, thanks!
as follow..
Store Procedure..
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @.front INT
DECLARE @.frontsav INT
DECLARE @.frontbk INT
DECLARE @.key INT
DECLARE @.test VARCHAR(7900)
SET @.key = 0
DECLARE spDashCursor CURSOR FOR
SELECT DISTINCT cs_srno
FROM all_sc
WHERE cs_orgn = '2213' AND cs_ssym = '9401' AND cs_cemk = '1'
AND cs_pqty IN ('Q','X') AND cs_cate = 'IV'
ORDER BY cs_srno
OPEN spDashCursor
FETCH NEXT FROM spDashCursor INTO @.front
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.key = 0
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
SET @.test = RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
SET @.key = @.key + 1
END
ELSE
BEGIN
FETCH NEXT FROM spDashCursor INTO @.frontsav
END
IF @.frontsav - @.front > 1
BEGIN
IF @.frontbk = @.front
BEGIN
SET @.test = @.test + '¡@.' + RIGHT('000000' + CAST(@.frontsav AS
VARCHAR(6)), 6)
END
ELSE
BEGIN
SET @.test = @.test + '¡ã' + RIGHT('000000' + CAST(@.front AS VARCHAR(6)), 6)
+ '¡@.' + RIGHT('000000' + CAST(@.frontsav AS VARCHAR(6)), 6)
END
SET @.frontbk = @.frontsav
END
SET @.front = @.frontsav
END
PRINT @.test
CLOSE spDashCursor
DEALLOCATE spDashCursor
GO
Result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
000001¡ã000051¡@.000055¡@.000057¡ã000058¡@.000060¡ã000064¡@.
000066¡ã000068¡@.000071¡@.000073¡ã000074¡@.000076¡ã000080¡@.
000085¡ã000087¡@.000089¡ã000090 ....
"wei xiao" <weix@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:#$v$cSjHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Do you mean you want the result to look like this?
> 1¡X51¡@.55¡@.57-58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> 73-74¡@.76¡X80¡@.85¡X87¡@.89-90¡@.92¡@.95-96¡@.
> 100¡@.103
>
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "angi" <enchiw@.sanrong.com.tw> wrote in message
> news:ORmSvOhHFHA.896@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have a serial numbers like follow and each of number is a record.
> >
> > 000001¡@.000002¡@.000003¡@.000004¡@.000005¡@.000006¡@.000007¡@.000008¡@.000009¡@.
> > 000010
> > 000011¡@.000012¡@.000013¡@.000014¡@.000015¡@.000016¡@.000017¡@.000018¡@.000019¡@.
> > 000020
> > 000021¡@.000022¡@.000023¡@.000024¡@.000025¡@.000026¡@.000027¡@.000028¡@.000029¡@.
> > 000030
> > 000031¡@.000032¡@.000033¡@.000034¡@.000035¡@.000036¡@.000037¡@.000038¡@.000039¡@.
> > 000040
> > 000041¡@.000042¡@.000043¡@.000044¡@.000045¡@.000046¡@.000047¡@.000048¡@.000049¡@.
> > 000050
> > 000051¡@.000055¡@.000057¡@.000058¡@.000060¡@.000061¡@.000062¡@.000063¡@.000064¡@.
> > 000066
> > 000067¡@.000068¡@.000071¡@.000073¡@.000074¡@.000076¡@.000077¡@.000078¡@.000079¡@.
> > 000080
> > 000085¡@.000086¡@.000087¡@.000089¡@.000090¡@.000092¡@.000095¡@.000096¡@.000100¡@.
> > 000103 ...
> >
> > Now, I use store procedure as follow and print the result
> >
> > ¡°SQL Store Procedure
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > DECLARE @.front INT
> > DECLARE @.frontsav INT
> > DECLARE @.frontbk INT
> > DECLARE @.key INT
> >
> > SET @.key = 0
> >
> > DECLARE spDashCursor CURSOR FOR
> > SELECT DISTINCT srno
> > FROM all
> > WHERE orgn = '2222' AND ssym = '9401' AND cemk = '1'
> > AND pqty IN ('Q','X') AND cate = 'IV'
> > ORDER BY srno
> > OPEN spDashCursor
> >
> > FETCH NEXT FROM spDashCursor INTO @.front
> > WHILE @.@.FETCH_STATUS = 0
> >
> > BEGIN
> >
> > IF @.key = 0
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > PRINT CAST(@.front AS VARCHAR(6))
> > SET @.key = @.key + 1
> > END
> > ELSE
> > BEGIN
> > FETCH NEXT FROM spDashCursor INTO @.frontsav
> > END
> >
> > IF @.frontsav - @.front > 1
> > BEGIN
> > IF @.frontbk = @.front
> > BEGIN
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> > ELSE
> > BEGIN
> > PRINT CAST(@.front AS VARCHAR(6))
> > PRINT CAST(@.frontsav AS VARCHAR(6))
> > END
> >
> > SET @.frontbk = @.frontsav
> > END
> >
> > SET @.front = @.frontsav
> > END
> >
> > CLOSE spDashCursor
> > DEALLOCATE spDashCursor
> > GO
> >
> > ¡°The Result
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡@.51¡@.55¡@.57¡@.58¡@.60¡@.64¡@.66¡@.68¡@.71¡@.
> > 73¡@.74¡@.76¡@.80¡@.85¡@.87¡@.89¡@.90¡@.92¡@.95
> > 96¡@.100¡@.103
> >
> > So, How could I make result like follow..
> > It's mean when numbers were continuous then use "DASH" to link.
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > 1¡X51¡@.55¡@.57¡X58¡@.60¡X64¡@.66¡X68¡@.71¡@.
> > 73¡X74¡@.76¡X80¡@.85¡X87¡@.89¡X90¡@.92¡@.95¡X96¡@.
> > 100¡@.103
> >
> > Any advice, thanks!
> > Angi
> >
> >
>
Sunday, February 12, 2012
About extended stored procedures
Hi,
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||There are only extended stored procedures and you would need to use EXEC to
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||On Tue, 13 Jan 2004 21:55:34 +1100
"Greg Linwood" <g_linwoodQhotmail.com> wrote:
> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might not
> be too thrilling, depending on the number of rows in the select & the amount
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, if
> that's important to you..
The performance is not important in my case because that function will be used in SELECTs with small number of rows in result set (in most cases with only one row in result set). And the logic is complex enough to have that logic in two sources - one in SQL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:
> For now you should use a UDF. When YUKON comes out, you can make your UDF in
> ainy dot net language...
:) Ooo god - no! The 'dot net' is the BIG mistake in world of programming for last 20 years ;).
> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL
I prefer to do it in xp_ and make wrapper for it in UDF (see my other post in that thread). Logic inside is too complex to have two sources to maintain - one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||There are only extended stored procedures and you would need to use EXEC to
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||On Tue, 13 Jan 2004 21:55:34 +1100
"Greg Linwood" <g_linwoodQhotmail.com> wrote:
> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might not
> be too thrilling, depending on the number of rows in the select & the amount
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, if
> that's important to you..
The performance is not important in my case because that function will be used in SELECTs with small number of rows in result set (in most cases with only one row in result set). And the logic is complex enough to have that logic in two sources - one in SQL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:
> For now you should use a UDF. When YUKON comes out, you can make your UDF in
> ainy dot net language...
:) Ooo god - no! The 'dot net' is the BIG mistake in world of programming for last 20 years ;).
> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL
I prefer to do it in xp_ and make wrapper for it in UDF (see my other post in that thread). Logic inside is too complex to have two sources to maintain - one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.
About extended stored procedures
Hi,
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
to
that
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
to
that
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
to
that
"Greg Linwood" <g_linwoodQhotmail.com> wrote:
The performance is not important in my case because that function will be us
ed in SELECTs with small number of rows in result set (in most cases with on
ly one row in result set). And the logic is complex enough to have that logi
c in two sources - one in S
QL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:
Ooo god - no! The 'dot net' is the BIG mistake in world of programming fo
r last 20 years ;).
I prefer to do it in xp_ and make wrapper for it in UDF (see my other post i
n that thread). Logic inside is too complex to have two sources to maintain
- one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
quote:
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
quote:
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
quote:|||There are only extended stored procedures and you would need to use EXEC to
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
quote:
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
quote:
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
quote:|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
quote:
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
quote:
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
quote:|||On Tue, 13 Jan 2004 21:55:34 +1100
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.
"Greg Linwood" <g_linwoodQhotmail.com> wrote:
quote:
> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might n
ot
> be too thrilling, depending on the number of rows in the select & the amou
nt
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, i
f
> that's important to you..
The performance is not important in my case because that function will be us
ed in SELECTs with small number of rows in result set (in most cases with on
ly one row in result set). And the logic is complex enough to have that logi
c in two sources - one in S
QL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:
quote:
> For now you should use a UDF. When YUKON comes out, you can make your UDF
in
> ainy dot net language...
Ooo god - no! The 'dot net' is the BIG mistake in world of programming for last 20 years ;).
quote:
> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL
I prefer to do it in xp_ and make wrapper for it in UDF (see my other post i
n that thread). Logic inside is too complex to have two sources to maintain
- one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.
Subscribe to:
Comments (Atom)