Recent

Author Topic: LazReport - using TRUNC function on NULL fields raises exception  (Read 3563 times)

LacaK

  • Hero Member
  • *****
  • Posts: 577
Hi,
is there any smart workaround how to avoid exception, when used TRUNC function on field, which has NULL value?
I have in LazReport object with expression like: [FRAC(T)+C], but when field T has null value exception is raised: "Could not convert variant of type NULL to double ..."
I want in case of T is null to return FRAC(T)=0.

sky_khan

  • Guest
Re: LazReport - using TRUNC function on NULL fields raises exception
« Reply #1 on: May 29, 2017, 10:39:19 am »
Which database ? I have not use lazreport but most databases have "coalesce" or equivalent function. I guess you can use it to convert nulls to zero at your query.

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: LazReport - using TRUNC function on NULL fields raises exception
« Reply #2 on: May 29, 2017, 11:34:10 am »
Yes in SQL SELECT I can use COALESCE(), but I look at solution at client side in LazReport ... I wonder if there is any built-in function which can handle (test) null variant values?

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: LazReport - using TRUNC function on NULL fields raises exception
« Reply #3 on: May 29, 2017, 02:47:15 pm »
Yes in SQL SELECT I can use COALESCE(), but I look at solution at client side in LazReport ... I wonder if there is any built-in function which can handle (test) null variant values?
Not to my knowledge. You can always see all the supported function by placing a text on the designer open the editor (right click\edit), press the function button and press it again on the new window. Null as a value/constant is not supported in the interpreter as far as I remember. The only choice you have for client side check is to add a custom function library in the report engine your self. I do not know if a variable can be set to return the value of a field but if it can, setting one up, might trick the interpreter to go through the field retrieval method that returns 0 when the field's value is null for numeric fields.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

jesusr

  • Sr. Member
  • ****
  • Posts: 470
Re: LazReport - using TRUNC function on NULL fields raises exception
« Reply #4 on: May 29, 2017, 04:49:58 pm »
Add unit variants to your uses clause.

at some point...
Code: Pascal  [Select]
  1.   NullStrictConvert := false;
  2.   ....
  3.  
« Last Edit: May 29, 2017, 05:08:59 pm by jesusr »

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: LazReport - using TRUNC function on NULL fields raises exception
« Reply #5 on: May 30, 2017, 07:26:08 am »
You can always see all the supported function by placing a text on the designer open the editor (right click\edit), press the function button and press it again on the new window.
Yes, I know. Btw there is not listed IF() function AFAIR

  NullStrictConvert := false;
Works! Thank you. Would it be difficult to add new built-in function like COALESCE() ?

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: LazReport - using TRUNC function on NULL fields raises exception
« Reply #6 on: May 30, 2017, 02:45:23 pm »
You can always see all the supported function by placing a text on the designer open the editor (right click\edit), press the function button and press it again on the new window.
Yes, I know. Btw there is not listed IF() function AFAIR
Works! Thank you. Would it be difficult to add new built-in function like COALESCE() ?
no IF() is not a function, if you check the script box on text object you can use if as a language construct to return different values, but you can not check for null if I remember correctly. In any case here is a quick and dirty library for lazreport cooked on the spot to showcase how easy it is to build your own libraries and extend the run time engine.
Code: [Select]
unit UEvsFRLib;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, LR_Class;
type

  { TEvsLibrary }

  TEvsLibrary = class(TfrFunctionLibrary)
  public
    constructor Create; override;
    procedure UpdateDescriptions; override;
    procedure DoFunction(FNo: Integer; p1, p2, p3: Variant; var val: Variant); override;
  end;

implementation

{ TEvsLibrary }

constructor TEvsLibrary.Create;
begin
  inherited Create;
  Add('IsNull');
end;

procedure TEvsLibrary.UpdateDescriptions;
begin
  inherited UpdateDescriptions;
  AddFunctionDesc('IsNull', 'Evosi',
                  'IsNull(<Expression> ,DefVal):Value ' +
                  'if the <Expression> returns null the contents of Defvalue is returned instead ');
end;

procedure TEvsLibrary.DoFunction(FNo: Integer; p1, p2, p3: Variant;
  var val: Variant);
begin
  case FNo of
    0 : begin //Isnull
          val := frParser.Calc(p1);
          if val = Null then val := p2;
        end;
  end;
end;

initialization
  frRegisterFunctionLibrary(TEvsLibrary);

end.


Keep in mind that the above was written by memory, it was never compiled or tested against the engine and is mend as a guide line only not as a working sample.
« Last Edit: May 30, 2017, 02:47:45 pm by taazz »
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: LazReport - using TRUNC function on NULL fields raises exception
« Reply #7 on: May 31, 2017, 07:37:01 am »
no IF() is not a function,
Yes, from technical point of view I understand, but from user point of view is IF() nowhere listed, so user does not know about it unless read some documentation

Thank you for your TfrFunctionLibrary sample. It works. Powerful!