Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Kulwant Singh
  Where is Kulwant Singh?
 Shimla
 India
 Kulwant Singh
 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: 249049 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Sunday, December 27, 2009 3:39:46 PM         
   


> 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

CREATE CURSOR temp (catcode c(10))
FOR i = 1 TO 7 
INSERT INTO temp VALUES ('STF00' + ALLTRIM(STR(I)))
ENDFOR

SELECT CATCODE, IIF(VAL(RIGHT(ALLTRIM(CATCODE), 3)) < 4, PADR('SENIOR STAFF',30),IIF(VAL(RIGHT(ALLTRIM(CATCODE), 3)) < 6, 'JUNIOR STAFF', 'CLERICAL STAFF')) AS STATUS FROM TEMP


kulwant
(Help Save the TIGER. Save Wildlife. Live & let Live)

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