MYSQL recursive loop through 2 tables in an infinite loop
$30-250 USD
Paid on delivery
Please create the MYSQL code to combine 3 tables recursively so that we can go at least 1,000 levels deep.
Essentially, we’re expanding the Bill of Materials. So, the ProdOrderRes table has the first level materials required to build the order. Then we check the Bill of materials using MAST and STPO over and over until we get to the end of the BOM and there are no more materials, basically down to the last screw.
I would do this by doing select loops on a table and then appending to that table. But, you can do it however it works. You will end up with a long table that has the order, the material(matnr), plant(werks), the quantity needed (bdmng), and BOM level.
I would write this in ABAP as follows, but I don’t know MYSQL well enough.
Select *
Into table t_header
from ProdOrderRes.
***first lines are all level one
loop at t_header assigning <head>.
<head>-level = ‘1’.
Endloop.
loop at t_header assigning <head>.
If <head>-matnr is not initial.
Sel_matnr = <head>-matnr.
Else.
***this is only for the level one materials.
Sel_matnr = <head>-plnbez.
Endif.
Select stlnr
into bom_stlnr
from mast
where matnr = sel_matnr
and werks = <head>-werks.
Select idnrk pswrk menge
Into next_matnr, next_werks, next_menge
From stop
Where stlnr = bom_stlnr.
Wa-aufnr = <head>-aufnr.
Wa-auart = <head>-auart.
Wa-autyp = <head>-autyp.
Wa-werks = next_werks.
Wa-matnr = next_matnr.
Wa-gamng = next_menge.
Wa-level = <head>-level + 1.
Append wa to t_head.
Endselect.
Endselect.
Endloop.
MYSQL attempt:
Only do this the first level
select aufnr, auart, autyp, werks, ktext, gltrs, GSTRS, GSTRI, PLNBEZ, gamng, ReqMat, ReqPlant, ReqQty, posnr
from prodorderreq;
update td1 set reqmat = plnbez;
update td1 set reqplant = werks;
Then loop through infinite times until there is no match in MAST or STPO.
select [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view] as reqmat, [url removed, login to view] as reqplant, [url removed, login to view]
from td1 t
join mast m
on [url removed, login to view] = [url removed, login to view]
and [url removed, login to view] = [url removed, login to view]
join stpo p
on [url removed, login to view] = [url removed, login to view]
INSERT INTO td1 (reqmat,reqplant) VALUES([url removed, login to view],[url removed, login to view])
endselect;
Project ID: #12640045
About the project
14 freelancers are bidding on average $175 for this job
Hello, I'm a professional programmer for web programming with php language to build the system website, Besides, I'm also expert in MySQL , HTML,HTML5,CSS, JS I'm always top in Vietnam freelancer [login to view URL] More
Hello, I am interested in your project. I am a software engineer and I have professional experience with MySQL. Thank you, Best regards, David
hi, i have very good experience about mysql recursive queries. i can easily create query for you. i worked recursive categories before.
