The illustration is as follows:
Used Database: SQL Server 2000, Data Table Name: TE (Chaos Seven Bad Named)
Table Structure:
VARCHAR (20) VARCHAR (20) DECIMAL (20, 4) Assbompoint SL Equipment semi-finished products 12-year-rounded products semi-finished products 2 3-year-rounded products 3 2 semi-finished products 1 semi-finished products 4 2 semi-finished products 1 semi-finished product 5 2 semi-finished products 3 semi-finished products 5 3
Eventually the result is output
Assimompoint SL Equipment Semi-finished 23 Package Semi-finished Products 44 Poinco Parts Semi-finished Products 513
That is, a unit of finished product requires the number of bottom semi-finished products and the name of the semi-finished product. For example, semi-finished products 4 = 1 unit finished product * 2 unit semi-finished product 1 * 2 unit semi-finished products 4.
The stored procedure is as follows: (huh, you don't pay attention, I don't pay too much)
Create Procedure GetBomtruelist @ssbomname VARCHAR (20), @ Pointname Varchar (20), @ Expre Varchar (500) Asbegin Declare @SL Decimal (20, 4) Declare @Expression Varchar (500) Declare @Point Varchar (20)
Declare @BOMTOP VARCHAR (20) DECLAR #Point_Cursor Cursor Local for Select Distinct Point, SL from TE where assbom = @PointName Open #Point_Cursor
Fetch next from #point_cursor INTO @ Point, @ SL
While @@ fetch_status = 0 begin - If there is no lower node, add IF in the data table (EXISTS (SETSTS (Select Point from Te Where Assbom = @Point) Begin - If there is a lower node, then loop select @Expression again. = RTRIM (@EXPRE) '*' RTRIM (Convert (CHAR (25), @ SL)) EXEC ('getBomtruelist' @ssbomname ',' @Point '""' @Expression '" ') END Else Begin Insert Into #bomTemp Values (@ Assimname, @ Point, @ SL, @ EXPRE ' * ' LTRIM (RTRIM (Convert (CHAR (25), @ SL))) End Fetch next from #point_cursor INTO @ Point, @ SL end
Close #point_cursor deallocate #point_cursorendgo
-------------------------------------------------- -------------------------------------------------- ---
Create Procedure GetBomlistas
Begin
Create Table #bomtemp (Asshibition VARCHAR (20), Point Varchar (20), SL Decimal (20, 4), Expression Varchar (500)) Create Table #bomlast (Assbom Varchar (20), Point Varchar (20), SL Decimal (20, 4)) - Call Recursive Declare @BOMTOP VARCHAR (20) DECLARE BOM_CURSOR CURSOR for SELECT DISSBOM from TE Where Assbom NOT IN (SELECT POINT ASS)
Open bom_cursor
Fetch next from bom_cursor @BOMTOP
While @@ fetch_status = 0 Begin Exec ('getBomtruelist' @BOMTOP ',' @ BOMTOP ',' '1')
Fetch next from bom_cursor @BOMTOP END
Close Bom_Cursor Deallocate Bom_cursor
- After obtaining data and arithmetic expressions, use the expression in the data sheet to operate in declare @ssbomt varchar (20) declare @Point varchar (20) Declare @Expression varchar (500) Declare @Value Decimal (20, 4 )
Declare @execupdate varchar (500)
declare bom_cursor_end cursor for select Assbom, point, expression from #bomTemp open bom_cursor_end fetch next from bom_cursor_end into @ AssbomT, @ point, @ expression while @@ FETCH_STATUS = 0 begin set @execUpdate = 'Insert into #bomLast values (' char ( 39) RTRIM (@Assbomt) CHAR (39) ',' CHAR (39) RTRIM (@Point) CHAR (39) ',' RTRIM (@EXPRESSION) ')' --set @execupdate = 'update #bomtemp set sl =' @expression) 'WHERE assbom =' char (39) RTRIM (@ssbomt) char (39) 'and point =' char (39) RTRIM (@Point) Char (39) Exec (@execupdate) fetch next from bom_cursor_end @ assbomt, @ point, @ expression end
CLOSE BOM_CURSOR_END DEALLOCATE BOM_CURSOR_ENDSELECT Assbom, Point, Sum (SL) AS SL from #bomlast GROUP BY Assbom, Point
Drop Table #bomtemp Drop Table #bomlastend
-------------------------------------------------- -----------------------
Design ideas:
First of all, the first step is to get the top-level finished product, because there are multiple finished products in the test table, there is a need to add a cursor for top-level finished products.
After getting a finished product, then to obtain the semi-finished product of its subordinate, and get the number of semi-finished products, set the quantity to an expression, such as 1 * 3, if the semi-finished product has semi-finished products, then the same is true for the semi-finished product Handling, note that this is incorporated into a top-level finished product, and ultimately you can get the relationship between the top-level finished product to the end of the finished product, will eventually get an expression.
When all the relationships are stored in the data table, it is necessary to further process, which is calculated to calculate the expression, which is the value of the value.
Since the primary key is not taken into account when the original table is designed, use the UPDATE statement may result in calculation errors, no good way, only join a new temporary table, one line to join the record.
Finally, execute a group by the new table, it is the result of the desired.
-------------------------------------------------- -----------------------------------
problem:
This stored procedure has no way to use in VB. I don't know why, I don't know if this is a bug, which is when the RecordSet always returns an error when it is not allowed when the object is turned off. I have been more annoying, so I finally came up with a way to join a permanent table, add a list of HostID, using HostID to identify the operator's machine, and finally returns the value related to HostID.
But inside C # testing everything is normal.
-------------------------------------------------- ----------------------------------
Trouble in the design process
1, Cursor Problem: There is a red tag local during the first stored process, which is not added when writing, always prompting the cursor. After joining, the problem is solved.
2. Expression problem: After building an expression, you need to calculate, and there is no formula in SQL Server to calculate the expression, and then return the value. Eventually, a cursor has to be added.
3 It is also depressed for a long time.
-------------------------------------------------- -----------------------------------
Related 9CBS post link
click here
