Arrgghh! Adding your line to the query that was sitting there in the
original test window:

SELECT
pbm.ID, pbm.GROUPID, pbm.RSPPATPAY, pbm.RSPDUEAMT, pbm.CALINGRCST,
pbm.MULTSRCCDE, pbm.DATESBM,
pbm.ACCOUNTID, pbm.SRVPROVID
, (SELECT ccf.CLAIM_ID FROM RXS_CLM_CALC_FEES ccf WHERE
pbm.ID=ccf.CLAIM_ID)
,(CASE WHEN (:NEWONLY <> 'Y') THEN 'All records' ELSE 'New only' END)
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'))

And now it works. Take it away, and it still works. This was the same
query that DIDN'T work before, and I cut and pasted it into the original
post!

Computers hate me. Thanks Svein.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling
Tysvaer
Sent: Wednesday, November 01, 2006 4:01 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Query problem

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links