select @p_cp=product_code from product_code where parent_id='cp' --Put his results in the following recursive query with recursive ps(product_code,description,parent_id) as (select product_code,description,parent_id from product_code where parent_id ='@P_CP' --=(select product_code from product_code where parent_id='cp') --How do reads the results of the above, it is necessary to use vernier union all select b.product_code,b.description,b.parent_id from ps p join product_code b on p.product_code=b.parent_id ) select * from ps |
|
YES declare @product_code char(10),@rowNR int set @rownr=1 select product_code,row_number() over(order by parent_id) as rowNR,parent_id into #FT_1 from product_code where parent_id='cp' //select * from #ft_1 while (1=1) begin select @product_code=product_code from #ft_1 where rownr=@rownr if (@@rowcount=0) break with recursive ps(product_code,description,parent_id) as (select product_code,description,parent_id from product_code where parent_id=@product_code union all select b.product_code,b.description,b.parent_id from ps p join product_code b on p.product_code=b.parent_id ) select shipper.customer_id,sum(sh.quantity),description=@parentid from (((select * from ps union all select product_code,description,parent_id from product_code where product_code=@product_code ) a join part on a.product_code=part.product_code) join shipper_line sh on part.part_id=sh.part_id) join shipper on sh.trans_no=shipper.trans_no group by shipper.customer_id,description set @rownr=@rownr+1 end I am a novice, don't know so write right, please correct Why are you writing this with a cursor? Perhaps something like this would be easier: with recursive ps(product_code,description,parent_id) as (select product_code,description,parent_id from product_code where parent_id in (select product_code from product_code where parent_id='cp') union all select b.product_code,b.description,b.parent_id from ps p join product_code b on p.product_code=b.parent_id ) select * from ps;
(04 Oct '13, 09:40)
Chris Keating
Replies hidden
Thank you for your reply, I demand is each variable recursive query, if this is you as a result of the constraint
(04 Oct '13, 09:47)
mfkpie8
begin declare var char(10); set var = 'cp'; with recursive ps(product_code,description,parent_id) as (select product_code,description,parent_id from product_code where parent_id in (select product_code from product_code where parent_id= var) union all select b.product_code,b.description,b.parent_id from ps p join product_code b on p.product_code=b.parent_id ) select * from ps; end
(04 Oct '13, 10:03)
Chris Keating
Replies hidden
Please see my new reply, here shows my needs
(04 Oct '13, 10:29)
mfkpie8
|
|
Demand for: display all customers of other sales summary according to the product categories declare @product_code char(10),@rowNR int set @rownr=1 select product_code,row_number() over(order by parent_id) as rowNR,parent_id into #FT_1 from product_code where parent_id='cp' --Perform statistical line number and display finished the first layer categories //select * from #ft_1 while (1=1) begin select @product_code=product_code from #ft_1 where rownr=@rownr --assignment if (@@rowcount=0) break --How to terminate without a line with recursive ps(product_code,description,parent_id) as (select product_code,description,parent_id from product_code where parent_id=@product_code -According to the first layer of the first child of the classification tree union all select b.product_code,b.description,b.parent_id from ps p join product_code b on p.product_code=b.parent_id ) --Recursive query until the end select shipper.customer_id,sum(sh.quantity),description=@parentid from (((select * from ps --Start with sales BiaoShen customer table product category table splicing statistics according to product category sales quantity union all select product_code,description,parent_id from product_code where product_code=@product_code ) a join part on a.product_code=part.product_code) join shipper_line sh on part.part_id=sh.part_id) join shipper on sh.trans_no=shipper.trans_no group by shipper.customer_id,description set @rownr=@rownr+1 end
|
And what might your question be? It looks to me that all you need to do is remove the single quotes around your '@P_CP' (i.e. just use ... where parent_id = @p_cp ) and you've got what you need.
HI thanks mark culp :i need Put his results in the following recursive query(select product_code from product_code where parent_id='cp')to with recursive ps where parent_id ='@P_CP'
@P_CP'= select product_code from product_code where parent_id='cp'
Does the first query ( select @p_cp=product_code from product_code where parent_id='cp' ) return more than one row?
If yes, then you need to use an IN clause in your first part of your recursive query.
YES
I am a novice, don't know so write right, please correct
Can you help me sort out my train of thought? thank you for example: http://sqlanywhere-forum.sap.com/questions/18396/using-a-recursive-statistical-total-car-sales-of-the-first-layer