Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: M. Akram Bhatti
  Where is M. Akram Bhatti?
 Riyadh
 Saudi Arabia
 M. Akram Bhatti
 To: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 Tags
Subject: RE: INLIST IN SELECT STATEMENT
Thread ID: 249029 Message ID: 249031 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Sunday, December 27, 2009 10:47:17 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
>
> You can do it straight like this:
>
>
Create Cursor junk (catcode c(10))
> For lnloop = 1 To 7
> 	Insert Into junk Values ("STF00"+Transform(lnloop))
> Next
> 
> Select *, Cast (Icase(catcode="STF001","SENIOR STAFF",catcode="STF002","SENIOR STAFF",;
> 	catcode="STF003","SENIOR STAFF",catcode="STF004","JUNIOR STAFF",;
> 	catcode="STF005","JUNIOR STAFF",catcode="STF006","CLERICAL STAFF",;
> 	catcode="STF007","CLERICAL STAFF") As c(30)) As STAFF_STATUS From junk
> 
> 

>
> Or you can create a function to handle the ICASE() and place that function instead in that place:
>
>
Create Cursor junk (catcode c(10))
> For lnloop = 1 To 7
> 	Insert Into junk Values ("STF00"+Transform(lnloop))
> Next
> 
> Select *, Cast (GetStatus() as c(30)) As STAFF_STATUS From junk
> 
> ******
> FUNCTION GetStatus()
> ******
> StaffStat = Icase(catcode="STF001","SENIOR STAFF",catcode="STF002","SENIOR STAFF",;
> 	catcode="STF003","SENIOR STAFF",catcode="STF004","JUNIOR STAFF",;
> 	catcode="STF005","JUNIOR STAFF",catcode="STF006","CLERICAL STAFF",;
> 	catcode="STF007","CLERICAL STAFF")
> RETURN StaffStat

>
> Or use the BETWEEN() function to make conditions shorter:
>
Create Cursor junk (catcode c(10))
> For lnloop = 1 To 7
> 	Insert Into junk Values ("STF00"+Transform(lnloop))
> Next
> 
> Select *, Cast (Icase(Between(catcode,"STF001","STF003"),"SENIOR STAFF",;
> 	BETWEEN(catcode,"STF004","STF005"),"JUNIOR STAFF",;
> 	Between(catcode,"STF006","STF007"),"CLERICAL STAFF");
> 	As c(30)) As STAFF_STATUS From junk
> 

>
> I believe there are better ways than that.
>
> Jun Tangunan
> http://weblogs.foxite.com/sandstorm36/default.aspx
> http://www.coderisland.com/forum/viewforum.php?f=10

Jun, ICASE() is not supported in VFP 6.0

Regards,
akram

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