The following code was presented as a Function.
This was back on 23/07/2013 10:14 AM by Barry in
'elevatesoft.public.elevatedb.extensions'.

CREATE FUNCTION "LeftJustify" (IN "aValue" VARCHAR COLLATE UNI_CI, IN
"aWidth" INTEGER, IN "aFillChar" CHAR(1) COLLATE UNI_CI)
RETURNS VARCHAR COLLATE UNI_CI
BEGIN
  Declare Result VarChar default null;
  set Result = Trim(both ' ' from aValue);
  if length(Result) < aWidth then
    set Result = Result + Repeat(IFNULL(aFillChar,' ',aFillChar),
aWidth-length(Result));
  end if;
  Return Result;
END

I tried converting that as a Script but got an error.
I then changed the logic to remove the IFNULL.

This is my result, but that still has an error. The error lands on point
3 of the Description :-
ElevateDB Error #700 An error was found in the script at line 33 and
column 29 (Expected ; but instead found ' FROM aValue);

SCRIPT
BEGIN

EXECUTE IMMEDIATE 'CREATE FUNCTION "LeftJustify" (IN "aValue" VARCHAR
COLLATE UNI_CI, IN "aWidth" INTEGER, IN "aFillChar" CHAR(1) COLLATE UNI_CI)
RETURNS VARCHAR COLLATE UNI_CI
BEGIN
  Declare _Result       VarChar Default null;
  Declare _iIterate     Integer Default 0;
  Declare _FillRequired Integer Default 0;

  Set _Result = TRIM(BOTH ' ' FROM aValue);

  if ( Length(_Result) < aWidth ) then
    BEGIN
      Set _FillRequired = aWidth - Length(_Result);
      REPEAT
        Set _Result = _Result + aFillChar;
        Set _iIterate = _iIterate + 1;
      UNTIL (_iIterate > _FillRequired) END REPEAT;
    END;
  end if;

  Return _Result;
END

DESCRIPTION ''Justify a number or string to the left to a certain width.

The function accepts 3 parameters:
1) aValue is the string you wish to fill. If you want to fill a number
then you need to use Cast(MyNumber as VarChar) as the parameter.

2) aWidth is the width the result should be

3) aFillChar is an optional character that is used for filling. If
excluded, it defaults to a space. If you want to RightJustify a number,
use '0'.

This routine was provided to elevatesoft.public.elevatedb.extensions on
23/07/2013 10:14 AM by Barry as subject :-
Functions for LeftJustify(), RightJustify(), CenterJustify()   ''

VERSION 1.00';

END


What am I doing wrong here?

Regards,
  Peter Evans

Peter


The whole of the execute immediate is a quoted string and you have several single quotes in there


SCRIPT
BEGIN

DECLARE Cmnd VARCHAR;
SET Cmnd = 'CREATE FUNCTION "LeftJustify" (IN "aValue" VARCHAR, ';
SET Cmnd = Cmnd +'IN "aWidth" INTEGER, IN "aFillChar" CHAR(1) ) ';
SET Cmnd = Cmnd +'RETURNS VARCHAR  ';
SET Cmnd = Cmnd +'BEGIN ';
SET Cmnd = Cmnd +'   Declare _Result       VarChar Default null;';
SET Cmnd = Cmnd +'   Declare _iIterate     Integer Default 0;';
SET Cmnd = Cmnd +'   Declare _FillRequired Integer Default 0;';
SET Cmnd = Cmnd +'   Set _Result = TRIM(BOTH '' '' FROM aValue);';
SET Cmnd = Cmnd +'   if ( Length(_Result) < aWidth ) then ';
SET Cmnd = Cmnd +'     BEGIN ';
SET Cmnd = Cmnd +'       Set _FillRequired = aWidth - Length(_Result);';
SET Cmnd = Cmnd +'       REPEAT';
SET Cmnd = Cmnd +'         Set _Result = _Result + aFillChar;';
SET Cmnd = Cmnd +'         Set _iIterate = _iIterate + 1;';
SET Cmnd = Cmnd +'       UNTIL (_iIterate > _FillRequired) END REPEAT;';
SET Cmnd = Cmnd +'     END;';
SET Cmnd = Cmnd +'   end if;';
SET Cmnd = Cmnd +'   Return _Result; ';
SET Cmnd = Cmnd +'END'+#13;
SET Cmnd = Cmnd +'DESCRIPTION ''Justify a number or string to the left to a certain width.'+#13;
SET Cmnd = Cmnd +'The function accepts 3 parameters:'+#13;
SET Cmnd = Cmnd +'1) aValue is the string you wish to fill. If you want to fill a number'+#13;
SET Cmnd = Cmnd +'then you need to use Cast(MyNumber as VarChar) as the parameter.'+#13;
SET Cmnd = Cmnd +'2) aWidth is the width the result should be'+#13;
SET Cmnd = Cmnd +'3) aFillChar is an optional character that is used for filling. If ';
SET Cmnd = Cmnd +'excluded, it defaults to a space. If you want to RightJustify a number, ';
SET Cmnd = Cmnd +'use 0.';
SET Cmnd = Cmnd +'This routine was provided to elevatesoft.public.elevatedb.extensions on ';
SET Cmnd = Cmnd +'23/07/2013 10:14 AM by Barry as subject :-'+#13;
SET Cmnd = Cmnd +'Functions for LeftJustify(), RightJustify(), CenterJustify()'''+#13;
SET Cmnd = Cmnd +'VERSION 1.00';
EXECUTE IMMEDIATE Cmnd;

END


Because the EXECUTE IMMEDIATE is one statement EDBManager (or more likely ElevateDB) has difficulty figuring out the source of the problem so its often a good idea to split things up as I've done to isolate it.

Roy Lambert

On 6/08/2015 6:51 PM, Roy Lambert wrote:

>
> The whole of the execute immediate is a quoted string and you have several single quotes in there

It was that pesky space in the TRIM that was the problem.

I have coded it using your approach.

Thank you for that approach.

Regards,
  Peter Evans

Peter


Its the old divide and conquer ploy - I've had to use it a few times in Delphi as well.

Roy Lambert