×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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!

*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

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

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

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

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

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! Already a Member? Login


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