HOW i CAN use of recursive query results! HELP thanks

[ximen]

My question is based on the number of vehicles "brand" sales statistics(The first layer is the "car" vehicle classification),

Please use the recursive query(Also can use the right connections), query results:

--Tbale 1:

drop table PRODUCT_CAR;
create table PRODUCT_CAR(Code varchar(100),Description  varchar(100),parent_id varchar(20))
insert into PRODUCT_CAR select * from (
 select '0'  as code, 'car'                  as Description,''   as parent_id union all
 select '1'  as code, 'BMW'                  as Description,'0'  as parent_id union all
 select '2'  as code, 'BENZ'                 as Description,'0'  as parent_id union all
 select '3'  as code, 'VW'                   as Description,'0'  as parent_id union all
 select '4'  as code, 'Hummer'               as Description,'0'  as parent_id union all
 select 'B5' as code, 'BMW 5 series'         as Description,'1'  as parent_id union all
 select 'B6' as code, 'BMW 6 series'         as Description,'1'  as parent_id union all
 select 'B7' as code, 'BMW 7 series'         as Description,'1'  as parent_id union all
 select 'Z1' as code, 'BZ Home Series'       as Description,'3'  as parent_id union all
 select 'Z2' as code, 'BZ Sercial series'    as Description,'3'  as parent_id union all
 select '1A' as code, 'BZ A series'          as Description,'Z1' as parent_id union all
 select '1B' as code, 'BZ B series'          as Description,'Z1' as parent_id union all
 select 'ZS' as code, 'BZ S series'          as Description,'Z2' as parent_id union all
 select 'V1' as code, 'VW Home Series'       as Description,'ZS' as parent_id union all
 select 'V2' as code, 'vw Sercial series'    as Description,'3'  as parent_id union all
 select 'V3' as code, 'AUDIO Sercial series' as Description,'3'  as parent_id union all
 select 'V001' as code,  'VW SUVS series'    as Description,'V1' as parent_id union all
 select 'VA01' as code,  'AD HOME Sercial'   as Description,'V3' as parent_id union all
 select 'VA02' as code,  'AD SUVS Sercial'   as Description,'V3' as parent_id union all
 select 'VA21' as code,  'AD A Sercial'      as Description,'V3' as parent_id union all
 select 'VA211' as code, 'AD Q Sercial'      as Description,'VA02' as parent_id
) a;
SELECT * FROM PRODUCT_CAR ORDER BY CODE ASC;

--Table 2

create table SEll_CAR(
   Car_Code     varchar(100),
   Description  varchar(100),
   quantity     numeric(10),
   product_code varchar(20)
);
insert into sell_car select * from (
  select 'B53001'  as Car_code , 'BMW 535 LI luxury cars' as Description, '21'   as quantity, 'B5'    as product_code union all
  select 'B75001'  as Car_code , 'BMW 750 LI XDriver'     as Description, '200'  as quantity, 'B7'    as product_code union all
  select '21101'   as Car_code , 'AD  Q7 luxury cars'     as Description, '30'   as quantity, 'Va211' as product_code union all
  select 'VA211'   as Car_code , 'AD  A6 LI luxury cars'  as Description, '2'    as quantity, 'VA01'  as product_code union all
  select 'VW1001'  as Car_code , 'VW  Cross polo  '       as Description, '100'  as quantity, 'V1'    as product_code
) b;
select * from sell_car;

My question is based on the number of vehicles "brand" sales statistics(The first layer is the "car" vehicle classification),

Please use the recursive query(Also can use the right connections), query results:

PRODUCT_CAR.code  PRODUCT_CAR.Description  sell.Sell_SUM
1                 BMW                      221
2                 BENZ                     0
3                 vw                       132
4                 Hummer                   0

[VolkerBarth]

Have you tried the suggestions given on your other question? These samples seem rather similar to your requirements...

[VolkerBarth]

yes i do but this is

@mfkpie8: Please respond with whole sentences, otherwise it's starting to feel quite useless to try to give advice...

[ximen]

You mean I suggest the same problem? Is the recursive query tree structure? Because is two tables so I should how to operate

[ximen]

Want to know the classified by car brand inquiries out of the first layer of car sales For car classification in table 1 Table 2 for the car sales quantity

[ximen]

According to the brand (layer 1) according to sales situation

[ximen]

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]

ERROR PLS Please help me to correction

    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

ERROR PLS

[ximen]

[chris_keating]

Rewriting this as WATCOM dialect will resolve the error. That dialect ends statements with semicolons, uses SET rather than SELECT to assign values to variables. There are other differences but those appear to be the ones that are relevant to this SQL.

You can use WATCOMSQL procedure to help you with the rewrite.

In the meantime, I will investigate and report back findings.

[jeff_albion]

@mfkpie8 : Many of your other associated questions seem to be various attempts at trying to solve this very specific problem, and it seems that you are getting very confused in the details and errors of implementation, which are generating more and more questions that may not be directly helping you to answer this question. Let's take this particular problem and show how you can solve these types of problems by approaching them in a piece-by-piece fashion and by breaking them out into separate steps.

In the future when posting to this forum, we will need to see:

1. A posted definition of the table / data you're currently using
2. A query that you have tried to solve that problem
3. The version and build of SQL Anywhere you're using

For this question, it took a month to discover which initial query you were trying yourself to solve this problem - many people are not keen to "give" you an answer without seeing that you have at least attempted the problem yourself. It's also difficult for people to try and track what you're specifically doing over multiple questions - if you're working on just one problem, you should try and keep all of the details of the resolution to that one question, if the details are related.

Finally, if you find that you are running into problems are are not happy with the responses or response times on the forum, we would encourage you to open a technical support case with technical support for direct assistance.

---

Here's a general strategy you could use to solve this problem:

Part #1: Create a hierarchical list of cars and associated codes, including a column that records the "top-level code" (which will be used later as an identifier to perform a SUM() aggregate over):

WITH RECURSIVE
  carlist  ( code, parent_id, tlid, description ) AS
( ( SELECT code, parent_id, code as tlid, description  -- initial subquery
      FROM product_car
     WHERE parent_id = '0' )                           -- level to start sum aggregate at
  UNION ALL
  ( SELECT p.code, p.parent_id, c.tlid, p.description  -- recursive subquery
    FROM product_car AS p JOIN carlist AS c
     ON   p.parent_id = c.code
      AND p.parent_id <> p.code) )
SELECT * FROM carlist;

This generates a list, similar to:

code,parent_id,tlid,description
'4','0','4','Hummer'
'3','0','3','VW'
'2','0','2','BENZ'
'1','0','1','BMW'
'Z1','3','3','BZ Home Series'
'Z2','3','3','BZ Sercial series'
...

This is a pretty standard recursive query, other than the slight "top-level" code modification added - there are examples of this in the documentation (as has been mentioned previously).

Part #2: Create associated quantities for each car 'type', using the first table, grouping by the 'top-level code'

We can do this by using the hierarchical list of codes we just created and using multiple table expressions, transform it into a new result set with the quantities we're looking for:

WITH RECURSIVE
  carlist  ( code, parent_id, tlid ) AS
( ( SELECT code, parent_id, code as tlid          -- initial subquery
      FROM product_car
     WHERE parent_id = '0' )                      -- level to start sum aggregate at
  UNION ALL
  ( SELECT p.code, p.parent_id, c.tlid            -- recursive subquery
    FROM product_car AS p JOIN carlist AS c
     ON   p.parent_id = c.code
      AND p.parent_id <> p.code) ),

  quantities ( tlid, qty ) AS
 (SELECT tlid, SUM(COALESCE(quantity, 0)) as qty  -- aggregate over "top-level" ids
  FROM carlist LEFT JOIN sell_car
  ON sell_car.product_code = carlist.code
  GROUP BY tlid)

SELECT code, description, quantities.qty FROM quantities, product_car
WHERE quantities.tlid = product_car.code
ORDER BY code;

The last SELECT rejoins our quantities table expression to car descriptions as we eliminated them during the SUM aggregate in the quantities table expression (and I removed the column in the earlier SELECT, for brevity). Also notice the use of the LEFT JOIN in the quantities table expression in order to maintain entries that have '0' quantities.

This query now gives you your desired result set:

1,BMW,221
2,BENZ,0
3,VW,132
4,Hummer,0

[ximen]

hi jeff albion thanks :I read your key or playing for a new logo

Why I can't upload back inside the image to upload:

When I click: browsing the selected files inside is blank

A: excuse me, do you use what chat tools (online

[jeff_albion]

If you're referring to uploading images to the forum directly, you need a reputation of '100' or more. Currently, you will need to post them in another location until you have a sufficient reputation level.

Regarding "chat tools" for obtaining support, we can only directly communicate to customers with a technical support plan. This forum only provides indirect, non-priority support assistance. If you wish to have a "direct interaction" with a technical support representative, you have to raise a new message / case / incident within your support area underneath your support plan and a support engineer will then be in contact with you to help you work on your problem.