Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Help with SQL statement
Message
From
11/01/1999 12:24:00
 
 
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Miscellaneous
Thread ID:
00174254
Message ID:
00174694
Views:
29
>>I have a table with 3 fields with data as follows:
>>
>><B>CLASS            CODE         DESCRIPTION</B>
>>Orientation         H         Horizontal
>>Orientation         V         Vertical
>>Orientation         I         Island
>>Orientation         S         Square
>>Geography           F         Full run
>>Geography           A         A/B split
>>Geography           R         Regional
>>Size                A         Full Page
>>Size                B         1/2 Page
>>Size                C         1/4 Page
>>Size                D         1/8 Page
>>Color               4         4 Colors
>>Color               2         2 Colors
>>Color               0         Black and White
>>Now, I need to build a table with CODE and DESCRIPTION field for each combination of 4 different classes. If my math is correct, I should have 144 records (4 Orientations X 3 Geography X 4 Sizes X 3 Colors). Both the CODE and DESCRIPTION field should contain "combined" data. An example of which would be: HFA4 (Horizontal, Full run, Full Page, 4 Colors). I'm looking for an "easy" SQL command to do this.
>
>Easiest would be to do 5 selects - the first four select out the Code and Description for each type of thing, and then a Cartesian join to create the combined code tables:
>
>
>SELECT Code,Description ;
>  FROM MyTable ;
> WHERE Class = 'Orientation' ;
>  INTO CURSOR Orient
>SELECT Code,Description ;
>  FROM MyTable ;
> WHERE Class = 'Geography' ;
>  INTO CURSOR Geog
>SELECT Code,Description ;
>  FROM MyTable ;
> WHERE Class = 'Size' ;
>  INTO CURSOR Size
>SELECT Code,Description ;
>  FROM MyTable ;
> WHERE Class = 'Color' ;
>  INTO CURSOR Color
>
>SELECT Orient.Code + Geog.Code + Size.Code +Color.Code AS Codes, ;
>       ALLTRIM(Orient.Description - ',' - ;
>               Geog.Description - ',' - ;
>               Size.Description - ',' - ;
>               Color.Description) ;
>  FROM Orient, Geog, Size, Color ;
>  INTO Cursor AllCodes ;
> ORDER BY Codes
>
Thank you. You're code works if I need to build it once or number of and names of the classes remain constant. But I need to dynamically generate these. Number and name of the classes can change anytime in which case I need to regenerate these codes. IOW, I need something more generic.
It's "my" world. You're just living in it.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform