I'm trying to join a generated alpabet list to the first occurrence of a field starting with the same character, if it exists.
To that end I crafted an alphabet file using MacGuyver (see my earlier post).

For that to work properly, I need to LEFT OUTER JOIN my alphabet file to my table file, or I would only get the characters from my alphabet file for which there is a value in my table file starting with it! But, that requires a WITH statement in the right-hand side of my left outer join, which gives me a syntax error!

With the CAR examples, the below works:

EX MakeSeq

JOIN COUNTER WITH SEATS IN CAR TO COUNTER IN FSEQ
DEFINE FILE CAR
 COUNTER/I3 WITH SEATS = SEATS;
END
TABLE FILE CAR
 PRINT SEATS
 BY COUNTER
END

(Yeah, I made the COUNTER column in the generated sequence indexable)

What I need however is this:

EX MakeSeq

JOIN LEFT_OUTER COUNTER IN FSEQ TO COUNTER WITH SEATS IN CAR
DEFINE FILE CAR
 COUNTER/I3 WITH SEATS = SEATS;
END
TABLE FILE FSEQ
 PRINT SEATS
 BY COUNTER
END

But the result is:

0 ERROR AT OR NEAR LINE     13  IN PROCEDURE MakeSeq FOCEXEC *
(FOC441) WARNING. THE FILE EXISTS ALREADY. CREATE WILL WRITE OVER IT
0 TRANSACTIONS:         TOTAL =   100  ACCEPTED=   100  REJECTED=     0
SEGMENTS:             INPUT =   101  UPDATED =     0  DELETED =     0
0 NUMBER OF RECORDS IN TABLE=       18  LINES=     18
0 ERROR AT OR NEAR LINE     17  IN PROCEDURE test_makeseq
(FOC376) SYNTAX ERROR OR MISSING ELEMENT IN JOIN/COMBINE COMMAND:
0 ERROR AT OR NEAR LINE     24  IN PROCEDURE test_makeseq
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: SEATS
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

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

You can't join to a defined field.

You could hold the file so the counter field is real, or change this to a MATCH or alternately use a conditional join, if you do not need to keep records on the left that do not match.

It's not clear to me what you are trying to do -- and how "McGuyver method" will help. In fact, by joining 1-1 directly to COUNTER you avoid the potential for the utility file to multiply your original record-set.

It sounds like your need is for data reduction, not multiplication, and would be served by

sum
fst.field1
fst.field2
...
by initial

where initial/a1 is suitably DEFINEd.

I suggest you give an example of your data and the desired result.

No, I do need the data multiplication, hence the outer join. Although I now realise I forgot to reduce each group to only the first result, maybe that's what the confusion is about.

The above yields to:

EX MakeSeq

JOIN COUNTER WITH SEATS IN CAR TO COUNTER IN FSEQ
DEFINE FILE CAR
 COUNTER/I3 WITH SEATS = SEATS;
END
TABLE FILE CAR
 SUM FST.CAR
 BY COUNTER
END

COUNTER FST 
        SEATS 
2 JAGUAR 
4 JENSEN
5 JAGUAR

What I'm aiming to get is:

COUNTER FST 
        SEATS 
 1
 2 JAGUAR
 3
 4 JENSEN
 5 JAGUAR
 6
 7
 8
 9
10

As the xref field has to be an indexed real field when using FOCUS data files, unless you can index seats, try using MATCH.

MATCH FILE FSEQ
BY COUNTER
RUN
FILE CAR
BY SEATS AS COUNTER
SUM FST.CAR
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT *
END

Match file, of course! Thanks!