exception join
SELECT
GRPID,
MEMBER.PLNCD,
MBIDT,
MBSEX
FROM
MMEMBER EXCEPTION JOIN GPLANMAS ON
MMEMBER.PLNCD = GPLANMAS.PLNCD
This is a query used in showcase tool.
It is looking for members who are
noted in the member file with an invalid plan code. The current query
produces 18 members with either invalid plan codes or blank plan codes. How can I achieve this in FM using inner join between these two table? According to me the package published has inner join between MMEMBER AND GPLANMAS tables. I will really appreciate the help.
Thanks a lot
Software/Hardware used:
Software/Hardware used:
ASKED:
June 11, 2005 3:50 PM
UPDATED:
June 19, 2005 3:23 PM
Answer Wiki:
SELECT GRPID,PLNCD,MBIDT,MBSEX
FROM MEMBER
WHERE PLNCD NOT IN (SELECT PLNCD FROM GPLANMAS)
should give you all invalid PLNCDs in the MEMBER table.
To see all answers submitted to the Answer Wiki: View Answer History.
a faster option might be to use a correlated subquery and a NOT EXISTS to perform the lookup.
Note: for this to perform, there must be an index on each table where the leading column of the index is the referenced column, thus: PLNCD
Try this:
select …….. from MMEMBER where NOT EXISTS
(select 1 from GPLANMAS where GLANMAS.PLNCD=MMEMBER.PLNCD)
The reason that this is faster is that with the IN statement, if you have milions of rows that qualify, you will build a subquery that could return milions of rows, and most RDBMS’s could not handle that.
Using the correlated subquery, each row by the outer query wil invoke the corelatd subquery, which does not actually return any rows but simply checks for the existence of a record and returns a boolean true or false which tells the sql engine whether to keep or discard the row from the outer query.
Regards:
Ferenc