Summing problem 1

[999Dom999]

I'm creating a web reporting app using PHP and connect to our VFP9 database with MS OLE DB Provider for VFP9.

I was previously using ODBC but it couldn't do embedded select statements so switched to OLE DB which has more functionality but I'm getting problems with the exact same query that worked fine in ODBC.

Its a very long SQL statement so I won't post it all the bit that is the problem is a simple sum that returns a 0 instead of the correct figure:

sum((quantityreq - quantityinv) * (unitval / 100)) as value //returns 0 expected 9907.2

quantityreq = 344
quantityinv = 0
unitval = 2880

The following does work :

sum((quantityreq - quantityinv) * unitval) as value //returns 990720 I just can't divide this by 100 at any part in the sum it returns a 0

sum((quantityreq - quantityinv) * (2880 / 100)) as value // returns 9907.2 as intended if I actually type the number in

I wondered if was to do with data types and changed integers to doubles using CAST but same result, any help appreciated as my head is getting very sore.

 

[Mike Lewis]

Have you by any chance got a GROUP BY clause in the SELECT statement?

The reason I ask is that there was a change in the syntax rules for GROUP BY between VFP 6.0 (which is the version supported by the ODBC driver) and later versions (supported by the OLE DB provider).

I'm not sure if it would explain the behaviour you are seeing, but it might be worth checking.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike has already gave you the argument I would also give you.

what should work out, only regarding the math of it and types is to cast the final result to double and then divide that:

Cast(sum((quantityreq - quantityinv) * unitval) as B) / Cast(100 as B)

Or

Cast(sum((quantityreq - quantityinv) * unitval) as B) / 100.0000

Bye, Olaf.

 

[999Dom999]

Hi Mike

Yes there is a GROUP BY and HAVING clause in the statement. In the ODBC version I just had just one column in the GROUP BY statement when using OLE DB I had to enter all non summed columns. I just read that this was changed in VFP8

http://support.microsoft.com/kb/813361

My statement matches the new changes.

 

[999Dom999]

Hi Olaf

Thanks for your suggestions, both return 0 (Actually I just noticed it doesn't return a zero it returns nothing) if I remove the division it works.

I can multiply it by 0.01 but then on the next line I need to divide by an exchange rate from the database. This line also returns nothing.

unitval / 100 this works fine so division works but not in conjunction with another sum?

 

[Olaf Doschke]

Do you have NULL values? I fear this doesn't take you any further, as you get a result, if not dividing.

If all else fails you could do:

Select temp.*, cast(temp.value/100.0000 as B) as value;
From ;
(Select ... ;
 sum((quantityreq - quantityinv) * unitval) as value ;
 ...) temp

What are the field types of the fields involved? I could create a sample table, if you tell us, and see where the quirk is.

Bye, Olaf.

 

[999Dom999]

Thanks Olaf for pointing me in the right direction!!!!

By playing around with CAST I managed to get the first line working then I got the exchange rate line working. I think I can shorten it further but below was my final statement. My sore head has now gone!

One more question is there a way to return only 2 decimal places?

SELECT 
			account,
			delacc,
			custname,
			itemclass, 			
			charname, 
			custpo, 
			orderno,
			model,
			description,
			sum(quantityreq - quantityinv) as quantity,
			sum(unitval) as unitvalue,
			sum((quantityreq - quantityinv) * unitval) as value,
			sum((quantityreq - quantityinv) * cast ((unitval/currencyrate)as B)) as sterling,
			duedate,
			deldate
		FROM (		
		SELECT
		IHEAD.ih_postacc as account,
		ihead.ih_account as delacc, 		
		ihead.ih_name as custname, 
		cname.cn_hclass as itemclass, 
		cname.cn_hchar as charname, 
		ihead.ih_custref as custpo, 
		ihead.ih_sorder as orderno, 
		itran.it_stock as model,
		it_desc as description,
		itran.it_quan as quantityreq, 
		itran.it_qtyinv as quantityinv,
		cast(itran.it_price/100.0000 as B) as unitval, 	
		cast(itran.it_fcrate as B) as currencyrate,
		itran.it_overall as overall,
		ihead.ih_due as duedate, 
		ITRAN.IT_DUE as deldate 
			FROM
		IHEAD JOIN ITRAN on IHEAD.ih_doc = ITRAN.it_doc 
		JOIN CNAME ON itran.it_stock = CNAME.CN_REF
		JOIN chclas ON cname.cn_hclass = chclas.cl_code		
			WHERE
					(IHEAD.ih_postacc = '101013' OR IHEAD.ih_postacc = '101031' OR IHEAD.ih_postacc = '101032' OR IHEAD.ih_postacc = '101033' OR IHEAD.ih_postacc = '101036' OR IHEAD.ih_postacc = '101037' OR IHEAD.ih_postacc = '101038' OR IHEAD.ih_postacc = '101040') AND
					 
					 
		ITRAN.it_status = 'A' AND 
		IHEAD.IH_CREDIT = '' AND
		ihead.ih_sorder != '' AND
		cl_group != 'TEXT'
		) X
			GROUP BY account,model,orderno,delacc,custname, itemclass, charname, custpo,description,duedate,deldate
			HAVING sum((quantityreq - quantityinv) * (unitval/100)) != 0 
			ORDER by account,orderno

 

[Olaf Doschke]

reegarding the precsion of 2 decimals: either stay with the double type and round the value using the ROUND() function, or switch to a numeric field with wdith and precision N(w,p).

N(w,p) works slightly different from other databases. VFP actually saves this exact precision by saving strings into the dbf. In memory this is transformed to numbers again. The width is really the string width of the numeric field, it's length in the database, that means it includes the decimal point and the sign of the number, so you need to make it 2 bytes longer than you might be used to from other databases. The precision simply is the number of decimal places after the decimal point, but be warned: an N(7,2) field can eg also store a million, using the 7 bytes of the field for the number without any decimal places, that's within the allowed range of values. And as the sign is stored only when minus, you have an uneven range into the negative direction, eg N(4,2) can store from -999 to 9999, though typically it would only be used to store values from 0.00 to 9.99, for example.

That said the code for that is as simple as

...
cast ((unitval/currencyrate)as N(X,2))
...

Where X should be 4 at least and 20 at max.

Using Round() still would be a good idea, converting the double result to N(X,2) might just cut off decimal places, I'm not sure, though.

Bye, Olaf.

Bye, Olaf.

 

[Olaf Doschke]

Another suggestion:

As the data is about currency, you might want to use the currency field type instead - Cast(value as Currency) or NTOM(value). It is, of course, meant for currency values and should actually have 4 decimal places (which is the precision banks calculate interests with, AFAIK).

If this is about INTL prices or exchange rates and you only want up to cents (or whatever the lowest currency unit is), then I suggest using Integers, storing prices in cents.

Bye, Olaf.