union problem with crystal reports 8.5

[itprsn]

getting an error message trying to get some logic correct in pervasive.sql data manager so I can use the code in crystal reports 8.5.

in essence I'm trying to obtain similar information from 2 tables - a header and a history table - so I can consolidate the information and use it in a report

i.e.

select part pn, purchase_order po from "v_po_history"
union
select part pn, purchase_order po from "v_po_header"

I've also tried union all and am not sure where to go next. the error message I receive no matter what I try is as follows:

odbc error: sqlstate = s0002 native error code = 0
the column prefix 'view2tab1' does not match with a table name or alias name used in the query

error in expression view2tab1.record_no

anyone have any ideas what's wrong??

 

[synapsevampire]

It sounds like one of the Views that the Views used are based on has a problem.

Try:

select part pn, purchase_order po from "v_po_history"
union
select part pn, purchase_order po from "v_po_history"

and then try:

select part pn, purchase_order po from "v_po_header"
union
select part pn, purchase_order po from "v_po_header"

to determine which is causing the error.

Pervasive is very proprietary, so I can't help anymore than this, though we do have some Pervasive experts around.

-k

 

[itprsn]

v_po_history union v_po_history works fine
v_po_header union v_po_header does not work
v_po_lines union v_po_lines does not work

I'm thinking that v_po_lines is somehow the problem because it has a record_no field that this error message may be referring to. would you agree? if so, any way around this that you know of?

thanks in advance...

 

[synapsevampire]

Well you added another View to the mix, but if neither v_po_header union v_po_header does not work and
v_po_lines union v_po_lines does not work then both are suspect, unless header includes the detail View.

Try adding in just the v_po_header table and do a Database->verify database.

Check the fields, do they have the appropriate fields? Does it work? If it fails, it sounds like the underlying Tables or Views have changed since their creation.

If Header doesn't have the same fields as History, then fabricate the ones missing.

Since your Union isn't selecting the record_no field, it sounds like you have both Views in the report joined in some fashion, if so, don't.

Just add in history Viewn and then try your Union SQL to header.

-k