Recent

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

LacaK

  • Hero Member
  • *****
  • Posts: 691
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: 691
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: 5368
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: 484
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: 691
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: 5368
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: 691
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!

 

TinyPortal © 2005-2018