Pls, help: Bill of Materials and SQL 2005 recursion problem

/*
Hello everyone,
Thank you for taking your time to look at my problem. In our busy lives it's
hard to find time,
therefore, I really appreciate your effort.
The problem I am tryiing to resolve is this.
In the Bill of Materials structure, any idem can own components, therefore,
becoming an assembly.
Assemblies can own other assemblies. Any item can be used several times in t
he overall assembly.
However when manufacturing these assemblies, it's essential to know not only
that a part(assy) is owned by another part(assy),
but that any part(assy) is owned by a specific instance of the the parent.
In the example below, ScrewPaint is in the BOM two times and the screw is tw
o times, but the ScrewPaint on the row 11 belongs to screw instance 2.
and ScrewPaint on the row 7 of the result set, belongs to screw instance # o
ne.
The new SQL 2005 recursion is an excellent tool to process BOMs. What I try
to do is to calculate item instance and the instance of the parent within
the recursion algorithm. I am just not seeing how I can do it. I resorted to
path calculation and establishing it this way,
but it looks ugly and I don't like it.
I have make lots of futile efforts to somehow do it while recursing but my m
ind just does not see it. I am not a recursion expert yet.
Is there any way, using defined structure, to define the recursion in such a
way that I can get the final result,
i.e. calculate instance of an item and derive parent and instance on the fly
within recursion, without temp table and post-processing?
Thank you very much. Your time and knowledge is very appreciated.
Farmer.
*/
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDInstance
-- non- relevent columns
, Tree, ItemPath, ItemPathParent, [Level]
FROM @.t t
GO>"Farmer" <someone@.somewhere.com> wrote in message
>news:ugDbygkOGHA.1032@.TK2MSFTNGP11.phx.gbl...
>/*
>Hello everyone,
>Thank you for taking your time to look at my problem. In our busy lives
>it's hard to find time,
>therefore, I really appreciate your effort.
>The problem I am tryiing to resolve is this.
>In the Bill of Materials structure, any idem can own components, therefore,
>becoming an assembly.
>Assemblies can own other assemblies. Any item can be used several times in
>the overall assembly.
>However when manufacturing these assemblies, it's essential to know not
>only that a part(assy) is owned by another >part(assy),
>but that any part(assy) is owned by a specific instance of the the parent.
>In the example below, ScrewPaint is in the BOM two times and the screw is
>two times, but the ScrewPaint on the row >11 belongs to screw instance 2.
>and ScrewPaint on the row 7 of the result set, belongs to screw instance #
>one.
> . . .
How about
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
, Instance
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(50))
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY
r.itmID ) as smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.Instance + '.' + cast(Row_Number() over (order by i.itmID)
as varchar) as varchar(50))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
returns
ItmID itmIDInstance itmIDParent Instance CompTree
-- -- -- -- --
Cabinet 1 NULL 0 Cabinet
Case 1 Cabinet 0.1 Case
Door 1 Case 0.1.1 Door
SPiece 1 Door 0.1.1.1 SPiece
XPiece 1 Door 0.1.1.2 XPiece
Screw 1 Case 0.1.2 Screw
ScrewPaint 2 Screw 0.1.2.1 ScrewPaint
SideL 1 Case 0.1.3 SideL
SideR 1 Case 0.1.4 SideR
Screw 2 Cabinet 0.2 Screw
ScrewPaint 1 Screw 0.2.1 ScrewPaint
Which not only uniquely identifies each part, but also encodes where it's
located.
David|||Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Farmer" <someone@.somewhere.com> wrote in message news:ugDbygkOGHA.1032@.TK2M
SFTNGP11.phx.gbl...
/*
Hello everyone,
Thank you for taking your time to look at my problem. In our busy lives it's
hard to find time,
therefore, I really appreciate your effort.
The problem I am tryiing to resolve is this.
In the Bill of Materials structure, any idem can own components, therefore,
becoming an assembly.
Assemblies can own other assemblies. Any item can be used several times in t
he overall assembly.
However when manufacturing these assemblies, it's essential to know not only
that a part(assy) is owned by another part(assy),
but that any part(assy) is owned by a specific instance of the the parent.
In the example below, ScrewPaint is in the BOM two times and the screw is tw
o times, but the ScrewPaint on the row 11 belongs to screw instance 2.
and ScrewPaint on the row 7 of the result set, belongs to screw instance # o
ne.
The new SQL 2005 recursion is an excellent tool to process BOMs. What I try
to do is to calculate item instance and the instance of the parent within
the recursion algorithm. I am just not seeing how I can do it. I resorted to
path calculation and establishing it this way,
but it looks ugly and I don't like it.
I have make lots of futile efforts to somehow do it while recursing but my m
ind just does not see it. I am not a recursion expert yet.
Is there any way, using defined structure, to define the recursion in such a
way that I can get the final result,
i.e. calculate instance of an item and derive parent and instance on the fly
within recursion, without temp table and post-processing?
Thank you very much. Your time and knowledge is very appreciated.
Farmer.
*/
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDInstance
-- non- relevent columns
, Tree, ItemPath, ItemPathParent, [Level]
FROM @.t t
GO|||Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thank you very much Adam,
it does appeared to work as I sought it to work, but only at the begining...
Here is an updated code for my case.
Things i have learned from your code.
1. In the ancor part, the left join to components is useless (I don't know w
hy I didn't see it myself). I guess I am just too focused on my issue..., d
idn't see obvious.
2. that one can declare the second CTE and use in it the first one. I think
I read about it in the past, I just didn't think of using it. It replaces my
insert @.t temp table, i.e. making it virtual. Beautiful idea. I can also use
it in the subquery as well, i think.
3. I used ROW_RUMBER a number of times and used it in my original attempts b
ut I never considered RANK() function. It seems to be an original solution t
o use it, however it's faltering in the logic somehow. I adjusted my code wi
th your ideas. i decided to complicate it yet and added screws to more parts
making an assymbly "Screw" have more parents. That is where error came. For
example ScrewPaint instance should have a parent screw instance 1 but your
code makes it belong to instance 2 where as it belongs to instance 1 accordi
ng to the calculated path.
I am working on combination of 2 and my original code using calculated path.
I am not sure if your idea can still be taken advantage of. Do you see a wa
y?
In the nutshell here is a problem of converting BOM definition into a true t
ree, as very appropriately David Browny pointed out (prior post), encoding t
he tree. If each node where to be numbered, the component's parent IDs have
to point the right components.
Thank you for good ideas.
Vladimir Moldovanenko,
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
-- LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
,T as (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS itmIDInstan
ce,
RANK() OVER (PARTITION BY itmID ORDER BY Level) AS itmIDParentInstance
FROM itm_tree
)
-- Capture the intermedeate result set
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
, case when itmID is null then null else itmIDParentInstance end as itmIDPar
entInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P as ItemPath
, Level
FROM t
ORDER BY p
Original code below
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDParentInstance
-- non- relevent columns
, Tree as CompTree, ItemPath, ItemPathParent, [Level]
FROM @.t t
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:%23qwjQ0lOGHA.1040@.TK2MSFTNGP12.phx.gbl...
Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||For your better understanding this is a piece of acrual structure where data
will end up to be. there are more fields in it, but relevand are here.
itmID is not varchar, just for test clarity it was made.
CREATE TABLE [dbo].[OrderItems](
[itmID] [int] NOT NULL,
[itmIDInstance] [smallint] NOT NULL,
[itmIDParent] [int] NULL,
[itmIDParentInstance] [smallint] NULL,
..
[olnID] [int] NOT NULL,
[olnIDInstance] [smallint] NOT NULL,
..
CONSTRAINT [PK_OrderItems_itmID_itmIDInstance_olnID
_olnIDInstance] PRIMARY K
EY NONCLUSTERED
(
[itmID] ASC,
[itmIDInstance] ASC,
[olnID] ASC,
[olnIDInstance] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderItems] WITH CHECK ADD CONSTRAINT [FK_OrderItems_OrderItems]
FOREIGN KEY([itmIDParent], [itmIDParentInstance], [olnID], [olnIDInstance])
REFERENCES [dbo].[OrderItems] ([itmID], [itmIDInstance], [olnID], [olnIDInstance])
GO
"Farmer" <someone@.somewhere.com> wrote in message news:OSkZwxmOGHA.3896@.TK2M
SFTNGP15.phx.gbl...
Thank you very much Adam,
it does appeared to work as I sought it to work, but only at the begining...
Here is an updated code for my case.
Things i have learned from your code.
1. In the ancor part, the left join to components is useless (I don't know w
hy I didn't see it myself). I guess I am just too focused on my issue..., d
idn't see obvious.
2. that one can declare the second CTE and use in it the first one. I think
I read about it in the past, I just didn't think of using it. It replaces my
insert @.t temp table, i.e. making it virtual. Beautiful idea. I can also use
it in the subquery as well, i think.
3. I used ROW_RUMBER a number of times and used it in my original attempts b
ut I never considered RANK() function. It seems to be an original solution t
o use it, however it's faltering in the logic somehow. I adjusted my code wi
th your ideas. i decided to complicate it yet and added screws to more parts
making an assymbly "Screw" have more parents. That is where error came. For
example ScrewPaint instance should have a parent screw instance 1 but your
code makes it belong to instance 2 where as it belongs to instance 1 accordi
ng to the calculated path.
I am working on combination of 2 and my original code using calculated path.
I am not sure if your idea can still be taken advantage of. Do you see a wa
y?
In the nutshell here is a problem of converting BOM definition into a true t
ree, as very appropriately David Browny pointed out (prior post), encoding t
he tree. If each node where to be numbered, the component's parent IDs have
to point the right components.
Thank you for good ideas.
Vladimir Moldovanenko,
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
-- LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
,T as (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS itmIDInstan
ce,
RANK() OVER (PARTITION BY itmID ORDER BY Level) AS itmIDParentInstance
FROM itm_tree
)
-- Capture the intermedeate result set
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
, case when itmID is null then null else itmIDParentInstance end as itmIDPar
entInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P as ItemPath
, Level
FROM t
ORDER BY p
Original code below
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDParentInstance
-- non- relevent columns
, Tree as CompTree, ItemPath, ItemPathParent, [Level]
FROM @.t t
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:%23qwjQ0lOGHA.1040@.TK2MSFTNGP12.phx.gbl...
Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||thank you so much David,
please see my other replies to Adam's posts. Your idea is along the idea of
calculated path I have used, it's just more compressed encoded path
calculation. I like it, it's neater. This data will end up in this table. I
will try to work on incorporating your idea and dual CTE declaration
suggested by Adam and I will post the results.
Thanks again for your idea.
For your better understanding this is a piece of actual structure where data
will end up to be. there are more fields in it, but relevand are here.
itmID is not varchar, just for test clarity it was made.
CREATE TABLE [dbo].[OrderItems](
[itmID] [int] NOT NULL,
[itmIDInstance] [smallint] NOT NULL,
[itmIDParent] [int] NULL,
[itmIDParentInstance] [smallint] NULL,
..
[olnID] [int] NOT NULL,
[olnIDInstance] [smallint] NOT NULL,
..
CONSTRAINT [PK_OrderItems_itmID_itmIDInstance_olnID
_olnIDInstance] PRIMARY
KEY NONCLUSTERED
(
[itmID] ASC,
[itmIDInstance] ASC,
[olnID] ASC,
[olnIDInstance] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderItems] WITH CHECK ADD CONSTRAINT
[FK_OrderItems_OrderItems]
FOREIGN KEY([itmIDParent], [itmIDParentInstance], [olnID], [olnIDInstance])
REFERENCES [dbo].[OrderItems] ([itmID], [itmIDInstance], [olnID],
[olnIDInstance])
GO
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OU%23dUolOGHA.3856@.TK2MSFTNGP12.phx.gbl...
>
> How about
>
> -- Define recursion
> WITH itm_tree (
> CompitmID
> --, itmcQty
> , itmID
> -- , itmIDInstance
> , P
> , Level
> , Instance
> ) as
> (
> SELECT
> i.itmID
> , ic.CompitmID
> -- , cast(null as smallint) as itmIDInstance
> , CONVERT(varchar(max), cast(i.itmID as char(10))) as P
> , 1 as Level
> , cast('0' as varchar(50))
> FROM @.Items i
> LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
> WHERE i.itmID = @.itmID
> UNION ALL
> SELECT
> ic.CompitmID
> , ic.itmID
> --, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY
> r.itmID ) as smallint) as itmIDInstance
> , CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
> , r.Level + 1 as Level
> , cast(r.Instance + '.' + cast(Row_Number() over (order by
> i.itmID) as varchar) as varchar(50))
> FROM @.Items i
> JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
> JOIN itm_tree r ON r.CompitmID = ic.itmID
> )
>
> returns
> ItmID itmIDInstance itmIDParent Instance CompTree
> -- -- -- -- --
-
> Cabinet 1 NULL 0 Cabinet
> Case 1 Cabinet 0.1 Case
> Door 1 Case 0.1.1 Door
> SPiece 1 Door 0.1.1.1 SPiece
> XPiece 1 Door 0.1.1.2 XPiece
> Screw 1 Case 0.1.2 Screw
> ScrewPaint 2 Screw 0.1.2.1
> ScrewPaint
> SideL 1 Case 0.1.3 SideL
> SideR 1 Case 0.1.4 SideR
> Screw 2 Cabinet 0.2 Screw
> ScrewPaint 1 Screw 0.2.1 ScrewPaint
>
> Which not only uniquely identifies each part, but also encodes where it's
> located.
> David
>
>|||Adam and David,
thank you for your help. I think I got it.
This is what I got so far.
any better ideas? David, i used my path calculation as in my db, I have itmI
D as integer. Using str(itmID, 10) to encode is making it easier to chop the
last (delimited by position) path element.
With your calculation, I need to search for a position of the past "." in th
e string. This may require an iterative function. ? Therefore I thought th
at WHERE s2.P = left(s1.P, len(s1.P) - 10) is simpler.
You may ask me why did I go with the structure I poasted. Well, my thinking
was that int+smallint is much smaller than varchar(max) ? for FK reference a
nd usage even if it is multi-key. For me it just made sense.
Thanks again. You are both very sharp.
any optimizations you see?
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
, P2
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, convert(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(max)) P2
FROM @.Items i
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.P2 + '.' + cast(ROW_NUMBER() OVER (ORDER BY i.itmID) as varchar) a
s varchar(max))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
),tmp AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY itm_tree.CompitmID ORDER BY itm_tree.Compit
mID) AS itmIDInstance
FROM itm_tree
)
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
,(
SELECT itmIDInstance
FROM tmp s2
WHERE s2.P = left(s1.P, len(s1.P) - 10)
) as itmIDParentInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P2
, P as ItemPath
, Level
FROM tmp s1
ORDER BY p2|||But then again. Know thy functions. David, your idea can also work.
It just appers that left(s1.P, len(s1.P) - 10) is simpler than
REVERSE(P2) once, then REVERSE(case when CHARINDEX( '.', RT) > 0 then substr
ing(RT, CHARINDEX( '.', RT) + 1, LEN(RT) - CHARINDEX( '.', RT)) end) to acco
mplish the same thing.
thanks
Vladimir
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
, P2
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, convert(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(max)) P2
FROM @.Items i
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.P2 + '.' + cast(ROW_NUMBER() OVER (ORDER BY i.itmID) as varchar) a
s varchar(max))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
),tmp AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY itm_tree.CompitmID ORDER BY itm_tree.Compit
mID) AS itmIDInstance
,REVERSE(P2) as RT
FROM itm_tree
)
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
,(
SELECT itmIDInstance
FROM tmp s2
WHERE s2.P = left(s1.P, len(s1.P) - 10)
) as itmIDParentInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P2 Node
, REVERSE(case when CHARINDEX( '.', RT) > 0 then substring(RT, CHARINDEX( '.
', RT) + 1, LEN(RT) - CHARINDEX( '.', RT)) end) ParentNode
, P as ItemPath
, Level
FROM tmp s1
ORDER BY p2|||Get a copy of TREES & HIERATCHIES IN SQL and use the chapter on BOM.
You do not need recursion at all and your queries will run 1-2 orders
of magnitude faster.

pls help

Hi guys,
i appreciate the speed at which the response come and the quantity as
well.
i want an identity column with repetitions allowed after a certain
condition is reached.
actually, i want to generate invoice number automatically. it will be
an integer data type.
but the seed should initialize every time when the financial year is
changed.
for example, you have to display invoice number as 1/2005 upto
1200/2005
and we should have both of these fields different viz year and invoice
number. then we will contecate them using a slash as shown.
on the next financial year it will be changed as 1/2006 upto 1200/2006.
i want a stored procedure for this that should return an identity.
thanks and regards
Suresh BeniwalSuresh
for year you can simply use YEAR(GETDATE()) AS DEFAULT.
for invoice number, I think one should not use identity. use int and
increment in the stored procedure/client
--
Regards
R.D
--Knowledge gets doubled when shared
"SureshBeniwal" wrote:

> Hi guys,
> i appreciate the speed at which the response come and the quantity as
> well.
> i want an identity column with repetitions allowed after a certain
> condition is reached.
> actually, i want to generate invoice number automatically. it will be
> an integer data type.
> but the seed should initialize every time when the financial year is
> changed.
> for example, you have to display invoice number as 1/2005 upto
> 1200/2005
> and we should have both of these fields different viz year and invoice
> number. then we will contecate them using a slash as shown.
> on the next financial year it will be changed as 1/2006 upto 1200/2006.
> i want a stored procedure for this that should return an identity.
> thanks and regards
> Suresh Beniwal
>|||and dont be misled by the word reseed. It wont set 1 again but the one more
than max number.So it is not possible to reset back to one when new year
starts.
DBCC CHECKIDENT ('table_name') or DBCC CHECKIDENT ('table_name', RESEED)
--
Regards
R.D
--Knowledge gets doubled when shared
"SureshBeniwal" wrote:

> Hi guys,
> i appreciate the speed at which the response come and the quantity as
> well.
> i want an identity column with repetitions allowed after a certain
> condition is reached.
> actually, i want to generate invoice number automatically. it will be
> an integer data type.
> but the seed should initialize every time when the financial year is
> changed.
> for example, you have to display invoice number as 1/2005 upto
> 1200/2005
> and we should have both of these fields different viz year and invoice
> number. then we will contecate them using a slash as shown.
> on the next financial year it will be changed as 1/2006 upto 1200/2006.
> i want a stored procedure for this that should return an identity.
> thanks and regards
> Suresh Beniwal
>|||Thanks a lot, R.D.
that should help
Regards
Suresh Beniwal|||>> want to generate invoice number automatically. it will be an integer dat
a type. but the seed should initialize every time when the financial year is
changed. <<
You did check to see that it follows the conventions of your accountng
department and accounting software first? I doubt it.
Does your accounting department know that you are planning to destroy
the audit trail? IDENTITY has gaps, no check digits, etc. You need to
stop and learn how to design codes. If you want to follow this pattern
put the year first, then a sequence number, then a check digit; make
sure it is fixed length. You do not want to give away information, you
can use an additive congruential generator to get values in
pseudo-random order.

Please test search engine

Hie there.
I'm a little frenchie (froggie ? lol) and I'm just working on a Web
Search engine which I would appreciate you to test...
Here the URL
http://erwann.conan.free.fr
"Insertion d'un site" means "insert a web site"
"Recherche d'un site" means "look for a web site"
Thanks a lotWhy would primarily English-speaking (or at least EFL) SQL Server
programmers have any interest in "testing" a French search engine? Did you
also post this request to the BMW and Debian newsgroups?
"IMMO" <erwanconan83@.hotmail.com> wrote in message
news:1131583585.955363.324020@.o13g2000cwo.googlegroups.com...
> Hie there.
> I'm a little frenchie (froggie ? lol) and I'm just working on a Web
> Search engine which I would appreciate you to test...
> Here the URL
> http://erwann.conan.free.fr
> "Insertion d'un site" means "insert a web site"
> "Recherche d'un site" means "look for a web site"
> Thanks a lot
>|||Because we Americans and Brits have more experience building things... and
breaking them. ;-P
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23fsTQsZ5FHA.156@.TK2MSFTNGP15.phx.gbl...
> Why would primarily English-speaking (or at least EFL) SQL Server
> programmers have any interest in "testing" a French search engine? Did
> you also post this request to the BMW and Debian newsgroups?
>
>
>
> "IMMO" <erwanconan83@.hotmail.com> wrote in message
> news:1131583585.955363.324020@.o13g2000cwo.googlegroups.com...
>|||Good thing us Canadians are there to fix the things that you guys break. :-)
"JT" <someone@.microsoft.com> wrote in message
news:uvvEn6j5FHA.2888@.tk2msftngp13.phx.gbl...
> Because we Americans and Brits have more experience building things... and
> breaking them. ;-P
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23fsTQsZ5FHA.156@.TK2MSFTNGP15.phx.gbl...
>

Please Provide INSIGHT - why Winforms or WebParts with RS?

I would appreciate some clairvoyance on this subject:
Why is it necessary to use WINFORMS or WEBPARTS to execute a Reporting Services Report? My naive understanding is that RS is an enterprise reporting tool...
If I want to develop an OLTP application I use something different and if I need to print (via RS) I provide a button that goes to a "un" hyperlink that then provides me the rich functionality of RS.....
So it seems for sure I am missing something here - please and sinncerely give me some insight!
Best reagrds,
Joe

Sorry for the delayed posting, hopefully it can still be useful.

I'm notentirelysure I understand your post so bear with me;
In fact you don't need to use WinForms nor WebParts, you can simply provide the user a url that directly points to the report or let's say an Excel output...

Sample url for Excel output:
http://<your server>/reportserver?/<your path>/<your report name>t&rs:Command=Render&rs:Format=XML&rc:Parameters=False&rc:XSLT=<your xslt file>.xslt&rc:MIMEType=application/vnd.ms-excel&rc:FileExtension=xls
Sample url for a Report output:
http://<your server>/Reportserver?/<your path>/<your report name>&rs:Command=Render
On the other hand you can create your own custom interface to output the reports in the way you see fit, such as data-filled dropdowns or any other control. You can also have any of these controls interact with the report itself
Unfortunately that's a bit more involved and I will not detail it in this post, I may in the near future write an article on this.