Need to define an Object which does some table row level checking and display either “Found” or “Not Found” in the report… Here is a sketch of the requirement
Customer Ref Value Flag
Customer1 10025 4572 S
Customer2 11088 3658 S
Customer3 15784 7854 S
Customer1 10025 4572 T
Customer3 15784 7854 T
Customer4 13620 2555 T
There are other columns but presently these are the columns of interest…
For each Customer, Ref and Value with Flag being ‘S’, need to find if another entry for those combination exists with Flag value ‘T’. If such an entry is found then need to display “Found” or else display “Not Found”.
So when we select Customer, Ref, Value and this object it should display
Customer Ref Value Info
Customer1 10025 4572 Found
Customer2 11088 3658 Not Found
Customer3 15784 7854 Found
If the same thing is run with Flag being ‘T’ then it should do it the other way and check for ‘S’ occurence and the result will be
Customer Ref Value Info
Customer1 10025 4572 Found
Customer3 15784 7854 Found
Customer4 13620 2555 Not Found
In the above we take Flag ‘T’ and report the value…
Alias your original table, join the alias table to your original table via a complex subquery join (using EXISTS). Create your FOUND/NOT FOUND object from the alias table.
Or
2) Try a defining your FOUND/NOT FUND object using:
CASE WHEN EXISTS (SELECT subquery...) THEN 'FOUND' ELSE 'NOT FOUND' END
WHen you get this resolved, can you also share this with me please. I have kind of a similar situation where I have to go through rows, based on a column condition.
Thanks Andreas… I thought of three options before I posted and wanna check how someone else will deal with this situation and you’ve given two of those… Thanks again…
I have a similar issue…
The scenario is that…
ID
PARENT ID
FLAG
I was trying to create a flag object that has “Y” or “N” depending on a particular ID is a parent or not…I should check if each ID is among the parent IDS in teh table and if not I should show a 'N and if yes, I should show a ‘Y’
I am kinda confused on coding case when exists object def… how do I do that…
case when exists (select parentID from table)…well can some one help me out …thanks in advance…