Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] (Outer) Joins with WITH in the right-hand side?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] (Outer) Joins with WITH in the right-hand side?
 Login/Join
 
Virtuoso
posted
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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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


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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] (Outer) Joins with WITH in the right-hand side?

Copyright © 1996-2020 Information Builders