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     "Not In" or "Exception" Join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
"Not In" or "Exception" Join
 Login/Join
 
Jason K.
Master
posted
I'm having trouble creating a join that shows all the items that exist in the primary table, but do not exist in the secondary table.

Is there such a join type?


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Darin Lee
Virtuoso
posted Hide Post
I would just do a regular join with SET ALL=PASS and then test the cross-ref field such as

WHERE crfield EQ MISSING;
or
WHERE hostfield NE crfield;

That would give you everything in the host file that does not have a matching value (i.e. does not exist) in the cross referenced file.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
hammo1j
Master
posted Hide Post
Darin's way will work but if you try using the obvious LEFT_OUTER JOIN and test for MISSING it wont. The following bug that occurs so commonly I have raised it as a case with IBI.

You have probably encountered this bug which IBI "may or may not fix"


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
PBrightwell
Master
posted Hide Post
The MATCH logic recommended by Tom should work, your hold would be old-not-new.
If you can select the field that do exist in the second table and hold them in a file, you can do a WHERE (or IF) NOT IN FILE. There are limitations to the size of the file, check your manual.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Jason K.
Master
posted Hide Post
Well, I have the following code, and it seems to be showing me all of the records from the JOBDATA table. Am I using the right type of match in an "Old-Not-New" if I want to show a list of all the invoices from the Jobdata file that do not exist in the Order_transactions_tbl file?

MATCH FILE JOBDATA
SUM INVNODATE
PRINT
INVNODATE
ORDERDATE
DELDATE
DELIVERED
BY INVNUMBER
WHERE INVNUMBER NE '';
RUN

FILE ORDER_TRANSACTIONS_TBL
SUM ORDER_NUM
BY PSC_INVNUMBER
WHERE PSC_INVNUMBER NE '';
AFTER MATCH HOLD OLD-NOT-NEW
END

TABLE FILE HOLD
PRINT INVNUMBER AS 'Invoice#'
INVNODATE/HMDYY AS 'Invoice,Date'
ORDERDATE/HMDYY AS 'Date,Ordered'
DELDATE/HMDYY AS 'Sched,Del Date'
DELIVERED/HMDYY AS 'Delivered'
BY HIGHEST INVNODATE NOPRINT
BY HIGHEST DELIVERED NOPRINT
WHERE INVNUMBER NE '';


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
PBrightwell
Master
posted Hide Post
quote:
SUM INVNODATE
PRINT
INVNODATE


I haven't ever tried to do a double verb in a match and in this case I think this won't accomplish anything. Also, you might try
BY PSC_INVNUMBER AS INVNUMBER.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Jason K.
Master
posted Hide Post
You all have been very helpful, thank you.
It's still showing all the records from the first file, regardless of whether they exist in the second file.

How do I specify which field[s] should be compared between the two files? I assumed it was the by fields, in this case "INV_NUM_RIPOUT" AND "PSC_INVNUMBER" are what we want to compare.

Same fex, only modified to include the inv_num_ripout virtual column.

MATCH FILE JOBDATA
PRINT
INVNODATE
INVNUMBER
ORDERDATE
DELDATE
DELIVERED
BY INV_NUM_RIPOUT
WHERE INV_NUM_RIPOUT NE '';
-*WHERE INVNODATE GE &CMPDTTM;
RUN

FILE ORDER_TRANSACTIONS_TBL
SUM ORDER_NUM
BY PSC_INVNUMBER AS 'INV_NUM_RIPOUT'
WHERE PSC_INVNUMBER NE '';

AFTER MATCH HOLD OLD-NOT-NEW
END
TABLE FILE HOLD
PRINT INVNUMBER AS 'Invoice#'
INV_NUM_RIPOUT
INVNODATE/HMDYY AS 'Invoice,Date'
ORDERDATE/HMDYY AS 'Date,Ordered'
DELDATE/HMDYY AS 'Sched,Del Date'
DELIVERED/HMDYY AS 'Delivered'
BY HIGHEST INVNODATE NOPRINT
BY HIGHEST DELIVERED NOPRINT
WHERE INVNUMBER NE '';


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Jason K.
Master
posted Hide Post
Tom,
Both are A15V


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
RSquared
Guru
posted Hide Post
The best way to do this is do a
JOIN LEFT_OUTER and then check for any field that is only on the second table eq ''. This will give you all the records from the first table that do not have a match on the second table.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Jason K.
Master
posted Hide Post
RSquared: I agree, that is the best way to do it, and if I could do this in SQL I would do it that way.

This was mentioned above in this thread, and this post
https://forums.informationbuilders.com/eve/forums/a/tpc/...521065452#8521065452
scared me away from this method.

I'd like to use the MATCH facility if I can.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
I think Tom is right. Those V formats always screw things up.

Put ENGINE rdbms_engine SET VARCHAR OFF in edasprof.prf and re-gen your masters.

I'll bet you get better success.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
RSquared
Guru
posted Hide Post
Jason,

Can you post the code you using?


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Jason K.
Master
posted Hide Post
I've changed both fields to remove the V from the datatype, but still the same problem.

Frowner


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Jason K.
Master
posted Hide Post
Here's the most recent version of the code...

MATCH FILE JOBDATA
SUM
INVNODATE
INVNUMBER
ORDERDATE
DELDATE
DELIVERED
BY INV_NUM_RIPOUT
RUN

FILE ORDER_TRANSACTIONS_TBL
SUM ORDER_NUM
BY PSC_INVNUMBER AS 'INV_NUM_RIPOUT'
WHERE PSC_INVNUMBER NE '';

AFTER MATCH HOLD OLD-NOT-NEW
END

TABLE FILE HOLD
PRINT INVNUMBER AS 'Invoice#'
INV_NUM_RIPOUT
INVNODATE/HMDYY AS 'Invoice,Date'
ORDERDATE/HMDYY AS 'Date,Ordered'
DELDATE/HMDYY AS 'Sched,Del Date'
DELIVERED/HMDYY AS 'Delivered'
BY HIGHEST INVNODATE NOPRINT
BY HIGHEST DELIVERED NOPRINT
WHERE INVNUMBER NE '';


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Gizmo
Gold member
posted Hide Post
quote:
Originally posted by hammo1j:
Darin's way will work but if you try using the obvious LEFT_OUTER JOIN and test for MISSING it wont. The following bug that occurs so commonly I have raised it as a case with IBI.

You have probably encountered this bug which IBI "may or may not fix"


As hammo1j stated, Darin's way will work, and hammo1j's issue does exist.

Remeber, that when FOCUS does a 1 to 1 join, missing alpha fields will become blank and numeric fields will become 0. WebFOCUS is retesting the data after the data has been retrieved from the SQL call. Because of this, you will need an OR condition.

For example...
JOIN LEFT_OUTER IDX IN TB1 TO IDX IN TB2
TABLE FILE TB1
PRINT IDX AS 'MISSING IDX,IN TB2'
WHERE TB2.IDX IS MISSING OR TB2 EQ ''
END


Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Report This Post
Jason K.
Master
posted Hide Post
Gizmo:
That's brilliant! Thank you!

I'm getting an error now related to the join that I've never seen before, but I've never attempted to join on a define (virtual field) either.

(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE: INV_NUM_RIPOUT
BYPASSING TO END OF COMMAND


The code is:
JOIN LEFT_OUTER INV_NUM_RIPOUT IN JOBDATA TO PSC_INVNUMBER IN ORDER_TRANSACTIONS_TBL
TABLE FILE JOBDATA
PRINT INV_NUM_RIPOUT AS 'MISSING INVOIVCE,IN LC'
WHERE PSC_INVNUMBER IS MISSING OR PSC_INVNUMBER EQ ''
END

and INV_NUM_RIPOUT is shown in the .mas as
DEFINE INV_NUM_RIPOUT/A15 WITH JOBREF=IF LENV(INVNUMBER, 'I8') GE 8 THEN SUBSTR(LENV( INVNUMBER, 'I8' ), INVNUMBER, 4, 8, 5, 'A5')
ELSE INVNUMBER; $

When I open the join in the GUI, it shows both the JOBDATA and ORDER_TRANSACTIONS_TBL tables, as well as the INV_NUM_RIPOUT define field.

Any ideas on this?


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Jason K.
Master
posted Hide Post
Same error.

Error:
(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE: INV_NUM_ME
BYPASSING TO END OF COMMAND

New Fex:
DEFINE FILE JOBDATA
INV_NUM_ME/A20=IF LENV(INVNUMBER, 'I8') GE 8 THEN SUBSTR(LENV( INVNUMBER, 'I8' ), INVNUMBER, 4, 8, 5, 'A5') ELSE INVNUMBER;
END

JOIN LEFT_OUTER INV_NUM_ME IN JOBDATA TO PSC_INVNUMBER IN ORDER_TRANSACTIONS_TBL
TABLE FILE JOBDATA
PRINT INV_NUM_ME AS 'MISSING INVOIVCE,IN LC'
WHERE PSC_INVNUMBER IS MISSING OR PSC_INVNUMBER EQ ''
END


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Jason K.
Master
posted Hide Post
Thank you for your patience.
What does the "WITH" do? I've tried the literal "WITH FIELD_NAME" as well as "WITH INV_NUM_ME" and even "WITH INVNUMBER" (the field upon which the define file is built).


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Jason K.
Master
posted Hide Post
That makes sense, thanks Tom Flynn.
The PSC_INVNUMBER is an actual field in the database table, format A15.

Here's the most recent version, which seems to match yours almost perfectly. The tables are on different databases, using different adapters jobdata on MS SQL, order_Transactions_tbl on DB2. Does that make a difference?

DEFINE FILE JOBDATA
INV_NUM_ME/A20=IF LENV(INVNUMBER, 'I8') GE 8 THEN SUBSTR(LENV( INVNUMBER, 'I8' ), INVNUMBER, 4, 8, 5, 'A5') ELSE INVNUMBER;
END

JOIN LEFT_OUTER INV_NUM_ME WITH INVNUMBER IN JOBDATA TO PSC_INVNUMBER IN ORDER_TRANSACTIONS_TBL AS JX1
TABLE FILE JOBDATA
PRINT INV_NUM_ME AS 'MISSING INVOIVCE,IN LC'
WHERE PSC_INVNUMBER IS MISSING OR PSC_INVNUMBER EQ ''
END


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Jason K.
Master
posted Hide Post
Thanks for you help.
I'm going to start a new thread related to the errors I've been getting recently for a field 'not found' as it's a little outside the realm of this discussion.

Thank you again, all.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report 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     "Not In" or "Exception" Join

Copyright © 1996-2020 Information Builders