Creating a view with Pervasive SQL in Macola Database

[dgillz]

I am trying to create a view in P.SQL 2000i and I am getting an error message when I hit OK or Apply, but not when I hit check syntax. Here is my error message:

[Pervasive][ODBC Client Interface][Pervasive][ODBC Engine Interface]Syntax Error: CREATE VIEW &quot;IndyHeart&quot; AS SELECT mn_no, sb_no, dp_no, trx_dt as dist_dt, trx_amt as dist_amt, job_no FROM GLTRXHST_SQL unionSELECT mn_no<< ??? >>, sb_no, dp_no, dist_dt, dist_amt, job_no FROM APDISFIL_SQL

Any ideas where my problem is and why am I getting the error?

The reason I am doing this, in case anyone is interested, is that if the APDISFIL_SQL has multiple lines of distribution for one transaction with the same GL account number but with different job numbers, only one record is being written to the GLTRXHST_SQL table. As a result I cannot get the report I want unless I write a view, and base a crystal report on the view.

If anyone has any alternative ways of doing this, I will certainly entertain them. If this was MS SQL I would have been done days ago!

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[stravis]

Can you please provide a copy of the original SQL statement that you are using to create your view.

Scott
NEXO Systems, Inc.

 

[dgillz]

Pretty much as you see it above:

SELECT mn_no, sb_no, dp_no, trx_dt as dist_dt, trx_amt as dist_amt, job_no FROM GLTRXHST_SQL union SELECT mn_no, sb_no, dp_no, dist_dt, dist_amt, job_no FROM APDISFIL_SQL



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[stravis]

All versions of Pervasive, below version 8.0 SP1 do not support the UNION operator in a stored view. You can however use the union operator in a temporary view, which is just a direct SQL SELECT Query. If you really need to aggregate this information before you bring it in to Macola I would suggest that you use an intermediary data source like Access to perform the UNION Query.

Scott
NEXO Systems, Inc.

 

[stravis]

Oops, I guess you are probably not intending to bring this information in to Macola. It's late. What I meant to say was the following &quot;If you really need to aggregate this information before you pull it in your reporting tool, I would suggest...&quot;.

Scott Travis
NEXO Systems, Inc.