help with IIF in SQL SELECT vfp 9.0 1

[Judi201]

Hi!
I had to put that vfp9.0 in the subject. It took me too long to make the move but I love it even if it is causing me to have to revisit a lot of issues

However, this is a new problem, not a revisit.

IIF(shpcomp = 1, 0,(IIF(shptotl < duetotl, (duetotl - shptotl), 0))) AS balance ;

This line doesn't give me an error, but when the result is not 0, I get an * in the field and don't know what that is telling me. Help, anyone?

Thanks

Judi

 

[bborissov]

You got asterix, because length of the field is determined by the first records from SELECT. I would do this:

CAST(IIF(shpcomp = 1 OR (shptotl >= duetotl), 0, (duetotl - shptotl)) as N(12,2) AS balance ;

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Judi201]

Thanks Borislav,

I had to add a ) but it works beautifully and is much better than mine!

Appreciate all of your help.

Judi

 

[Judi201]

Borislav,

How would I use NOT NULL to prevent getting NULL values?
I can't get it right or would that be the way to do it?

Thanks
Judi

 

[Judi201]

Hi!

I got the statement to work; Syntax error but it didn't say that!!

Thanks

Judi

 

[bborissov]

Judi,
Where you want to use NOT NULL?
In this IIF() or in WHERE clause of the SELECT?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Judi201]

Borislav,

I was trying to use it in the IIF() as I read in help about the CAST(). I was not familiar with that.

I am coming back with another question. I wanted to do this by myself but think I will have to ask for helpl

SELECT Orders.jobsnumb AS jobsnumb, Orders.partnumb AS partnumb, ;
	Orders.ordrnumb AS ordrnumb, Orders.purnumb AS purnumb, ;
	Orders.ordrdate AS ordrdate, Orders.duetotl AS duetotl, ;
	Orders.duedate AS duedate, Orders.shptotl AS shptotl, ;
	Orders.shpdate AS shpdate, Orders.purwght AS purwght, ;
	Orders.shpcomp AS shpcomp, Orders.revdate AS revdate, Orders.custno AS custno, ;
	Orders.Purrate, Orders.purtotl,   ;
	Jobs.jobsnumb, Jobs.partstoc AS partstoc, Jobs.partstoc AS stksave, ;
	Jobs.partproc AS partproc, Jobs.partproc AS prcsave, ;  
	Customer.custno, Customer.bname, ;
	CAST(IIF(shpcomp = 1 OR (shptotl >= duetotl), 0, (duetotl - shptotl)) as N(12,2) NOT NULL) AS balance ;
	FROM  casting!Orders ;
	LEFT OUTER JOIN Casting!customer ;
	ON  Orders.custno = Customer.custno ;
	LEFT OUTER JOIN casting!jobs ;
	ON Orders.jobsnumb = jobs.jobsnumb ;
	ORDER BY Orders.jobsnumb ;
	INTO CURSOR csrtemp1

I know this is awkward but I am trying.

This gets what I need for a report except for including the following someway.

stktotl = partstoc
prctotl = partproc
ordleft = balance
*!*		IF ordleft > 0
*!*			IF ordleft > stktotl
*!*				stktotl = 0
*!*				ordleft = ordleft - stktotl
*!*			ELSE
*!*				stktotl = stktotl - ordleft
*!*				ordleft = 0
*!*			ENDIF
*!*			REPLACE partstoc WITH stktotl
*!*		ENDIF
*!*		IF ordleft > 0
*!*			IF prctotl > 0
*!*				IF ordleft > prctotl
*!*					prctotl = 0
*!*					ordleft = ordleft - prctotl
*!*				ELSE
*!*					prctotl = prctotl - ordleft
*!*					ordleft = 0
*!*				ENDIF
*!*				REPLACE partproc WITH prctotl
*!*			ENDIF
*!*		ENDIF
*!*

I know this SUPER GREAT FOX 9 will do it, I just can't seem to get it. I am trying to do a second SELECT but can't make it work.

Thanks for any help.

Judi

 

[Judi201]

Hi!

Just to close this thread, I have found a work-around for this problem.

Judi

 

[Olaf Doschke]

Hi Judi,

I see every problem already solved. But still I wanted to congratulate you to your decision to go for VFP9. Star awarded to you...

It seems you can solve at least half the questions you have by yourself now.

Bye, Olaf.