MYSQL recursive loop through 2 tables in an infinite loop
Budget $30-250 USD
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;
Décerné à:
Hi, I have 15+ years experience in SQL development. in the past in one of my projects I have worked with Bill of material, explosion of bill of material for product requirement analysis in production planning of the Plus
14 freelances font une offre moyenne de 175 $ pour ce travail
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] Plus
HI, I can assist you with this site. Here is a sampling of the few of my work contained at my portfolio: [login to view URL] I highly value professionalism and hold myself strictly accountable to Plus
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.
I have been using various databases for over 10 years, and MySQL for the last 5. Would you be able to send me a database dump? That would make it easier for me to test. It would be helpful if your field names were i Plus
