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)
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.
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
Thank you! it works great!
Quote from: adam_mc on 02 Aug 2019 08:01:21 AM
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.
Quote from: seb24c on 07 Aug 2019 09:54:14 AM
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
Thank you