Join unrelated fields

[franklin1232]

Or software vendor has done something wierd with a logical field that pertains to Job Numbers in our database. They created a table called ComXref and put Job number amoung other information into ComXref.Key1 field. I am new to 4GL so can I join the Job table and ComXref even if there is no direct database relationship? If so what type of join should I use.

Plus any website that has simple and well commented source code would be a great help.

 

[Simon Sweetman]

How about this one:

for each JobTab no-lock:
    for each ComXref no-lock where
        ComXref.Key1 matches "*" + string(JobTab.JobNum) + "*":
    end.
end.

I have assumed that JobNum is a numeric field and Key1 is a string. NOTE: this join is off-index and will require a search through the entire ComXref table for each JobNum.

If the field key1 on ComXref has a word index on it you can replace the matches clause with ComXref.key1 contains string(JobTab.JobNum).

It all depends how it is set up. Typically the JobNum field should be formatted with leading zeros so that a matches/contains on value 51 dosn't also find 1051, 251, etc. the string function can take a second parameter with the display format eg. string(JobNum, "999999") would give 000051 for a value of 51.

Hope this gives you some ideas on how to proceed.

Simon