Mon Aug 15, 2022 9:27 am
Login Register Lost Password? Contact Us

Please Note: The HPCC Systems forums are moving to Stack Overflow. We invite you to post your questions on Stack Overflow utilizing the tag hpcc-ecl (https://stackoverflow.com/search?tab=newest&q=hpcc-ecl). This legacy forum will be active and monitored during our transition to Stack Overflow but will become read only beginning September 1, 2022.



Key condition does not have any comparisons against key fiel

Comments and questions related to the Enterprise Control Language

Wed Dec 29, 2021 5:00 pm Change Time Zone

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?
janet.anderson
 
Posts: 52
Joined: Wed Jul 17, 2013 5:31 pm

Tue Jan 04, 2022 6:38 pm Change Time Zone

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
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1619
Joined: Wed Oct 26, 2011 7:40 pm

Tue Jan 04, 2022 7:48 pm Change Time Zone

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);
janet.anderson
 
Posts: 52
Joined: Wed Jul 17, 2013 5:31 pm

Tue Jan 04, 2022 8:20 pm Change Time Zone

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
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1619
Joined: Wed Oct 26, 2011 7:40 pm

Thu Jan 06, 2022 3:09 pm Change Time Zone

The LOOKUP seemed to work. Thanks, Richard.
janet.anderson
 
Posts: 52
Joined: Wed Jul 17, 2013 5:31 pm

Tue Mar 22, 2022 12:49 pm Change Time Zone

Test, please ignore...
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1006
Joined: Wed Jun 29, 2011 7:13 pm


Return to ECL

Who is online

Users browsing this forum: No registered users and 1 guest

cron