null returned by decode is treated as empty string?

[easyblue]

Hello

I am now extend my previous software which uses MyDAC into ODAC to have support for both MySQL and Oracle.

This software is running a query, and draw a line based on query result using TeeChart components.

Due to the different handling of divided by 0, I modified the query sent to TOraQuery as

decode(nvl(value1,0),0,null,value2/value1) as result

Its corresponding query in TMyQuery is simply as:

round(value2/value1, 8 ) as result

So in both cases, I expect a null value instead of a divide by 0 alarm.

I verified by a DBGrid, in both case, I can get null value from MySQL and Oracle, since the grid cell is empty.

Then when I using following code to draw a line by passing parameters
//--------------------------------------------
void __fastcall TMainForm::MakeLineSeries(TLineSeries* &Series,TCustomChart* ParentChart,
TCustomDADataSet* pQuery, AnsiString ValueName)
{
Series=new TLineSeries(ParentChart);
Series->ParentChart =ParentChart;
Series->DataSource =pQuery;
Series->TreatNulls =tnSkip;
switch (ServerMode) {
case 1:// mysql
Series->XLabelsSource="datetime";
break;
case 2: // oracle
Series->XLabelsSource="PERIOD_START_TIME";
break;
default:
;
}
Series->Marks->Style =smsValue;
Series->Marks->Visible =false;
Series->Pointer->Visible =true;
Series->Pointer->Style =psCircle;
Series->VertAxis =aRightAxis;
Series->YValues->ValueSource = ValueName;
Series->Title =ValueName;
Series->LinePen->Width =4;

}

//--------------------------------------------

Then I found that if I am using TMyQuery, there will be no problem that null value is not displayed.
But when I switch to TOraQuery, I got alarm of
""" is not a valid float point value".

It seems that

1. instead of a null value, TOraQuery returns an empty string?
2. Or I have something wrong in my decode function?

[Plash]

Check DataType of the created field. Maybe you need to cast the value. For example:

Code: Select all

CAST(decode(nvl(value1,0),0,null,value2/value1) AS NUMBER) as result