[CLOSED] Conditional JOIN - Where A not in Table B Plus some selection

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, 2009

rfbowley

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, 2005

Hua

Guru

posted

Hide Post