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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm personally using VFP 7.0. However, the company still has versions as old as 2.5
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, ...
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, ...
ICASE was introduced in VFP 9.0.
You can use nested IIFs as well.
SELECT IIF(logical1,IIF(logical2,
For most readability use a function.
Another way.
SELECT SPACE(50) as value, ... INTO CURSOR query READWRITE
REPLACE ALL value WITH MyCase(s.class)
You can use nested IIFs as well.
SELECT IIF(logical1,IIF(logical2,
For most readability use a function.
Another way.
SELECT SPACE(50) as value, ... INTO CURSOR query READWRITE
REPLACE ALL value WITH MyCase(s.class)
ASKER
When was concat introduced? I don't see that anywhere in VFP 7.0?
Like select concat (s.code + s.sampleid) as CodeSampleID ?
Like select concat (s.code + s.sampleid) as CodeSampleID ?
ASKER
Once again, I appreciate all of the wonderful comment and suggestions!!!!!
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/