select case syntax in foxpro 1

[Pattycake245]

It's been a while since I used foxpro. I need the syntax for doing case statements in a select. Ie. in sql server it's

select case when state='al' then 'alabama' end
from table

so what is the syntax dto do this in foxpro, or can you not use case in a select?

Tim

 

[mspratt]

Hi Tim,

I don't think there's a CASE clause in SELECT SQL.

SELECT <fieldlist> ;
FROM <tablename> ;
WHERE UPPER(LEFT(state, 2)) = "AL"

Regards,

Mike

 

[TheRambler]

Hi,

Yes, there isn't a CASE clause in SELECT SQL that I am aware of, but you could use the IIF() function.

SELECT IIF(state="AL", "Alabama", ;
       IIF(state="CA", "California", ;
       "some other state")) ;
  FROM yourtable

 

[TamarGranor]

Actually, VFP 9 adds the ICASE() function that's analogous to IIF(). To borrow TheRambler's example:

SELECT ICASE(state="AL","Alabama", ;
             state="CA", "California", ;
             "some other state") ;
  FROM YourTable

Tamar

 

[TheRambler]

Thanks Tamar, I didn't know that. Now I see it is possible to pass up to 100 pairs of parameters for ICASE().

 

[dbMark]

TamarGranor, I gave a star because I read through all the VFP 9 new features and overlooked ICASE() or more likely promptly forgot it.

I have a tough problem over at thread1253-1013978 that so far no one's commented on. It's in a small web-related VFP forum. It deals with Web Services, VFP dll as IUSR, to VFP exe as IWAM, which then crashes with a probably rights issue when trying to APPEND GENERAL for MsGRAPH to set up a pie chart for inclusion in a report. Any help is appreciated - it's a real stumper!

 

[Mike Lewis]

Dbmark,

I have a tough problem over at Thread1253-1013978 that so far no one's commented on.

I know that it can be annoying when you post a question and no-one answers. But it's not a good idea just to repeat the question in another thread.

If there are any forum members who can help you with web services, etc., they are unlikely to see your question buried in a thread about the CASE construct. Also, for the benefit of people searching for information in the future, it is better for each subject to have its own thread with a title that indicates the nature of the discussion.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)