Hmm, excepting that I'm a bit uncertain about colons in stored
procedures (I code too few), your logic seems correct to me. Hence, I
would guess that NEWONLY isn't Y.

Try adding this to your select part (I don't know if it works):

CASE WHEN :NEWONLY <> 'Y' THEN 'All records' ELSE 'New only' END

If you get 'New only' in rows with ccf.CLAIM_ID different from NULL,
then something is fishy...

I take it that NEWONLY is initialized and that there can be no doubt
about which parameter it is.

Set

Rick Debay wrote:

> This is a query inside of a stored procedure. The COALESCE fields are
> because the SP needs to ignore that field if the caller passes in NULL.
> The problem is that when NEWONLY is Y, rows are being returned with the
> column commented as 'test purposes' containing values other than NULL.
>
> (:NEWONLY<>'Y' OR NOT EXISTS (SELECT 1 FROM RXS_CLM_CALC_FEES ccf WHERE
> pbm.ID=ccf.CLAIM_ID))
>
> If NEWONLY is Y, then the select statement, which is identical to the
> NOT EXISTS statement, should always return NULL.
>
> SELECT
> pbm.ID, pbm.GROUPID, pbm.RSPPATPAY, pbm.RSPDUEAMT, pbm.CALINGRCST,
> pbm.MULTSRCCDE, pbm.DATESBM,
> pbm.ACCOUNTID, pbm.SRVPROVID
> /* test purposes */
> , (SELECT ccf.CLAIM_ID FROM RXS_CLM_CALC_FEES ccf WHERE
> pbm.ID=ccf.CLAIM_ID)
> FROM
> PBM_CLAIM pbm
> WHERE
> (:NEWONLY<>'Y' OR NOT EXISTS (SELECT 1 FROM RXS_CLM_CALC_FEES ccf
> WHERE pbm.ID=ccf.CLAIM_ID)) AND
> (pbm.ACCOUNTID = COALESCE(:CHC,pbm.ACCOUNTID)) AND
> (pbm.SRVPROVID = COALESCE(:PHARMACY,pbm.SRVPROVID)) AND
> (pbm.DATESBM >= COALESCE(:SUBMITTEDFROM,'1900-01-01')) AND
> (pbm.DATESBM <= COALESCE(:SUBMITTEDTO,'2100-01-01'))
>
> Thanks, Rick DeBay