Expressions with null |
Fernando Hartmann |
2009-09-04 13:56:24 |
The following command returns 'Infinity' but in others databases like Oracle, MS SQL, Postgresql it return 'null'
select 4 / null
My application is designed to handle this situation (null response) using nvl.
Can this be changed ?
Thanks
|
Re:Expressions with null |
HXTT Support |
2009-09-06 09:41:38 |
|
Changed. Please download the latest package.
|
Re:Re:Expressions with null |
Fernando Hartmann |
2009-09-09 06:30:51 |
The update solved the division problem , but persist for "+" and "-", the following expressions are wrong too.
select null - 4, 4 - null
|
Re:Re:Re:Expressions with null |
HXTT Support |
2009-09-09 07:02:41 |
|
Because in Xbase product, null means 0 sometimes, so that we can't let null-4=null.
|
Re:Re:Re:Re:Expressions with null |
Fernando Hartmann |
2009-09-09 07:20:09 |
Strange, in this case why was the 4 / null behavior corrected ?
|
Re:Re:Re:Re:Re:Expressions with null |
HXTT Support |
2009-09-09 07:29:33 |
>Strange, in this case why was the 4 / null behavior corrected ?
Because 4/null return Infinity is Java syntax, and Xbase doesn't allow 4/0 too.
|
Re:Re:Re:Re:Re:Re:Expressions with null |
Fernando Hartmann |
2009-09-09 10:00:23 |
but in vfp9
select cast(null - 4 as double) from tst
results null
|
Re:Re:Re:Re:Re:Re:Re:Expressions with null |
HXTT Support |
2009-09-09 16:28:37 |
>select cast(null - 4 as double) from tst
VFP supports nullable column, but other Xabase doesn't support nullable column.
|
Re:Re:Re:Re:Re:Re:Re:Re:Expressions with null |
Fernando Hartmann |
2009-09-10 12:36:43 |
Ok, I', trying to use the new behavior but found a lot of problems, the samples bellow show that problems, the only that works is the first , the others all are returning various types of errors.
select if ( null / 4.0 == 5.0 / 3.0 , 'equal', 'different') --- ok
select if ( sum(5.0) / sum(4.0) == sum(5.0) / sum(3.0) , 'equal', 'different') -- wrong response
select if ( sum(null) / sum(4.0) == sum(5.0) / sum(3.0) , 'equal', 'different') -- wrong response
select if ( null / 4.0 == sum(5.0) / sum(3.0) , 'equal', 'different') -- wrong response
select if ( null / 4.0 == 5.0 / 0.0 , 'equal', 'different') --- parse error
select if ( f2 / f3 == 5.0 / 0.0 , 'equal', 'different') from (select 1 as f1 , null as f2, 4 as f3) -- error
select if ( f2 / f3 == 5.0 / 0.0 , 'equal', 'different') from (select sum(1) as f1 , sum(null) as f2, sum(4) as f3) --error: it's our case
|
Re:Re:Re:Re:Re:Re:Re:Re:Expressions with null |
HXTT Support |
2009-09-11 06:53:17 |
|
The older package doesn't support aggregate function in SELECT expression. Please download the latest package which has supported.
|
Re:Re:Re:Re:Re:Re:Re:Re:Expressions with null |
Fernando Hartmann |
2009-09-11 09:38:07 |
Thanks for yours response, with the last version of the driver, some of my samples now run correct, but the following steal returning wrong responses, please fix these too.
select if ( sum(5.0) / sum(4.0) == sum(5.0) / sum(3.0) , 'wrong response', 'correct response') -- wrong response
select if ( sum(null) / sum(4.0) == sum(5.0) / sum(3.0) , 'wrong response', 'correct response') -- wrong response
select if ( null / 4.0 == sum(5.0) / sum(3.0) , 'wrong response', 'correct response') -- wrong response
|
Re:Re:Re:Re:Re:Re:Re:Re:Expressions with null |
HXTT Support |
2009-09-12 00:44:40 |
|
Supported. Please redownload.
|
Re:Re:Re:Re:Re:Re:Re:Re:Expressions with null |
Fernando Hartmann |
2009-09-15 18:16:40 |
|
It worked, thanks a lot.
|
Main
webmaster@hxtt.com