Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 To: Kulwant Singh
  Where is Kulwant Singh?
 Shimla
 India
 Kulwant Singh
 Tags
Subject: RE: INLIST IN SELECT STATEMENT
Thread ID: 249029 Message ID: 249052 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Sunday, December 27, 2009 4:06:32 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)

When you have a part of a column that means something, then normally it should be in another column. So the catcode should be divided into 2 parts, STF and the number. In any case, as Anders has pointed out, it should link to another table, which determines the type of staff. Actually the STF part should most probably also point to another table.

Regards
Tushar

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