sql - Recursive CTE Bill of Materials -


this first question forgive me if not clear enough.

i tasked getting total cost of components assembly(bill of materials). basically, want query table holds purchase order allocation components can cost associated unposted assembly.

it gets tricky because component of assembly assembly itself, , in case need query table holds information on whether other assemblies linked main one. (i have checks in place make sure on allocation not occur, it's possible nothing has been allocated yet , that's ok) in case query purchase order allocation table components of assembly number, , add costs total parent assembly.

i'm using cte, first time, , not having luck. can possibly determine i'm doing wrong here?

the anchor pulls in components, excluding sub-assemblies, , unit costs , qty po main assembly i'm trying determine cost for.

the recursive part should pulling components, cost , qty assemblies have been 'linked' existing on bm10200_assemblyqtydetail table. if parent assembly in trannum column, trx_id of row linked assembly sub-assembly component of main assembly. 

use ht go bomcost (assembly, component, pricefrompo, qty, bomlevel) ( -- anchor member definition select asl.trx_id, asl.itemnmbr, asl.unitcost, asl.serltqty, 0 bomlevel ht.dbo.bm10400 asl asl.trx_id = 'asm0002909' union -- recursive member definition select asl.trx_id, asl.itemnmbr, asl.unitcost, asl.serltqty,     bomlevel + 1 ht.dbo.bm10400 asl inner join ht.dbo.bm10200_assemblyqtydetail bqd      on asl.trx_id = bqd.trannum inner join bomcost bc     on bqd.trx_id = bc.assembly )   -- statement executes cte select assembly, component, pricefrompo, qty, bomlevel bomcost

to reiterate, problem here returns components of main assembly sans sub-assembly related costs. components of top level. have record in linking table linking assembly sub-assembly of bm main assembly, yet not pull components assembly number. think may have recursive sections joins. appreciated!

here's data scenario. asm0002909 assembly component assembly. asm0002914 being built simultaneously asm0002909 , has 2 components. want cost of whats been received po not matter if other components necessary have not been received yet. here's should summed (serltqty * unitcost) total cost main assembly.

assembly serial lot table

yet here result i'm getting query. should running recursion step once , returning asm0002914 results. results cte

i've set sqlfiddle 2 main tables, data, , sql query i'm using still not picking subassemblies components , returning recursion limit exceeded error. http://sqlfiddle.com/#!3/bd1b98/6

i may missing something, appears join criteria precludes recursion:

inner join bomcost bc on bqd.trx_id = bc.assembly

asm0002909 doesn't equal asm0002914, no results bottom portion.

update:

with bomcost (assembly, component, pricefrompo, qty, bomlevel) ( -- anchor member definition     select asl.trx_id, asl.itemnmbr, asl.unitcost, asl.serltqty,     0 bomlevel     dbo.bm10400 asl     asl.trx_id = 'asm0002909'     union  -- recursive member definition     select asl.trx_id, asl.itemnmbr, asl.unitcost, asl.serltqty,         bomlevel + 1     dbo.bm10400 asl     inner join dbo.bm10200_assemblyqtydetail bqd          on asl.trx_id = bqd.trx_id     inner join bomcost bc         on bqd.trannum = bc.assembly  )   -- statement executes cte select distinct assembly, component, pricefrompo, qty, bomlevel bomcost

demo: sql fiddle


Comments

Post a Comment

Popular posts from this blog

php - SPIP: From Tag directly to an article -

i work spip , plugin called tagsphere works fine tags in spip. have tag groupe put 1 article tag. when click on tag in tagsphere send me before menu can see articles linked tag. want send me directly article without see tag menu before. here code model of thagsphere: <div id="tagsphere-#env{id_article}"> <ul> <boucle_mot(mots){id_groupe ?}> <li>[<a href="#url_mot">(#titre)</a>]</li> </boucle_mot> </ul> </div> anybody idea? if want skip tag page, have link directly article. <b_article> block prevents display tag if no article linked. <div id="tagsphere-#env{id_article}"> <ul> <boucle_mot(mots){id_groupe ?}> <b_article> <li> <a<boucle_article(articles){...
Read more

jquery - isAjaxRequest always return false -

i got error message 404,'model has not been saved' following code my action controller public function actionextend($id) { $model=$this->loadmodel($id); if(yii::app()->request->isajaxrequest) { $model->todate = date('y-m-d h:i:s a',(strtotime('next month',strtotime(date('y-01-d'))))); if($model->save()) { yii::app()->end(); } else { throw new chttpexception(404,'model has not been saved'); } } } the jquery call $("#buttonextend<?php echo $this->post_row;?>").click(function(e){ $.ajax({ type: "post", url: "<?php echo yii::app()->createurl('post/extend', array('id' => $data['id'])); ?>", }); }); why not ajax call ? thank help i found response, missed } , function ...
Read more

ruby on rails - In a controller spec, how to find a specific tag in the generated view? -

i know integration tests preferred need ran in controller test, i'm testing gem injecting html code in view, xhr can't run in feature spec (if can, please explain me how :) ) so rspec controller tests can assert selector present (with capybara) : response.body.should have_selector('#foobar') has_selector? call all method capybara find selector. what want last child of body , assert id in particular. afaik it's not possible have_selector. what : all('body:first-child').first.id.should == '#foobar' however, capybara dsl, defined (more or less): def all(*args) page.all(*args) end and page empty unless use visit it's integrations specs. how can use capybara all method inside rspec controller test ? i can't test right after googling seems trick def page capybara::node::simple.new(response.body) end source
Read more