Key condition does not have any comparisons against key fiel

[janet.anderson]

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?

[rtaylor]

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

[janet.anderson]

Below is the index definition:

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);


[rtaylor]

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

[janet.anderson]

The LOOKUP seemed to work. Thanks, Richard.

[bforeman]

Test, please ignore...