Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Set command in script does not work on Pervasive V11

Status
Not open for further replies.

DWest1965

Programmer
Feb 5, 2018
2
0
0
US
CREATE FUNCTION Statuteconcat:)A char(4), :B char(5), :C char(25))
RETURNS char(34)
AS
BEGIN

DECLARE :chapter char(4)
DECLARE :act char(4)
DECLARE :workspace char(4)
DECLARE :workspac2 char(4)
DECLARE :workspac3 char(5)
DECLARE :actdec char(1)
DECLARE :smallest char(34)

DECLARE :vInteger Int
DECLARE :vIntege2 Int
DECLARE :flag Int
DECLARE :fla2 Int


SET :workspace = LEFT:)B, 4)
SET :flag = 0;
SET :fla2 = 0;
SET :vInteger = 1;
SET :vIntege2 = 1;
SET :workspac2 = '';
SET :workspac3 = '';


TestLoop:
LOOP

IF :)vInteger > 4) THEN
LEAVE TestLoop;
END IF;

SET :workspace = SUBSTRING:)A, :vInteger, 1);
IF :)workspace = 0 and :flag = 0) THEN
SET :workspac2 = :workspac2 + '';
ELSE
SET :flag = 1;
SET :workspac2 = :workspac2 + :workspace;
END IF;
SET :vInteger = :vInteger + 1;
IF :)vInteger = 5 and :workspac2 > '') THEN
SET :workspac2 = :workspac2 + '-';
END IF;

END LOOP;

SET :vInteger = 1;


TestLoop:
LOOP

IF :)vInteger > 4) THEN
LEAVE TestLoop;
END IF;

SET :workspace = SUBSTRING:)B, :vInteger, 1);
IF :)workspace = 0 and :fla2 = 0) THEN
SET :workspac3 = :workspac3 + '';
ELSE
SET :fla2 = 1;
SET :workspac3 = :workspac3 + :workspace;
END IF;
IF :)vInteger = 4 and :workspac3 > '') THEN
SET :workspac3 = :workspac3 + '/';
END IF;
SET :vInteger = :vInteger + 1;

END LOOP;

SET :smallest = :workspac2 + :workspac3 + :C;
RETURN :smallest;

END;

This user defined function works fine using version 9 but will not work with Version 11. I have narrowed it down to the line that says SET :workspac2 = :workspac2 + :workspace;
 
What do you mean by "will not work with Version 11?" What is the expected result and what results are you seeing?
I ran your code on an 11.30.51 install of PSQL v11 (64 bit) and it seemed to work. Here's what I did:
Code:
select Statuteconcat('1234','56789', 'abcdefghijklmnopqrstuvwxy')
and the result:
Code:
abcdefghijklmnopqrstuvwxy



Mirtheil
 
Mirtheil,

I mean that I have two server engines Pervasive V9.5 and Pervasive V11. The script works on the V9.5 no problem.

The expected result is a string that gets concatenated together to be 625/5-11-106(B). That is what V9.5 does. Which is correct.

When I run the same script, on Pervasive V11, everything works except this one function. This user defined function gets called from another procedure that has this in a select clause. What this procedure does is it takes three fields that get read in and then calls this function to strip out any leading zeros and puts in a "/" and a "-" when needed. So the outcome shows a number like "625/5-11-106(B)". The data for :A, :B and :C that was pulled into this was "0625", "0050", and 11-106(B) respectively.
On Pervasive V11 all I get is the last section "11-106(B)" returned in the :smallest variable.

I appreciate any input.
 
Okay, I've reproduced the behavior you are seeing on my v11 server. It appears that in v9, the strings were trimmed automatically but aren't in v11. If you change your function as below, it should work and the new version should work with V9 as well. As to why it changed, I'm not sure. It was probably something to do with the SQL engine itself. Since PSQL v11 isn't supported, it won't get fixed. You might try it on PSQL v13 since that's the currently support product.

Code:
CREATE FUNCTION Statuteconcat(:A char(4), :B char(5), :C char(25)) 
RETURNS char(34)
AS 
BEGIN 

DECLARE :chapter char(4);
DECLARE :act char(4);
DECLARE :workspace char(4);
DECLARE :workspac2 char(4);
DECLARE :workspac3 char(5);
DECLARE :actdec char(1);
DECLARE :smallest char(34);

DECLARE :vInteger integer;
DECLARE :vIntege2 integer;
DECLARE :flag integer;
DECLARE :fla2 integer;


SET :workspace = LEFT(:B, 4);
SET :flag = 0;
SET :fla2 = 0;
SET :vInteger = 1;
SET :vIntege2 = 1;
SET :workspac2 = '';
SET :workspac3 = '';

TestLoop:
LOOP

IF (:vInteger > 4) THEN
LEAVE TestLoop;
END IF;
SET :workspace = SUBSTRING(:A, :vInteger, 1);
IF (:workspace = '' and :flag = 0) THEN
  SET :workspac2 = rtrim(:workspac2) + '';
ELSE
  SET :flag = 1;
  SET :workspac2 = rtrim(:workspac2) + rtrim(:workspace);
END IF;
SET :vInteger = :vInteger + 1;
IF (:vInteger = 5 and :workspac2 > '') THEN
  SET :workspac2 = rtrim(:workspac2) + '-';
END IF;
END LOOP; 

SET :vInteger = 1;

TestLoop:
LOOP

IF (:vInteger > 4) THEN
LEAVE TestLoop;
END IF;

SET :workspace = SUBSTRING(:B, :vInteger, 1);
IF (:workspace = 0 and :fla2 = 0) THEN
  SET :workspac3 = rtrim(:workspac3) + '';
ELSE
  SET :fla2 = 1;
  SET :workspac3 = rtrim(:workspac3) + rtrim(:workspace);
END IF;
IF (:vInteger = 4 and :workspac3 > '') THEN
  SET :workspac3 = rtrim(:workspac3) + '/';
END IF;
SET :vInteger = :vInteger + 1;
END LOOP; 

SET :smallest = rtrim(:workspac2) + rtrim(:workspac3) + rtrim(:C);
RETURN :smallest; 

END;


Mirtheil
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top