exception join

pts.
Tags:
Development
Software Quality Assurance
Software testing
SQL
T-SQL
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
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • FerencMantfeld
    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
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: