Link to home
Start Free TrialLog in
Avatar of srionline2k6
srionline2k6Flag for United States of America

asked on

How can i write a case within the case statement for finding the string value for a below scenario

User generated imageHow can i write a case statement for the following scenario. Please guide me or give an idea

We need to populate Activity_info column.

For populating the column, we need to consider few things which i am mentioning in here.

First  Search for the values 'Paid search click' , 'Banner ad click' , "Email click' in the Activity column

Lets say For the Corresponding recording for "Paid search click" We have the Entry_id value as 1711 ; We need to search for that particular value "1711" in the entire column.
In the above we have 2 cases of same Log_Entry_ID with value "1711" but we need to look for only  "CAMP" value that exists in the ACT_CD for one of the 1711 value and get the corresponding value from ACT_TXT (in here we have "Search") and needs to populate it into Activity_info column for that related record.

That means Activity_info should be equal to ACT_TXT WHERE we need to satisfy the above condition.

Same is the case with "Banner AD Click" the value is 1752 for Entry_ID as we have two same values for entry_id we need to look For Selected_Activity_CD = "CAMP", Activity Info will be Selected_Activity_Tx ..in here it is "s3k265"

In the above 2 cases of same Log_Entry_ID, when Activity is in either of Banner Ad Click, Paid Search Click or Email Click                                    
                                    
For Selected_Activity_CD = "CAMP", Activity Info will be Selected_Activity_Tx

How can i write a case statement. ?
Please guide me.
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Hi,

so you wanted to update actiovity_info based on act_cd only?
or if u have two same entry_id  then you want to update?
select (case when  Entry_id =1711 then
                          ( case when ACT_CD ='Camp' then
                                ACT_TXT
                           else
                                Activity_info
                           end )
              else
                        Activity_info
               end ) as Activity_info from tablename
If you write both togather try this...

select (case when  (Entry_id =1711 or Entry_id =1712)  then
                          ( case when ACT_CD ='Camp' then
                                ACT_TXT
                           else
                                Activity_info
                           end )
              else
                        Activity_info
               end ) as Activity_info from tablename
Avatar of srionline2k6

ASKER

Thanks for quick reply.

It might be two or more. I just mentioned for the sake of understanding.
for your understanding
just a quick overview

Its based on initially Activity value lets say " paid search click" then  look for the Entry_id value it can be anything in here its 1711. Search for the related "1711" values it might be many ( may be 10 but in here only 2 values for sake of understanding) then find the "CAMP" value in the Act_cd column and populate the related ACT_TXT in ACTIVITY_INFO

SELECT

CASE
WHEN ACTIVITY = " paid search click" THEN (CASE WHEN SELECT Entry_id FROM T_ACTIVITY (assuming here 1711 ; it might be more than two) AND CORRESPONDING ACT_CD = "CAMP VALUE" THEN POPULATE ACT_TXT in ACTIVITY_INFO


Please guide me. THanks!!
Thanks Keyu for your quick reply.

But we don't know what will be the value for Entry_cd it might be anything, we can't mention or  provide value for entry_cd. we need to query the related value for that particular instance lets say paid search click. We don't know the value here for Entry_Cd. The query should find the value and search for multiple instances of that value in entry_cd and  look for the CAMP value in Act_cd and populate the corresponding Act_txt value in activity_info.

The key here is finding the related value for the entry_cd without specifying the value in case statement and only populating Activity_info column with Act_txt that has CAMP Value in Act_cd ....

CASE
WHEN ACTIVITY = " paid search click" THEN (CASE WHEN SELECT Entry_id FROM T_ACTIVITY (assuming here 1711 it might be anything some x its not hardcoded the value is not implicitly mentioned) AND CORRESPONDING ACT_CD = "CAMP VALUE" THEN POPULATE ACT_TXT in ACTIVITY_INFO


Hope this helps
P.S: The Entry_id value is not constant for 'Paid search click' , 'Banner ad click' , "Email click'.
It might change. 1711 might be 1752 for paid search click.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I'm not really understanding all the logic here but that's OK.  Since you understand the logic, I suggest you write a function that takes whatever input parameters you need and returns the value you need.  This way you can have very complex logic and not try to force everything into a CASE statement.
How can I write a funciton or stored proc for this ? could you please guide me.

Where are you findiing it difficult to understand the logic?
For example I'm not understanding why you need "CAMP" when you have two entry_ids for a
"Paid search" acitivty.

What if the second 1711 was SAPL?

Are there sort of hard-coded business rules?

>>How can I write a funciton or stored proc for this ?

Create a stored Oracle function:
create or replace myFunc(input1 in varcahr2, input2 in number) return varchar2
is
    someReturnVal varchar2(100);
begin
--do whatever you need to to decide what you need to return
   someReturnVal := 'Hello';
   return someReturnVal;
end;
/


Then any rules/logic/??? can be as complex as you need it to be.
We need to select only CAMP group from other groups and eliminate all those like SAPL or RURL etc. That' s like selecting QUeen victoria from other victoria's . That's the business rule.

Hope this helps.

I greatly appreciate your involvement and i need your help !!
I would greatly appreciate iif you guys have a solution for it. I don't have any clue. Appreciate your help . I am new to this. please help me  in this regard
Let's make sure I understand:

Given some starting activity_id say: 'Paid Search', grab the entry_id, look for a CAMP act_cd with the same entry_id then update the CAMP records activity_info column with what is in the act_txt column?

So based on the image above there is nothing to return for 'Email click' since no CAMP recored exists?
Can I also get your Oracle version (all 4 numbers: ex: 10.2.0.4)?
Yes exactly Absolutely right What you understood from the above is true. if there is no CAMP it should be null.
Activity_info value should populate  ACT_TXT row if the condition is CAMP.


The oracel version is  (9.2.0.1)
Let me see what I can come up with.  Hopefully it will be 9i compatible.
Thanks for your involvement. Greatly appreciate your help
could you please help me out in this matter. I don't know how can i create function
When I get a little time later I'll work on a solution for you.
Thank you Could you please do it ASAP. Appreciate your time
Please understand that the Experts here are all volunteer with outside jobs.  I'll try to get some time later today to work on this for you.
Thanks Sorry
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial