>Hi to all, I have a tricky one (at least for me), I need to create a recursive query of the same table, let me explain a little more:
>
>
>I have a table with these fields as structure and some sample data:
>Category___Manufacturer____Brand________Weight_____Characteristic1_Char2___Char3______Char4_______Char5
>Cereals_______Kellogs_________Corn Flakes__300 Gr_____Box_____________Natural_Corn_______No Def______No Def
>Sodas_________Coca Cola Comp__Coca Cola____2000 Ml____Glass___________Cola____Light______No Def______No Def
>Female Prot___Kimberly Klark__Kotex________12 Units___With Wings______Thin____with smell_Good Nights_No Def
>
>The fields characteristic 1 to 5 could be different attributes for each category, like type of package, flavor, model, etc.
>
>I have another table, with a field Character(6) that holds the order of the way that the user needs to see the information, I use six because Category, Manufacturer and Brand use the same order 0,1,2 respective.
>The users decides the order of the other parts 0 for Weight, 1 for characteristic 1, 2 for characteristic 2 and so on.
>
>I need to display a hierarchical view of the data depending of the order that the users decide, for example
>>Weight
>>>characteristic 1
>>>>characteristic 2
>>>>>characteristic 3
>>>>>>characteristic 4
>>>>>>>characteristic 5
>
>but it could be :
>>characteristic 1
>>>Weight
>>>>characteristic 5
>>>>>characteristic 4
>>>>>>characteristic 3
>>>>>>>characteristic 2
>
>it all depends of the order that the user decide, all the data to be displayed it's on the same table, so for each group I have to create a query with a more complex filter, the possible permutations of this are 720, I want to write an algorithm t use for N permutations, just in case one day they want to use more than 5 characteristics.
>
>Any help as an start point, will be really appreciated.

One approach is to build SQL SELECT statements on the fly, based on the order your users choose. The key trick is to use the AS clause on columns so your result table always looks the same regardless of the order chosen e.g.

SELECT ;
  Weight AS OrderCol1 ;
  , Char1 AS OrderCol2 ;
  , Char2 AS OrderCol3 ;
  ...
  GROUP BY ...
  ORDER BY ...

SELECT ;
  Char1 AS OrderCol1 ;
  , Weight AS OrderCol2 ;
  , Char5 AS OrderCol3 ;
  ...
  GROUP BY ...
  ORDER BY ...

If you are using VFP9 you can use CAST() to make sure your output columns are always the same type (e.g. Character).

If you do this properly you need only 1 report to display the output, regardless of the order chosen because the result is always OrderCol1 ... OrderCol6.

You may need other reports if your users select less than 6 order columns - maybe they only want 3, or maybe only 1. You would then need 1 report per possible grouping level.

One other thing I can mention - another way to deal with what you call "weight" is to split it up into 2 columns, "quantity" and "units of measure". Your sample goods would be:

Item           Qty    UoM
Corn Flakes    300    gms
Coca Cola     2000    ml
Kotex           12    each