The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.
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
86404550
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
86404550
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
86404550
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:

×16

question asked: 03 Oct '13, 10:36

question was seen: 735 times

last updated: 04 Oct '13, 10:29