The use of variable assignment or a cursor

[ximen]

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

[MarkCulp]

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.

[ximen]

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'

[MarkCulp]

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.

[ximen]

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

[ximen]

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-th...

[ximen]

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

[ximen]

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

[chris_keating]

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;

[ximen]

Thank you for your reply, I demand is each variable recursive query, if this is you as a result of the constraint

[chris_keating]

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

[ximen]

Please see my new reply, here shows my needs