Thank a lot Dragan and Al.


I have to build a text file depending of the values, so I need to query every time the table but the filter condition will be being more complex, depending of the level, for example

Lparameters lnManufacter As Integer, lnBrand As Integer
lcOrder="012345" && but it can be in any permutation of the characters 720 in total
*Remember 0=Weight, 1 Characteristic 1,2 Characteristic 2, etc
Select Acts
Scan
  If Left(lcOrder,1,1)="0"
     Select Distinct Weight From Acts Where idManufact=lnManufacter And idBrand=lnBrand Into Cursor Weights ReadWrite
     Scan
        lnWeight=Acts.idWeight
        Insert Into Parameters (Text) Values (">Manufacter")
        Insert Into Parameters (Text) Values (">>Brand")
        Insert Into Parameters (Text) Values (">>>Weight")
        If Len(lcOrder)>1
          Do Case
             Case Left(lcOrder,2,1)="1"
                Select Distinct Charac1 From Acts Where idManufact=lnManufacter And idBrand=lnBrand ;
                and idWeight=lnWeight Into Cursor Characts1 ReadWrite
                Scan
                   lnChar1=Acts.idChar1
                   Insert Into Parameters (Text) Values (">Manufacter")
                   Insert Into Parameters (Text) Values (">>Brand")
                   Insert Into Parameters (Text) Values (">>>Weight")
                   Insert Into Parameters (Text) Values (">>>>Char 1")
                   If Len(lcOrder)>2
                     Do Case
                        Case Left(lcOrder,3,1)="2"
                          Select Distinct Charac2 From Acts Where idManufact=lnManufacter And idBrand=lnBrand ;
                          and idWeight=lnWeight And idChar1=lnChar1 Into Cursor Characts2 ReadWrite
                          Scan
                            lnChar2=Acts.idChar2
                            Insert Into Parameters (Text) Values (">Manufacter")
                            Insert Into Parameters (Text) Values (">>Brand")
                            Insert Into Parameters (Text) Values (">>>Weight")
                            Insert Into Parameters (Text) Values (">>>>Char 1")
                            Insert Into Parameters (Text) Values (">>>>>Char 2")
                            If Len(lcOrder)>3
&&                                 .....
                            EndIf  
                          EndScan
                        Case Left(lcOrder,3,1)="3"
                        *More Cases....
                     EndCase
                   EndIf  
                EndScan
             Case Left(lcOrder,2,1)="1"  
             *More Cases....
          EndCase
        EndIf
     EndScan
  EndIf
EndScan

That is the algorithm in a hard way, imagine I will have to write it for 720 different permutations, what I want is a way to do it for N permutations, is more clear now?

Thanks a lot for your time

Luis Navas

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