Allow multiple record selection criteria 1

[itkyle]

Using CR6...

I've written a report to show clients to whom we've made payments in 3 categories, and used the Select Expert using {PMT_CAT} "is one of...", then chose the 3 categories. The problem with this is that when a client has been paid under more than one of those categories, they won't appear on the report.

Any suggestions? Thanks in advance for the help.

Kyle

 

[cuetzpalin]

need more information.

 

[synapsevampire]

This shouldn't prevent people in more than one of the IN.

Post your selection criteria and the generated SQL statement, and a quickie example of data and expected results.

The selection criteria for your select expert would generate:

{PMT_CAT} in ["choice 1","choice 2","choice 3"]

Which means that as long as they're any one one of those, the row will be fetched.

I believe that your problem lies elsewhere.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[itkyle]

Here's the record selection formula:

{CLAIM.ACC_NUM} = "HCP" and
{PAYMENT.PMT_CAT} in ["I31", "I30", "I06"]

Here's the SQL statement:

SELECT
CLAIM.ACC_NUM, CLAIM.CLM_NUM, CLAIM.CAS_NAM, CLAIM.OCC_DTE, CLAIM.STATUS, PAYMENT.PMT_CAT, PAYMENT.ISS_DTE, PAYMENT.PAID
FROM
PYRAMID.PYRAMID.CLAIM CLAIM,
PYRAMID.PYRAMID.CLAIMANT CLAIMANT,
PYRAMID.PYRAMID.PAYMENT PAYMENT
WHERE
CLAIM.UNQ_ID = CLAIMANT.UNQ_ID AND
CLAIMANT.UNQ_ID = PAYMENT.UNQ_ID AND
CLAIM.ACC_NUM = 'HCP' AND
(PAYMENT.PMT_CAT = 'I06' OR
PAYMENT.PMT_CAT = 'I30' OR
PAYMENT.PMT_CAT = 'I31')
ORDER BY
CLAIM.ACC_NUM ASC,
PAYMENT.PMT_CAT ASC,
PAYMENT.ISS_DTE ASC

Thanks for taking a look at this.

Kyle

 

[Naith]

Hey Kyle,

You can rest assured that your problem is definitely not this part:

(PAYMENT.PMT_CAT = 'I06' OR
PAYMENT.PMT_CAT = 'I30' OR
PAYMENT.PMT_CAT = 'I31')

This subclause means that as long as the client satisfies one or more of these criterias, the row will be pulled back.

I would be more apt to have a look at the linking you have established between the three tables, and also verify whether or not the ACC_NUM for these multi-paid clients remains 'HCP'.

Naith

 

[Naith]

Just curious about one aspect of your linking as an afterthought...

Your links are dependent on:

CLAIM.UNQ_ID = CLAIMANT.UNQ_ID AND
CLAIMANT.UNQ_ID = PAYMENT.UNQ_ID

yet CLAIMANT appears to be an unnecessary inclusion, as you don't really call on any elements from CLAIMANT at all in the query. Why can't CLAIM.UNQ_ID link directly to PAYMENT.UNQ_ID?

 

[synapsevampire]

Try changing <CLAIMANT.UNQ_ID = PAYMENT.UNQ_ID AND> to a Left Outer.

I understand the concept to be:

A Claim has a claimant <I assume an equal, perhaps it too is a Left Outer>
But a claim can have numerous payments <Left Outer>

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com