Getting Decimals from SQL Query

sfo · 2008-02-06

Hello,

I've been searching the forums for some time but I haven't been able to find a solution to my problem. Though it's pretty standard stuff. O:)

I'm making SQL Queries in my (SQL 2000) DB and getting sums from the DB.

select sum(Quantity) as SMQ, sum(Amount) as SMA from ['+CompName+'$Sales Invoice Line]  ...

I can't assign the returned values neither to an Integer neither to a Decimal as they are of Variant type:

TempA := rsRequest0.Fields.Item('SMA').Value; // Does not work

All I can do is pass them to another automation:

xlSheet.RANGE('G'+FORMAT(iCurROw)).VALUE := rsRequest0.Fields.Item('SMA').Value;

I've been trying everything I could to use the values in C/AL... Would someone have run into the same problem?

Stephan

kine · 2008-02-06

1) Which NAV version?
2) Did you tried to assign the field into variable of type "'Microsoft ActiveX Data Objects 2.7 Library'.Field" first and than assign the .Value of this variable to the target variable?

sfo · 2008-02-06

kine wrote:

1) Which NAV version?
2) Did you tried to assign the field into variable of type "'Microsoft ActiveX Data Objects 2.7 Library'.Field" first and than assign the .Value of this variable to the target variable?

Hello Kamil,

Thanks for your quick reply.

I have 3.7. I have created variables from the type you give and I can assign the variant to them. But when I assign the .Value of this .Field variable to a decimal, I have the same error message: This data type is not supported by C/SIDE. You can access data from any of the following type; VT_VOID, VT_I2, VT_I4, ...

kine · 2008-02-06

It can be possible that the result you got is NULL and than you cannot assign that. You need to be sure that result of your query has no NULL values or you need to test the value before you assign it into Decimal.

sfo · 2008-02-06

kine wrote:

It can be possible that the result you got is NULL and than you cannot assign that. You need to be sure that result of your query has no NULL values or you need to test the value before you assign it into Decimal.

Unfortunately it cannot be NULL because it's a select sum().

kine · 2008-02-06

But SUM from NULL is NULL... isn't it?

sfo · 2008-02-06

Yes but there were no NULL entries.

But you put me on the way and I found a workaround: instead of 'SELECT SUM()' I do a 'SELECT STR(SUM())' and then EVALUATE the result. THat's a bit akward but it works.

Thanks.

Howdy, Stranger!

Quick Links

Categories

Getting Decimals from SQL Query

Comments