[SOLVED] (Outer) Joins with WITH in the right-hand side?
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,
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
February 03, 2011, 03:30 PM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
February 03, 2011, 09:14 PM
j.gross
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.
February 04, 2011, 03:51 AM
Wep5622
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
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
February 04, 2011, 04:45 AM
Alan B
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
Alan. WF 7.705/8.007
February 04, 2011, 05:38 AM
Wep5622
Match file, of course! Thanks!
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :