×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Set command in script does not work on Pervasive V11

Set command in script does not work on Pervasive V11

Set command in script does not work on Pervasive V11

(OP)
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;

RE: Set command in script does not work on Pervasive V11

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
http://www.mirtheil.com

RE: Set command in script does not work on Pervasive V11

(OP)
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.

RE: Set command in script does not work on Pervasive V11

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
http://www.mirtheil.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close