I am receiving this error while connected to an Informix database via ODBC - Key Value [pkcustcode] is not unique. I have setup an internal view definition with a query that has 4 records in it with a unique value and I am still getting the error. My query is:
select trim(am.cust_code)||trim(am.mbr_code) as pkcustcode,
trim(am.cust_code)||'-'||trim(am.mbr_code) as cust_code,
trim(am.fname) as first_name,
trim(am.lname) as last_name,
sc.obtained_date as begin_date,
sc.club||'='||trim(am.cust_code) as mag_encode_1,
trim(am.mbr_code)||'='||am.card_num as mag_enode_2,
from asamembr am join strcustr sc on am.cust_code = sc.cust_code
where am.status = 'A'
and am.cust_code in ('10018')
The result of that query is:
What am I missing? I know the value is unique so I do not understand why I am getting the error.
Any help is appreciated.
Without having the database available, I cannot see whether the view really gives a single record per primary-key value. It is best to test the view in a query tool, rather than in CardExchange, as CardExchange does not offer debugging facilities for views.
If connecting directly to the view results in a syntax error in DataExchange.log, it would be good to post that error too, so we can investigate why that happens. With ODBC, CardExchange uses double quotes to mark the table and column names. Maybe you need to set an option in the database to allow for this type of quoting or remove the quotes by unselecting the option "Quote table and colñumn names" in the Confuguration - Advanced options in CardExchange.
Senior Software Engineer
"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)