Passing multiple values into one string

[peewan]

Hello Friends.

Is it possible to create a COGNOS report that is like this SQL statement:
SELECT *
FROM RADAR Claims
WHERE Claim # IN (
                                SELECT Claim# FROM RADAR Claims
                                WHERE Proc Code = '80053' );

I would like to get result for All line of Claim# have "80053' Pro Code.
For example:

CL_N  Claim_Nbr    Proc_Code   line#
52     12000       80053            1
52     12000       82306            2
52     12000       82670            3
52     12000       84481            4

Problem here is passing multiple values into IN Statement in the filter criteria doesn't seem to be working in for me. LIKE Statement and other ways I tried also doesn't seem to be working.
When i use "In, Like, Contains and prompt" it will give me all Claim# with Pro Code = '80053' (Line 1) only.

Any insight guys on how to proceed on this. Please share your thoughts. Let me know if it is unclear and I can add additional info. Thanks.

(Note: I read this one, but all code in the same row, and in difference row, so it doesn't work for me.  https://www.cognoise.com/index.php?topic=32391.0)

[adam_mc]

You can certainly do the equivalent of a sub-select directly in Cognos, however, my experience has shown me that this typically does not work as performantly as you would want especially when you start adding more complex conditions like "in", "contains", etc...

I have found it best to create two queries:
The first query to obtain the set of Claims you are looking for - In your case, a distinct set of Claim Numbers with at least one Procedure Code  = '80053'.
The second query to get the the set of all Claim and Procedure Code details.
Restrict both queries by whatever other attributes you can to increase performance - Date etc...

Then, join these queries together on Claim Number, and create a third query you should get the results you need.

Hope this helps.
Thanks,
Adam.   

[seb24c]

I do this in one query using a calculation and filter:

c_ProcCodeInd: maximum ( if ( Proc_Code = '80053' ) then ( 'Y' ) else ( null ) for Claim_Nbr )
filter: c_ProcCodeInd = 'Y'

Hope this helps,
Sarah

[peewan]

Thank you! it works great!

You can certainly do the equivalent of a sub-select directly in Cognos, however, my experience has shown me that this typically does not work as performantly as you would want especially when you start adding more complex conditions like "in", "contains", etc...

I have found it best to create two queries:
The first query to obtain the set of Claims you are looking for - In your case, a distinct set of Claim Numbers with at least one Procedure Code  = '80053'.
The second query to get the the set of all Claim and Procedure Code details.
Restrict both queries by whatever other attributes you can to increase performance - Date etc...

Then, join these queries together on Claim Number, and create a third query you should get the results you need.

Hope this helps.
Thanks,
Adam.

[peewan]

I do this in one query using a calculation and filter:

c_ProcCodeInd: maximum ( if ( Proc_Code = '80053' ) then ( 'Y' ) else ( null ) for Claim_Nbr )
filter: c_ProcCodeInd = 'Y'

Hope this helps,
Sarah

This also works great! :) Thank a lot

[peewan]

Thank you