I need to conditionally join two files when field1 in file1 'contains' field2 in file2. 'field' is not an option when I select the 'contains' LogicalRelation in Expression Builder. Is there another way?

This message has been edited. Last edited by: jlentner,

Have you tried a conditional join ?

e.g.

JOIN INNER|LEFT_OUTER
FILE file1 AT field1 [TAG tag1] TO UNIQUE|MULTIPLE
FILE file2 AT field2 [TAG tag2] AS jointag
[WHERE tag1.field1 CONTAINS tag2.field2 ;]
END  

I think this CONTAINS works, if it doesn't, leave the WHERe out and add the WHER clause to the TABLE request.

But be careful with trailing blanks in field2 --

"applesauce  " CONTAINS "apple   "
 ^^^^^^^^^^^^            ^^^^^^^^
is False

I tried the WHERE clause in both the conditional join and then at the TABLE level. Got the same result for both which was as if I had used EQ instead of CONTAINS.

Conditional Join:

-* File iscd_home_performance_dropdown_1_2.fex
-SET &ECHO=ALL;
-DEFAULT &SRV_KEY01 = '0.0.1.0.77.0.1';
JOIN
INNER FILE ISDIMSERVICE AT ISDIMSERVICE.ISDIMSERVICE.DIDSERVICEKEY TO MULTIPLE
FILE ISMETRICMASTER AT ISMETRICMASTER.ISMETRICMASTER.DIDSERVICEKEY TAG J1 AS J1
WHERE ISDIMSERVICE.ISDIMSERVICE.DIDSERVICEKEY CONTAINS J1.ISMETRICMASTER.DIDSERVICEKEY;
END
TABLE FILE ISDIMSERVICE
PRINT
ISDIMSERVICE.ISDIMSERVICE.DIDSERVICEKEY
J1.ISMETRICMASTER.METRICKEY
J1.ISMETRICMASTER.DIDSERVICEKEY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
LINES-PER-PAGE=20,
$
ENDSTYLE
END


WHERE clause on the TABLE:

* File iscd_home_performance_dropdown_1_2.fex
-SET &ECHO=ALL;
-DEFAULT &SRV_KEY01 = '0.0.1.0.77.0.1';
JOIN
FILE ISDIMSERVICE AT ISDIMSERVICE.ISDIMSERVICE.DIDSERVICEKEY TO MULTIPLE
FILE ISMETRICMASTER AT ISMETRICMASTER.ISMETRICMASTER.DIDSERVICEKEY TAG J1 AS J1
END
TABLE FILE ISDIMSERVICE
PRINT
ISDIMSERVICE.ISDIMSERVICE.DIDSERVICEKEY
J1.ISMETRICMASTER.METRICKEY
J1.ISMETRICMASTER.DIDSERVICEKEY
WHERE ISDIMSERVICE.ISDIMSERVICE.DIDSERVICEKEY CONTAINS J1.ISMETRICMASTER.DIDSERVICEKEY;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
LINES-PER-PAGE=20,
$
ENDSTYLE
END

quote:

as if I had used EQ instead of CONTAINS.

Meaning, the record is pulled only when ISDIMSERVICE.ISDIMSERVICE.DIDSERVICEKEY and ISMETRICMASTER.ISMETRICMASTER.DIDSERVICEKEY contain identical values.

You're just not paying attention: The trailing blanks are treated as part of the value, so a "CONTAINS" comparison of two fields yields "true" only when both fields have identical contents.

Rather than using "CONTAINS", use functions -- ARGLEN to determine the trimmed length of the second field's value, and POSIT to test whether the first field's value contains the leading portion of the second field's value, up to the latter's trimmed length.

If the keys are declared as char (An), this should do it:

 
WHERE
 POSIT(ISDIMSERVICE.ISDIMSERVICE.DIDSERVICEKEY, 
       n,
       ISMETRICMASTER.ISMETRICMASTER.DIDSERVICEKEY, 
       ARGLEN(n, ISMETRICMASTER.ISMETRICMASTER.DIDSERVICEKEY, 'I5'),
       'I5') 
  NE 0; 

If varchar (AnV), you may have to use corresponding funtions designed for use with varchar fields.

Whether WebFocus is clever enough to translate that test to SQL and pass it on to the server, you can only determine by obtaining a statement trace.

Is there a way to use the contents of a field as the comparison pattern to use in a 'like' in a conditional join or really any WHERE clause?

I think you can do this with a function on the tested value, will check.

Jack's suggestion is probably the best way to go.

This message has been edited. Last edited by: Waz,

Thanks, that works.