Avatar of anderdw2
anderdw2Flag for United States of America

asked on 

adding case statment with SELECT

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      
FoxPro

Avatar of undefined
Last Comment
anderdw2
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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/
ASKER CERTIFIED SOLUTION
Avatar of Cyril Joudieh
Cyril Joudieh
Flag of Lebanon image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of anderdw2
anderdw2
Flag of United States of America image

ASKER

I'm personally using VFP 7.0.   However, the company still has versions as old as 2.5
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

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, ...

Avatar of Cyril Joudieh
Cyril Joudieh
Flag of Lebanon image

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)
Avatar of anderdw2
anderdw2
Flag of United States of America image

ASKER

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

Like select concat (s.code + s.sampleid) as CodeSampleID ?
Avatar of anderdw2
anderdw2
Flag of United States of America image

ASKER

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

Visual FoxPro (VFP), and its predecessor FoxPro, is a data-centric, object-oriented, procedural, database programming language and IDE from Microsoft last released in 2007 that still has some active use due to its low cost of deployment and fairly rapid development. In 2008, Microsoft released a set of add-ons for VFP's xBase components to allow interoperability with various Microsoft technologies. It allows data processing against its native file-based data tables or database servers such as SQL Server.

11K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo