Using if-else condition inside a insert-select block

Question

I have two tables elig(mID, startDate, endDate) and claim(mID, serviceDate). I have to check if the serviceDate is between the startDate and endDate, and insert into a new table hasClaim(mID,startDate,endDate,Flag). The flag is to be set to 'Y' if the condition in satisfied and 'N' otherwise. What I have done is:

INSERT INTO hasClaim (mID, startDate, endDate, has_claim)  
SELECT e.mID, e.startDate, e.endDate, 'Y' 
FROM elig e 
inner JOIN claim c 
ON e.mID=c.mID 
WHERE c.serviceDate BETWEEN e.startDate AND e.endDate

But this only returns those values whose serviceDate is between the startDate and endDate with the flag set to 'Y'. How to retrieve other values as well and set the flag to 'N'? I am using oracle.

Answer 1

Try:

INSERT INTO hasClaim (mID, startDate, endDate, has_claim)  
SELECT e.mID, 
       e.startDate, 
       e.endDate, 
       CASE 
          WHEN c.serviceDate BETWEEN e.startDate AND e.endDate
          THEN 'Y'
          ELSE 'N'
       END AS has_claim
FROM elig e 
inner JOIN claim c 
ON e.mID=c.mID;

This uses the searched case statement (from the 10g docs but it is the same in 11g).