Pervasive 9.5 Help with inserting recors from complex query to a table

[speedyvw]

Hi Folks,

I'm using Pervasive 9.5, but found that the SQL has a lot of limitations. I'm trying to create a table that is populated from key fields from another table. Extracting the key fields is done by complex query containing nested queries, UNION, GROUP BY and ORDER BY. Here is an example of such a query:

SELECT P.Field1 AS C1, P.Field2 AS C2, P.Field3 AS C3, RD.Field3 AS C4
FROM Database2.table1 P, Database2.table2 R1, Database2.table3 RD
WHERE (P.SomeField = R1.SomeField AND R1.TypeCode = RD.TypeCode and R1.Rating = RD.Rating AND R1.TypeCode = 1 AND
R1.RatingDate = (SELECT MAX(RatingDate) FROM Database2.table2 R2 WHERE R2.SomeField = R1.SomeField AND R2.TypeCode = 1))
GROUP BY RD.Field3, P.Field1, P.Field2, P.Field3
HAVING SUM(P.Amount) > 0.0
UNION SELECT Field1 AS C1, Field2 AS C2, Field3 AS C3, ' ' AS C4 FROM Database2.table1 where SomeField NOT IN (SELECT DISTINCT
SomeField FROM Database2.table2 WHERE TypeCode = 1 ) GROUP BY Field1, Field2, Field3 HAVING SUM(Amount) > 0.0 ORDER BY C4;

If I was using MSSQL server I would have done simple INSERT INTO KeyTable SELECT ...MyQuery..., but I can not do that due to the PSQL syntax limitations.
Another approach I tried was to use Stored Procedure like this one:

CREATE PROCEDURE PosKeyGeneratePrm();
BEGIN
DECLARE :C1 CHAR(15);
DECLARE :C2 SMALLINT;
DECLARE :C3 CHAR(1);

DECLARE c1Bulk CURSOR FOR SELECT P.Field1 AS C1, P.Field2 AS C2, P.Field3 AS C3, RD.Field3 AS C4
FROM Database2.table1 P, Database2.table2 R1, Database2.table3 RD
WHERE (P.SomeField = R1.SomeField AND R1.TypeCode = RD.TypeCode and R1.Rating = RD.Rating AND R1.TypeCode = 1 AND
R1.RatingDate = (SELECT MAX(RatingDate) FROM Database2.table2 R2 WHERE R2.SomeField = R1.SomeField AND R2.TypeCode = 1))
GROUP BY RD.Field3, P.Field1, P.Field2, P.Field3
HAVING SUM(P.Amount) > 0.0
UNION SELECT Field1 AS C1, Field2 AS C2, Field3 AS C3, ' ' AS C4 FROM Database2.table1 where SomeField NOT IN (SELECT DISTINCT
SomeField FROM Database2.table2 WHERE TypeCode = 1 ) GROUP BY Field1, Field2, Field3 HAVING SUM(Amount) > 0.0 ORDER BY C4;

OPEN c1Bulk;
BulkLinesLoop:
LOOP
FETCH NEXT FROM c1Bulk INTO :C1, :C2, :C3;
IF SQLSTATE = '02000' THEN
LEAVE BulkLinesLoop;
END IF;
INSERT INTO poskey1 VALUES(0,:C1, :C2, :C3);
END LOOP;
CLOSE c1Bulk;
END

This works, but I have to create Stored procedure for every variation of the query which is not acceptable. Looks like I can not accept the cursor query as input parameter nor I can do:

IF :Query = 1 THEN
DECLARE c1Bulk CURSOR FOR .some query.
END IF;

IF :Query = 2 THEN
DECLARE c1Bulk CURSOR FOR .different query.
END IF;

What are my options? Any help would be greatly appreciated!