Trying to create unions

[kwalters110]

Pervasive 8.6 SP1

I have been trying to create a union of two views on my pervasive server. First I tried to create a view that made a union of two tables but then I learned on this forum that is not supported.

Now I am trying to use query tools like Microsoft Access and Crystal SQL Designer to create a query that accesses the views through ODBC and makes a SELECT UNION ALL query but I get the following error:

[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]The column prefix 'View2Tab1' does not match with a table name or alias name used in the query. (#0) [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in expression: View2Tab1.RECORD_NO (#0)

Now RECORD_NO is a field in my view but I have no idea where it is getting View2Tab1 from. Below is the sql for my access query

SELECT V_ORDER_HIST_LINE.ORDER_NO, V_ORDER_HIST_LINE.ORDER_SUFFIX, V_ORDER_HIST_LINE.ORDER_LINE, V_ORDER_HIST_LINE.CUSTOMER, V_ORDER_HIST_LINE.PART, V_ORDER_HIST_LINE.QTY_ORDERED, V_ORDER_HIST_LINE.QTY_SHIPPED, V_ORDER_HIST_LINE.QTY_ORIGINAL, V_ORDER_HIST_LINE.DATE_ORDER, V_ORDER_HIST_LINE.DATE_ORDER_DUE, V_ORDER_HIST_LINE.DATE_SHIPPED, V_ORDER_HIST_LINE.PRICE, V_ORDER_HIST_LINE.COST, V_ORDER_HIST_LINE.MARGIN
FROM V_ORDER_HIST_LINE
UNION ALL SELECT V_ORDER_LINES.ORDER_NO, V_ORDER_LINES.RECORD_NO, V_ORDER_LINES.RECORD_NO, V_ORDER_LINES.CUSTOMER, V_ORDER_LINES.PART, V_ORDER_LINES.QTY_ORDERED, V_ORDER_LINES.QTY_SHIPPED, V_ORDER_LINES.QTY_ORIGINAL, V_ORDER_LINES.DATE_ORDER, V_ORDER_LINES.DATE_ITEM_PROM, V_ORDER_LINES.DATE_SHIP, V_ORDER_LINES.PRICE, V_ORDER_LINES.COST, V_ORDER_LINES.MARGIN
FROM V_ORDER_LINES;

Any help would be greatly appreciated. Oh, and does anyone know if there is any talk of allowing unions of 2 different tables in a view?

Thanks

 

[mirtheil]

Do the queries work separately?
I've heard talk of it but can't say for sure. If you need it, you should contact your sales rep at Pervasive.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[kwalters110]

Yes the queries do work seperately. but as soon as I place the one query ontop of the other and use UNION to join them together I get that error, or a variation of that error. I really need to create a report that uses All Orders (open and closed) and they are located in two different tables.