2

I have a scenario where I have claim application table and application claimant table. I need to sparse look up for the application claimant ID from the application claimant table using SSN as the key. Problem is there are multiple application claimant IDs for the same SSN. So I would like to pull the ones which are not already used in the Claim_application table. So I am using Sparse look up with the below query. 

SELECT SSN, CLAIM_APPLICATION_CLAIMANT_ID 

FROM CLAIM_APPLICATION_CLAIMANT 

WHERE ORCHESTRATE.CLAIM_APPLICATION_CLAIMANT_ID not in (
     SELECT CLAIM_APPLICATION_CLAIMANT_ID 
     FROM CLAIM_APPLICATION
)

But when I am going to map key expression in the lookup stage, it is throwing below error.

Key expression cannot be set on key columns of this link.The connected stage defines the key lookup

What could be the issue here?

ryanyuyu
  • 6,366
  • 10
  • 48
  • 53
blr20
  • 23
  • 7

1 Answers1

0

i am also baffled but please explore these options.

  1. in the nested query add distinct clause and another where clause to equate the ssn between both tables

  2. try an alternate approach involving use of joins.

  3. try using the SQL MINUS operator if the columns are equal in number

Nish
  • 11
  • 1