adding case statment with SELECT

anderdw2
anderdw2 used Ask the Experts™
on
I had asked how to get the LEFT JOIN to work with the following statement and got a great response.  However, now I'm stuck on another issue.    s.class and s.nir are numeric identifiers that do not have an associated table as a reference.   Can I add a case statement within the select to show their textual value?

Close Data
SET EXCL OFF
SET NULL ON

SELECT   s.batch, s.code, s.class as Package_Type, s.nir as NIR_type, s.account, s.arrival, s.return, f.name as Feed_Type, s.describe, s.due, b.item as Special_Item, b.amount as Special_Amt, ;
             s.farmname, c.first, c.last, c.business, c.discount, c.pricetier, s.postage, c.billto, c.address1, c.city, c.state, c.zip, ;
             c.fax, c.phone, c.email;  
   FROM samples s ;
  INNER JOIN samplexp x ON s.sampleid = x.sampleid ;
  INNER JOIN clients c ON s.account = c.account ;
  INNER JOIN feedcode f ON s.feed_type = f.feed_type ;
  LEFT JOIN billitem b ON s.sampleid = b.sampleid ;
  WHERE s.batch = 11748 ;
    AND x.location="LANCSAMPLE"
 
COPY TO C:\lancasterTest\lancBill TYPE DELIMITED

Return      
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yes, VFP offers ICASE() function:

SELECT   s.batch, s.code, ICASE(s.class=1,"CLASS 1",s.class=2,"CLASS 2", "CLASS x") as Package_Type, ICASE(s.nir=0, "NIR 0", "Unknown NIR") as NIR_type, ...

You should also pad all text descriptions with spaces to ensure same length on output:

SELECT   s.batch, s.code, ICASE(s.class=1,"CLASS 1",s.class=2,"CLASS 2", "CLASS x") as Package_Type, ICASE(s.nir=0, PADR("NIR 0",12) , PADR("Unknown NIR",12) ) as NIR_type, ...


The latest VFP 9 SP2 help is available here: http://vfpx.codeplex.com/
Founder, Software Engineer, Data Scientist
Commented:
If you are using an older version of FoxPro you can create a User Defined Function.

SELECT MyCase(s.class), ...

FUNCTION MyCase
LPARAMETERS nClass
DO CASE
   CASE nClass = 1
      RETURN "Value 1"
   CASE nClass = 2
      RETURN "Value 2"
   ...
   CASE nClass = n
      RETURN "Value n"
   OTHERWISE
      RETURN "       "
ENDCASE

Just make sure the returns all have max size.

Author

Commented:
I'm personally using VFP 7.0.   However, the company still has versions as old as 2.5
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Hmm, in such case you have to use what Captain suggested (ICASE was introduced in VFP9).

Or nested (and ugly) IIF() functions:

SELECT   s.batch, s.code, IIF(s.class=1,"CLASS 1",IIF(s.class=2,"CLASS 2", "CLASS x")) as Package_Type, IIF(s.nir=0, "NIR 0", "Unknown NIR") as NIR_type, ...

CaptainCyrilFounder, Software Engineer, Data Scientist

Commented:
ICASE was introduced in VFP 9.0.

You can use nested IIFs as well.

SELECT IIF(logical1,IIF(logical2,,),IIF(logical3,,)) AS value, ...

For most readability use a function.

Another way.

SELECT SPACE(50) as value, ... INTO CURSOR query READWRITE
REPLACE ALL value WITH MyCase(s.class)

Author

Commented:
When was concat introduced?  I don't see that anywhere in VFP 7.0?

Like select concat (s.code + s.sampleid) as CodeSampleID ?

Author

Commented:
Once again, I appreciate all of the wonderful comment and suggestions!!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial