Trust ID PBCEX logo 300x102

Key Value is not unique

6 years 2 weeks ago
Bill Burch
Fresh Boarder
Fresh Boarder
Posts: 15
More
Topic Author
Key Value is not unique #4674
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,
am.card_num,
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,
sc.club
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:
100180
100181
100182
100183

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.

Bill
6 years 2 weeks ago
Sergio Agostinho
Administrator
Administrator
Posts: 508
More
Key Value is not unique #4675
Hi Bill,

Please go to CardExchange data folder, select DataExchange.log file, zip it and upload it here for our analysis.

Thank you.

With best regards
Sergio Agostinho
6 years 2 weeks ago
Bill Burch
Fresh Boarder
Fresh Boarder
Posts: 15
More
Topic Author
Key Value is not unique #4676
Here is the zipped DataExchange log.
Attachments:
6 years 2 weeks ago
Sergio Agostinho
Administrator
Administrator
Posts: 508
More
Key Value is not unique #4677
Hi Bill,

According with your DataExchange.log file you're running the select below and you pass the column name as the value for the where clause, i think that's the reason for the problem.
-- Harbour2:
SELECT * 
FROM (select * from card) iv
WHERE "pkcustcode" = ?
? = pkcustcode

-- An error occurred in DefinitionExchange:
Key value [pkcustcode] is not unique.

With best regards
Sergio Agostinho
6 years 2 weeks ago
Bill Burch
Fresh Boarder
Fresh Boarder
Posts: 15
More
Topic Author
Key Value is not unique #4678
Actually I am not. See attachment. Within CardExchange I am simply writing select * from card, where card is a view within Informix. The rest of that is being added by the program.
Attachments:
6 years 2 weeks ago
Sergio Agostinho
Administrator
Administrator
Posts: 508
More
Key Value is not unique #4680
Hi Bill,

In that case why you don't link CardExchange directly to that view instead of use CardExchange internal view? This will save you an additional layer.

With best regards
Sergio Agostinho
6 years 2 weeks ago
Bill Burch
Fresh Boarder
Fresh Boarder
Posts: 15
More
Topic Author
Key Value is not unique #4684
Hi Sergio,

I tried that but it did give me a choice for the primary key in the drop down. If oyu check the log it says there is a syntax error when I selected it. Not sure why as it works as a straight query.
6 years 1 week ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Key Value is not unique #4686
Hello Bill,

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.

regards,
Rutger.

Rutger Koperdraad
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)
×

Notice

The forum is in read only mode.