sql query issue

[syncdba]

Hi,
I'm working on oracle 9i. I have a table with columns as
person_id Order_no ID2 ID1 Name
-----------------------------------------------------------
002 900 emp002 aud0i Internal
009 1109 per009 aud0e External
009 1109 cmp008 aud0c Partner
007 1009 per011 aud0e External
007 1009 cmp006 aud0c Customer
003 1000 per003 aud0e External
0055 100 per0055 aud0e External

In this table I have person's with two different set of Names. I'm tring to create a sql which shows
if person's ID1 is ("aud0e" and "aud0p")and ID2 Like(%cmp% & %per%) then "Partner"
if person's ID1 is ("aud0e" & "aud0c")and ID2 Like(%cmp% & %per%) then "Customer"
if person's ID1 is "aud0e" only then "External"
if person's ID1 is "aud0i" only then "Internal"

OUTPUT as:
person_id Order_no ID2 ID1 Name O/P
-----------------------------------------------------------
002 900 emp002 aud0i Internal Internal
009 1109 per009 aud0e External Partner
009 1109 cmp008 aud0p Partner Partner
007 1009 per011 aud0e External Customer
007 1009 cmp006 aud0c Customer Customer
003 1000 per003 aud0e External External
0055 100 per0055 aud0e External External
008 1109 per008 aud0e External Partner
008 1109 cmp067 aud0p Partner Partner
001 1090 per001 aud0e External Customer
001 1090 cmp006 aud0c Customer Customer


Thanks in advance..

 

[SantaMufasa]

Syncdba,

Your specs are a bit goofy:

1) How can ID1 = "aud0e" and (at the same moment)
ID1 = "aud0p"?
similarly,

2) How can ID1 = "aud0e" and (at the same moment)
ID1 = "aud0c"?

If you meant or instead of and, then there is still a problem:

If ID1 = "aud0e"...and (ID2 Like '%cmp%' or ID2 like '%per%')

then there is no way that the second "If" (which checks for the same condition) will ever happen.

Could you please refine up your logic and repost with the corrected logic?

Thanks,


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Sync, are you "sync-ing" about this issue, or about "some-sync" else now?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[syncdba]

Hi,
Actually, In the application
All people have 2 level of Audience_Types
1> Person Level
2> Company Level

If a person has Audience_Type as "External" at Person Level
which is "ID2=per00..& ID1=aud0e.." & also "Partner" at Company level which is "ID2=cmp00.. & ID1=aud0p.." Then SQL should show this person as "Partner". SAME CASE WITH CUSTOMER.
If a Peron has Audience_Type as "External" at Person Level
which is "ID2=per00..& ID1=aud0e.." & audience_type at company level is NULL then "External"

ID2--> shows Person & company level where as ID1-->shows audience_type..
Please let me know if you need anything else.
Thanks in advance

FYI,
I have created a sql which is not working properly
it gives
1> if person has "External" & "Partner" both level then "partner"
but if a person has only "External" then also "partner"
I want "External" there.
-----------------------------------------------------------
select distinct a.order_number,a.person_id,a.first_name,a.last_name,a.offering_name,
a.id2,a.id1,a.name,
case
when a.person_id=b.person_id and a.id2 like '%emplo%' then 'Internal'
when a.person_id=b.person_id and (a.id2 like '%compny%' and b.id1 like '%persn%')
and (a.id1='audie000000000001443' and b.id1='audie000000000000002')
and (a.name='Partener' and b.name='External') then 'Partner' else 'External'
end as Audience_type
from
SummaryofEnrollments_nov13 a,SummaryofEnrollments_nov13 b
where a.person_id=b.person_id

 

[SantaMufasa]

Syncdba,

I'm posting the code and results, below, which I devised yesterday (prior to my 13 Nov 06 18:44 post when I discovered the descrepancy between your results and my results). In my code (prior to your recent qualifying your logic specifications), I used an "OR" instead of an "AND" (which could never be true, as I mentioned in my 13 Nov 06 18:44 post).

Since I have some "paying customer" issues to attend to presently, I propose that you peruse my code and results, then you do some detective work to identify the logic that my code is missing:

select x.*
      ,case when id1 in ('aud0e','aud0p')
             and (ID2 Like '%cmp%' or ID2 like '%per%') then 'Partner'
            when id1 in ('aud0e','aud0c')
             and (ID2 Like '%cmp%' or ID2 like '%per%') then 'Customer'
            when id1 = 'aud0e' then 'External'
            when id1 = 'aud0i' then 'Internal'
       end "O/P"
from syncdba x
/

PERSON_ID   ORDER_NO ID2        ID1        NAME       O/P
--------- ---------- ---------- ---------- ---------- --------
        2        900 emp002     aud0i      Internal   Internal
        9       1109 per009     aud0e      External   Partner
        9       1109 cmp008     aud0p      Partner    Partner
        7       1009 per011     aud0e      External   Partner
        7       1009 cmp006     aud0c      Customer   Customer
        3       1000 per003     aud0e      External   Partner
       55        100 per0055    aud0e      External   Partner
        8       1109 per008     aud0e      External   Partner
        8       1109 cmp067     aud0p      Partner    Partner
        1       1090 per001     aud0e      External   Partner
        1       1090 cmp006     aud0c      Customer   Customer

Let us know.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[syncdba]

Hi Mufasa,
Your sql query works fine for the "External" & "Partner"
combination for a person but if a Person has only"External" audience_type on person level then it shows "Partner" for that person also. It should show the "External" for that person.

 

[syncdba]

Hi Mufasa,
Seems single sql query is not working. I'm trying create a procedure with a loop in it which will check for the count of Person_id's.
1> If person_id COUNT = 1 them Name (Ex. Person_id=002 &003)
2> If person_id COUNT>1 then ID2 like'%cmp%' & its corresponding Name (Ex.Person_id=009)

Person_id Order_no ID2 ID1 Name
-----------------------------------------------------------
002 900 emp002 aud0i Internal
009 1109 per009 aud0e External
009 1109 cmp008 aud0c Partner
007 1009 per011 aud0e External
007 1009 cmp006 aud0c Customer
003 1000 per003 aud0e External
0055 100 per0055 aud0e External

I'm not sure if this logic will work but I want to give a try....
Thanks in Advance.

 

[cmmrfrds]

It looks to me that this problem can be solved with an SQL statement. My understanding of your description is that the data needing to be compared is in multiple records. If that is the case then the information can be condensed to 1 record in a subquery (inline view) and then joined back to the main table to make the decisions. Since there are only 3 ID1 values these could be inverted into 3 columns.

something like.
Select A.person_id,
A.order_no,
A.ID2,
..... add the logic here in case statement

From syncdba A
(
Select person_id,
order_no,
max(case when id1 = 'aud0i' then 1 else 0 end) as aud01,
max(case when id1 = 'aud0e' then 1 else 0 end) as aud0e,
max(case when id1 = 'aud0p' then 1 else 0 end) as aud0p,
max(case when (ID2 Like '%cmp%' or ID2 like '%per%') then
1 else 0 end) as ID2Flag
from syncdba
group by person_id, order_no
) aview
On A.person_id = aview.person_id
and A.order_no = aview.order_no

I did not test this but I believe it is close. You may need to put ID2 into 2 columns.

 

[syncdba]

Hi,
I have tried to run the sql subquery. Its not working properly. Here is the sql I have used.

Select A.person_id,
A.offering_no,
A.ID2,
case when id1 in('audie000000000001443','audie000000000000002')
and (ID2 Like '%cmpny%' or ID2 like '%persn%') then 'Partner'
when id1 in('audie000000000001442','audie000000000000002')
and (ID2 Like '%cmpny%' or ID2 like '%persn%') then 'Customer'
when id1 = 'audie000000000000002' then 'External'
when id1 = 'audie000000000000001' then 'Internal'
end "O/P"
From syncdba A
(
Select person_id,
order_no,
max(case when id1 = 'aud0i' then 1 else 0 end) as aud01,
max(case when id1 = 'aud0e' then 1 else 0 end) as aud0e,
max(case when id1 = 'aud0p' then 1 else 0 end) as aud0p,
max(case when (ID2 Like '%cmp%' or ID2 like '%per%') then
1 else 0 end) as ID2Flag
from syncdba
group by person_id, order_no
) aview
On A.person_id = aview.person_id
and A.order_no = aview.order_no

 

[cmmrfrds]

The subquery when combined with the original table will build an intermediate results that look like.

person_id Order_no  ID2   ID1    Name aud0i aud0e aud0c aud0p ID2flag

002   900  emp002 aud0i  Internal 1 0 0 0 0
009  1109 per009 aud0e External  0 1 0 1 1
009  1109 cmp008 aud0p Partner 0 1 0 1 1
007 1009 per011 aud0e External  0 1 1 0 1
007 1009 cmp006 aud0c Customer 0 1 1 0 1
003 1000 per003 aud0e External  0 1 0 0 0
055 100 per0055 aud0e External  0 1 0 0 0
008 1109 per008 aud0e External  0 1 0 1 1
008 1109 cmp067 aud0p Partner 0 1 0 1 1
001 1090 per001 aud0e External  0 1 1 0 1
001 1090 cmp006 aud0c Customer 0 1 1 0 1


I missed 1 column in the previous example. It should have 4 columns.
Select person_id,
order_no,
ID2,
case when aud0e = 1 and aud0c = 1 and ID2flag = 1 then
'Customer'
when aud0e = 1 and aud0p = 1 and ID2flag = 1 then
'Partner'
when aud0e = 1 and aud0p = 0 and aud0i = 0
and aud0p = 0 then 'External'
when aud0e = 0 and aud0p = 0 and aud0i = 1
and aud0p = 0 then 'Internal'
end as 'O/P'
From syncdba A
(
Select person_id,
order_no,
max(case when id1 = 'aud0i' then 1 else 0 end) as aud0i,
max(case when id1 = 'aud0e' then 1 else 0 end) as aud0e,
max(case when id1 = 'aud0c' then 1 else 0 end) as aud0c,
max(case when id1 = 'aud0p' then 1 else 0 end) as aud0p,
max(case when (ID2 Like '%cmp%' or ID2 like '%per%') then
1 else 0 end) as ID2Flag
from syncdba
group by person_id, order_no
) aview
On A.person_id = aview.person_id
and A.order_no = aview.order_no