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     [CLOSED] Conditional JOIN - Where A not in Table B Plus some selection

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Conditional JOIN - Where A not in Table B Plus some selection
 Login/Join
 
Member
posted
Assuming the following created using the developer studio join tool

JOIN
FILE ESM_MESSAGE AT ESM_MESSAGE.ESM_MESSAGE.MESSAGE_ID TO ALL
FILE ESM_ROUTES AT ESM_ROUTES.ESM_ROUTES.MESSAGE_ID TAG J0 AS J0
END



I want to create a report that only includes rows from ESM_MESSAGE

when the MESSAGE_ID on ESM_MESSAGE does not exist in MESSAGE_ID on ESM_ROUTES

FOR ESM_ROUTES WHERE I match on some passed in paramaters

ESM_ROUTES.PUBLISHER_ID EQ &PublisherID
AND
ESM_ROUTES.SUBSCRIBER_ID EQ &SubscriberID
AND
ESM_ROUTES.DESTINATION_ID EQ &DestinationID

I've been pulling my hair out trying to get it to work. I can write a sql statement that does it but the raw sql doesn't work in a webfocus report at least i can't get it too. Any help would be appreciated.

Thanks

John

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


WebFOCUS 7.6.8
Unix
ALL
 
Posts: 15 | Registered: March 31, 2009Report This Post
Platinum Member
posted Hide Post
Perfect occasion to do a MATCH FILE solution with an OLD-NOt-NEW clause in the hold statement. ie

MATCH FILE ESM_MESSAGE 
.....
BY MESSAGE_ID
WHERE ESM_ROUTES.PUBLISHER_ID EQ &PublisherID
WHERE ESM_ROUTES.SUBSCRIBER_ID EQ &SubscriberID
WHERE ESM_ROUTES.DESTINATION_ID EQ &DestinationID
MORE

FILE ESM_ROUTES
.......
BY MESSAGE_ID
AFTER MATCH HOLD OLD-NOT-NEW
END


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
 
Posts: 132 | Location: Gadsden, Al | Registered: July 22, 2005Report This Post
Guru
posted Hide Post
quote:
I want to create a report that only includes rows from ESM_MESSAGE

quote:
ESM_ROUTES.PUBLISHER_ID EQ &PublisherID
AND
ESM_ROUTES.SUBSCRIBER_ID EQ &SubscriberID
AND
ESM_ROUTES.DESTINATION_ID EQ &DestinationID
Are these requirements contraditing?


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report 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     [CLOSED] Conditional JOIN - Where A not in Table B Plus some selection

Copyright © 1996-2020 Information Builders