Range lookup issue

[datastagequestions]

The SQL statement on the reference link is : Select id,eff_date,Term_date,name from lookup where id=? and ? between eff_date & term_date.

OK, three keys, and therefore three comparisons in the lookup SQL.

Please post the SQL statement on the reference link. ...

[chulett]

And the value for the second ? comes from what column? You must mark as keys all columns in the where clause and select all columns defined in the stage in the proper order, including the keys.

[datastagequestions]

That's where I think my logic is not working. I don't know if I should add one more condition in Reference where clause like :

where id=? and ? between eff_date and term_Date and ? between eff_date & term_date because my both srce.columns are checking for the dates to be in between Reference dates.

Like : Srce.eff_Date between Lkup.eff_date & term_date and
Srce.term_Date between Lkup.eff_Date & term_date

I am having hard time putting this logic to work. Its easier if there is one input column to match between Lookup date columns. Here we have 2 source columns to match with Lookup dates columns.

And the value for the second ? comes from what column? You must mark as keys all columns in the where clause and select all columns defined in the stage in the proper order ...

[chulett]

Regardless of your query logic, you must follow the lookup rules:

1) Every column used in the 'where' must be marked as a key.
2) ? bind variables are positional so order your keys to match.
3) All columns defined in the stage must be selected in your SQL.

Ancillary rule:

4) If you need to use a key value twice in the lookup SQL, you must define two key columns and put the same value in both. Three times? Three key columns. Etc.

Note that #2 and #4 do not apply to the OCI stage as it uses numbered parameter markers.