CASE statement within a VFP6 program

[TooMuchCoffee]

Hello all,
I'm working with Visual FoxPro 6. I've learned that I have have to do my SQL statements inside a program because if I use the view or query generator, the various joins get messed up or the field length of my IIF statements are too long. Anyway.

Here is a sample SQL (program?) that I'd like to convert some of the IIF statemnts:

CREATE SQL VIEW vewBase AS SELECT _MFD.empno AS ID,;
 	...;
  	IIF((EMPTY(_MFD.CTDate).AND.CTOD(_MFD.chdate)<CTOD('01/01/2012')).OR.(EMPTY(_MFD.CTDate).AND.CTOD('12/31/2013')-CTOD(_MFD.becamemgr)=>365.25),IIF((CTOD('12/31/2013')-CTOD(_MFD.CHDate))/365.25>=3 AND Job $ '45'.AND.YEAR(CTOD(_MFD.becamemgr))<=2012,3,IIF((CTOD('12/31/2013')-CTOD(_MFD.CHDate))/365.25>=2,2,1)),0) AS EligWks;

So that's a pretty ugly IIF statement. I haven't programmed in VFP in years and my skills are lacking. I'm looking for a resource that shows how to use a CASE statement inside of a SQL query that's in a VFP 6 program.

I keep trying something like this:

CREATE SQL VIEW vewBase AS SELECT _MFD.empno AS ID,; 
[indent][/indent]...;
[indent]DO CASE[/indent]
[indent]CASE <field> = <some value> <do some action>;[/indent]
[indent]CASE <field> = <some value> <do some action>;[/indent]
[indent]ENDCASE AS <somefieldname>;[/indent]
FROM...

... but I can't get the syntax correct. Any ideas?

Thx.
AWS

 

[mmerlinn]

First thing I noticed is your ENDCASE command. According to

http://msdn.microsoft.com/en-US/library/sbst4db5(v=vs.80).aspx

the ENDCASE command has no additional commands or functions modifying it. However, I am not sure that your use of ENDCASE (because of SQL) is wrong.

Change that and see if that makes a difference.

You might also want to visit

http://msdn.microsoft.com/en-US/library/sbst4db5(v=vs.80).aspx

and related pages for ideas.

Word of warning. I only program in FP2.6 so my observations relating to anything VFP may be incorrect especially when it relates to SQL syntax.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[GriffMG]

I am not sure you can mix a SQL with a CASE construct like that - never even considered it - although
I do think mmerlinn has misread it slightly; the FROM is a SQL term most likely not an extra CASE...ENCASE
expression.

If I absolutely had to do smoething like this, I would probably use a UDF to make it more readable - because it
looks too hard to read at the moment.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Mike Lewis]

I'm afraid you've got a misconception here.

Your "<do some action>" is out of place in a SQL Statement. The SQL statement describes the data that you want to extract. It is not like a program where you tell it to do something. For that reason, you cannot combine a DO CASE construct with a SELECT.

Also, "CASE <field>" doesn't make sense. The CASE clause must be followed by a condition.

What you ideally should use is an ICASE() function. That works in a similar way to a DO CASE, but it returns a value rather than initiate an action.

Unfortunately, ICASE() is not available in VFP 6.0. The usual solution is to simulate an ICASE() with a nested IIF(). Something like this:

SELECT .....
  IIF(<condition 1>, ;
    IIF(<conditon 2>, ;
       IIF(<condition 3>, ;
          value 1, value 2, value 3)))

As you can see, that can get quite messy.

A good alternative would be to write a function that uses DO CASE, and which returns the value you want. Then, call that function from your SELECT. But be careful. Any function you call from a SELECT must not do anything to disturb the tables that are involved in the SELECT - such as moving the record pointer, setting a filter, changing the current work area, etc. But providing you avoid those things, calling your own function to do the CASE would be the simplest solution.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

VFP SQL is not T-SQL, you don't have CASE in VFP queries, you can use a function call to a userdefined function,in which you can make use of any VFP code, including VFPs DO CASE..ENDCASE, or use nested IIF() calls. The disadvantage of using a user defined function is, you introduce the prerequisite to SET PROCEDURE TO some.prg ADDITIVE where some.prg contains the function, before the view can be used.

Bye, Olaf.

 

[Mike Lewis]

Just to amplify my suggestion of writing your own function. You said you want to achieve something like this:

DO CASE
CASE <field> = <some value> <do some action>;
CASE <field> = <some value> <do some action>;
ENDCASE AS <somefieldname>;

What I am proposing is something like this:

* Define the function
FUNCTION MyFunction
LPARAMETERS lcField
LOCAL lcRet

DO CASE
  CASE <lcField> = <some value> 
    lcRet = <some new value>
  CASE <lcField> = <some value 1> 
    lcRet = <some new value 1>
ENDCASE 

RETURN lcRec
ENDFUNC

* Then, in the code that creates the view

CREATE SQL VIEW vewBase AS SELECT _MFD.empno AS ID,;
  ... , [b]MyFunction(<Field>) AS Whatever,[/b] ;
  ....

Of course, you will need to make sure the function is available when you create the view, and whenever you use it. One way to do that is to put in a PRG file, and then SET PROCEDURE to that file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TooMuchCoffee]

Thank you all for your knowledge! It will be most helpful today as I try to tackle the issue. I'll play around with the suggestions above and see if I can get it working.

 

[TooMuchCoffee]

This is the full IIF function, yes, it's nasty, that I need to make work. It's in "pretty"/"readable" format. Normally it would be crammed all on one line. When I run this in a PRG from within the VFP application, I get "Fatal error: Exception code=C0000005". So I'll be hunting down that error, too. But If I can make it work better by using another approach (comments from above, maybe?), then I won't need to worry about troubleshooting the error.

IIF((EMPTY(_MFD.CTDate).AND.CTOD(_MFD.chdate)<CTOD('01/01/2012')).OR.(EMPTY(_MFD.CTDate).AND.CTOD('12/31/2013')-CTOD(_MFD.becamemgr)=>365.25),
		IIF((CTOD('12/31/2013')-CTOD(_MFD.CHDate))/365.25>=3.AND.Job $ '45'.AND.YEAR(CTOD('12/31/2013'))-YEAR(CTOD(_MFD.becamemgr))>=3,
			3,
			IIF((CTOD('12/31/2013')-CTOD(_MFD.CHDate))/365.25>=2.AND.Job $ '45'.AND.YEAR(CTOD('12/31/2013'))-YEAR(CTOD(_MFD.becamemgr))>=2,
				2,
				IIF((CTOD('12/31/2013')-CTOD(_MFD.CHDate))/365.25>=2.AND.Job $ '45'.AND.YEAR(CTOD('12/31/2013'))-YEAR(CTOD(_MFD.becamemgr))>=1,
					1,
					IIF((CTOD('12/31/2013')-CTOD(_MFD.CHDate))/365.25>=5,
						2,
						IIF((CTOD('12/31/2013')-CTOD(_MFD.CHDate))/365.25>=2,
							1,
							0
							)
						)
					)
				)
			)
				
		,0) AS EligWks;

 

[Mike Lewis]

You should seriously consider creating a function for this code. That will allow you to use a DO CASE / ENDCASE construct, which will be simpler and more readable (and therefore more reliable).

Just be sure to pass any required field values (becamemgr, CHDate, etc) as parameters rather than trying to access them directly from the table from within the function.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TooMuchCoffee]

Ok, thx, Mike! I appreciate your help very much.

 

[Olaf Doschke]

In the end you compute a EligWks between 0 and 3.
What I'd do is a query

SELECT fierldlist, Cast(0 as int) as EligWks,....From table(s) INTO CURSOR curTempresult READWRITE

and then update EligWks with further code, eg with UPDATEs or in a SCAN ENDSCAN loop, not within a single query computing it on the fly.

You could also simplify many of your expressions, eg YEAR(CTOD('12/31/2013')) is 2013. If it should be the last year, use YEAR(DATE())-1 and put that in a variable to use in the statement as parameter.

Bye, Olaf.