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

asked 03 Oct '13, 10:36

mfkpie8's gravatar image

mfkpie8
85424852
accept rate: 10%

edited 03 Oct '13, 10:50

Comment Text Removed

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.

(03 Oct '13, 10:46) Mark Culp
Replies hidden

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'

(03 Oct '13, 10:50) mfkpie8

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.

(03 Oct '13, 11:00) Mark Culp

YES

 
begin 
DECLARE crsr cursor using qry ;
set  qry=(select product_code from product_code where parent_id='cp' );
 begin 
 declare crsr cusRor using qry 
 open crsr
with recursive ps(product_code,description,parent_id) as
(select product_code,description,parent_id from product_code, where parent_id =crsr
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 ) close crsr; end; select * from ps end

I am a novice, don't know so write right, please correct

(03 Oct '13, 11:22) mfkpie8
(03 Oct '13, 11:48) mfkpie8

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

permanent link

answered 03 Oct '13, 11:22

mfkpie8's gravatar image

mfkpie8
85424852
accept rate: 10%

edited 04 Oct '13, 09:50

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

alt text

permanent link

answered 04 Oct '13, 10:28

mfkpie8's gravatar image

mfkpie8
85424852
accept rate: 10%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18

question asked: 03 Oct '13, 10:36

question was seen: 807 times

last updated: 04 Oct '13, 10:29