Define Object to report a condition

Hello :mrgreen:

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…

Any ideas/suggestions…

Thanks


Sridharan :india: (BOB member since 2002-11-08)

Some ideas:

  1. 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

Andreas :de: (BOB member since 2002-06-20)

Sri,

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

Kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

Or you could write a function/procdure at the DB level or ask your ETL team to incorporate the logic :wink:


Andreas :de: (BOB member since 2002-06-20)

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…


Sridharan :india: (BOB member since 2002-11-08)

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…

Esther


esther (BOB member since 2004-08-23)