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,

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

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?