Key condition does not have any comparisons against key fiel
I am trying to use an index that has city, state, zip (this is a file maintained by another user, but I want to leverage it). City and state are keys, but I want to use this index like a normal dataset. I am trying to do a join on zip but get the following error:
Error: Key condition (LEFT.order_zip = RIGHT.zip5) does not have any comparisons against key fields (496, 13), 4099,
How can I do a join on a non-keyed field only?
Error: Key condition (LEFT.order_zip = RIGHT.zip5) does not have any comparisons against key fields (496, 13), 4099,
How can I do a join on a non-keyed field only?
- janet.anderson
- Posts: 52
- Joined: Wed Jul 17, 2013 5:31 pm
Janet,
What does the actual JOIN code look like? Is the index the first or second parameter to your JOIN? Do you have the LOOKUP option on it?
I don't see any reason why it shouldn't work, so I'd need to see the code to comment any further.
HTH,
Richard
What does the actual JOIN code look like? Is the index the first or second parameter to your JOIN? Do you have the LOOKUP option on it?
I don't see any reason why it shouldn't work, so I'd need to see the code to comment any further.
HTH,
Richard
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
Below is the index definition:
And the join:
- Code: Select all
SHARED r_zipcityst := RECORD
STRING28 city;
STRING2 state
=>
STRING5 zip5;
STRING1 zipclass;
STRING25 county;
STRING28 prefctystname;
UNSIGNED8 __internal_fpos__;
END;
SHARED d_zipcityst := DATASET(prod_prefix + 'thor_data400::key::bipv2::qa::zipcityst', r_zipcityst, THOR);
EXPORT i_zipcityst := INDEX(d_zipcityst, {city, state}, {zip5, zipclass, county, prefctystname,__internal_fpos__}, prod_prefix + 'thor_data400::key::bipv2::qa::zipcityst');
And the join:
- Code: Select all
d_trans7 := join(d_trans6, Monthly_Shop_Report_Datacube.modFiles().i_zipcityst,
left.order_zip = right.zip5,
transform(recordof(d_trans6),
self.order_state := right.state;
self := left;), left outer);
- janet.anderson
- Posts: 52
- Joined: Wed Jul 17, 2013 5:31 pm
Janet,
I would first try adding the LOOKUP option to your JOIN. As it's written, the compiler sees a half-keyed JOIN, so it's not expecting to treat the rhs as a dataset but as an index. Adding LOOKUP may be enough to get around that.
If that doesn't work, I'd create a TABLE from the index of just the zips and their state field values then do the JOIN against the TABLE instead of the index.
HTH,
Richard
I would first try adding the LOOKUP option to your JOIN. As it's written, the compiler sees a half-keyed JOIN, so it's not expecting to treat the rhs as a dataset but as an index. Adding LOOKUP may be enough to get around that.
If that doesn't work, I'd create a TABLE from the index of just the zips and their state field values then do the JOIN against the TABLE instead of the index.
HTH,
Richard
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
The LOOKUP seemed to work. Thanks, Richard.
- janet.anderson
- Posts: 52
- Joined: Wed Jul 17, 2013 5:31 pm
Test, please ignore...
- bforeman
- Community Advisory Board Member
- Posts: 1006
- Joined: Wed Jun 29, 2011 7:13 pm
2 posts
• Page 1 of 1
Who is online
Users browsing this forum: Google [Bot] and 1 guest