The following stored proc returns no results. If I remove the UNION ALL
and the second SELECT it does work. What must I do to get the UNION ALL
to work? Thanks.

CREATE PROCEDURE paEmplRelateProc(IN :RR INTEGER)
RETURNS(
RecType VARCHAR(4),
Id VARCHAR(8),
Name VARCHAR(80),
SharedAmt REAL,
IsPrimary CHAR(1),
RR INTEGER,
FromRR INTEGER
);
BEGIN
SELECT
R.RecType,
P.PracArea AS Id,
CAST(P.Name AS VARCHAR(80)) AS Name,
R.SharedAmt,
R.IsPrimary,
P.RR AS RR,
R.FromRR
FROM paRelate R LEFT OUTER JOIN paPracArea P
ON P.RR = R.ToRR
WHERE R.RecType = 'PRAC'
AND R.FromTableId = 912 AND R.FromRR = :RR
UNION ALL
SELECT
R.RecType,
C.ContactId AS Id,
CAST(C.Name AS VARCHAR(80)) AS Name,
R.SharedAmt,
R.IsPrimary,
C.RR AS RR,
R.FromRR
FROM paRelate R LEFT OUTER JOIN paContact C
ON C.RR = R.ToRR
WHERE R.RecType = 'OFFC'
AND R.FromTableId = 912 AND R.FromRR = :RR;
END;

--
..Bill.

This is a known issue with Pervasive PSQL v9.5:

53862 A UNION ALL in a stored procedure returns no data
Workaround: Contact Pervasive Support for field test fix (FTF)
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/2007 ***


Bill wrote:

I just got the FTF and it does work now.

--
..Bill.