Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: DEREK DODOO
  Where is DEREK DODOO?
 READING, UK
 United Kingdom
 DEREK DODOO
 Tags
Subject: RE: INLIST IN SELECT STATEMENT
Thread ID: 249029 Message ID: 249037 # Views: 1 # Ratings: 1
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Sunday, December 27, 2009 11:46:42 AM         
   


> Dear Expert,
>
> A select statement to create a new field, for the output of an IIF statement.
>
> The cases are as follows
>
> When a field name 'CATCODE' is 'STF001,STF002,STF003' a variable 'SENIOR STAFF'
> when 'CATCODE' is 'STF004,STF005' a variable 'JUNIOR STAFF'
> when 'CATCODE' is 'STF006,STF007' a variable 'CLERICAL STAFF'
>
>
>
> All the outcomes i.e. SENIOR STAFF
> JUNIOR STAFF
> CLERICAL STAFF
>
> should all be in a single new field say 'STAFF_STATUS'
> to allow for reporting
>
>
> Please provide a select statement to generate the new field status with the above results.
>
> Thanks

This is a trivial case of having a lookup table that describes the office organozation. It should not be solved by various tricky or simple queries. Any change in he office organization could then be handled simply by modifiying a data table, instead of rewriting code.
 
OPEN DATABASE xxx
CREATE TABLE StaffCategories (catcode C(6) PRIMARY KEY, descript C(20))
INSERT INTO  Staffcategories VALUES ('STF001','SENIOR STAFF') 
INSERT INTO  Staffcategories VALUES ('STF002','SENIOR STAFF') 
INSERT INTO  Staffcategories VALUES ('STF003','SENIOR STAFF') 
INSERT INTO  Staffcategories VALUES ('STF004','JUNIOR STAFF') 
INSERT INTO  Staffcategories VALUES ('STF005','JUNIOER STAFF') 
INSERT INTO  Staffcategories VALUES ('STF006','CLERICAL STAFF') 
INSERT INTO  Staffcategories VALUES ('STF007','CLERICAL STAFF') 

SELECT T1.*, T2.descript ;
 FROM Mydata T1 LEFT JOIN Staffcategories T2 ON T1.catcode=T2.catcode ;
 INTO CURSOR Query1 


Even better would be to put the three category description names into three rows in a table:
 CREATE TABLE Categories (catno N(2) PRIMARY KEY, name C(20) )
 INSERT INTO Categories VALUES (1, 'Senior staff')
 INSERT INTO Categories VALUES (2, 'Junior staff')
 INSERT INTO Categories VALUES (1, 'Clerical staff') 


and change StaffCategories this way:
 CREATE TABLE Catcodes (catcode C(6) PRIMARY KEY, catno N(2) REFERENCES Categories)
 INSERT INTO  Catcodes VALUES ('STF001',1) 
 INSERT INTO  Catcodes VALUES ('STF002',1) 
 INSERT INTO  Catcodes VALUES ('STF003',1) 
 INSERT INTO  Catcodes VALUES ('STF004',2) 
 INSERT INTO  Catcodes VALUES ('STF005',2) 
 INSERT INTO  Catcodes VALUES ('STF006',3) 
 INSERT INTO  Catcodes VALUES ('STF007',3) 


And use this query:
 SELECT T1.* , T2.name FROM Mydata T1 ;
 JOIN Catcodes T0 ON T0.catcode=T1.catcode ;
 JOIN Categories T2 ON T2.catno=T0.catno   


-Anders

ENTIRE THREAD

INLIST IN SELECT STATEMENT Posted by DEREK DODOO @ 12/27/2009 10:12:30 AM
RE: INLIST IN SELECT STATEMENT Posted by Jun Tangunan @ 12/27/2009 10:34:45 AM
RE: INLIST IN SELECT STATEMENT Posted by M. Akram Bhatti @ 12/27/2009 10:47:17 AM
RE: INLIST IN SELECT STATEMENT Posted by Jun Tangunan @ 12/27/2009 10:56:32 AM
RE: INLIST IN SELECT STATEMENT Posted by M. Akram Bhatti @ 12/27/2009 11:02:41 AM
RE: INLIST IN SELECT STATEMENT Posted by Jun Tangunan @ 12/27/2009 11:07:19 AM
RE: INLIST IN SELECT STATEMENT Posted by M. Akram Bhatti @ 12/27/2009 10:53:26 AM
RE: INLIST IN SELECT STATEMENT Posted by Anders Altberg @ 12/27/2009 11:46:42 AM
RE: INLIST IN SELECT STATEMENT Posted by Jun Tangunan @ 12/27/2009 11:54:25 AM
RE: INLIST IN SELECT STATEMENT Posted by Anders Altberg @ 12/27/2009 3:24:11 PM
RE: INLIST IN SELECT STATEMENT Posted by kulwant singh @ 12/27/2009 3:39:46 PM
RE: INLIST IN SELECT STATEMENT Posted by tushar @ 12/27/2009 4:06:32 PM