|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/12/2006 5:47:00 PM
Posts: 2,
Visits: 1
|
|
We have many legends available, and I would like to determine which ones are being referenced in campaigns and how often. Is there a simple way to do this in the GUI, or a query anyone has already written to obtain this information.
Thanks,
Ryan
|
|
|
|
|
Junior Member
Group: Moderators
Last Login: 7/21/2008 2:27:50 PM
Posts: 24,
Visits: 9
|
|
Dear Ryan,
Thank you for your post. I have forwarded your question on to one of our subject matter experts to answer. Will follow up to ensure an answer gets posted. Carol
|
|
|
|
|
Junior Member
Group: Moderators
Last Login: 7/21/2008 2:27:50 PM
Posts: 24,
Visits: 9
|
|
One option:
select
key8_id lgnd_mast_id
, key9_desc lgnd_name
, key10_id lgnd_det_id
, count(*) usage
from ios_cc_det
where sel_typ_cd = 'ESG'
and key7_id = 'L'
group by 1,2,3
Key8_id contains the lgnd_mast_id and key10_id contains lgnd_det_id
Another option on its way...
|
|
|
|
|
Junior Member
Group: Moderators
Last Login: 7/21/2008 2:27:50 PM
Posts: 24,
Visits: 9
|
|
Two more suggestions:
Have a look over the ios_cc_det table and sample SQLs belows.
1) Legend used in each segment
Select T1.ecamp_id
,T1.ecamp_nm
,T2.seg_id
,T2.seg_nm
,T3.key8_id as "Legend id"
,T3.key9_desc "Legend_desc"
,count(T3.key8_id)
>From ios_ecamp as T1
Left outer join ios_ecamp_x_seg as T2
On T1.div_id = T2.div_id and
T1.ecamp_id = T2.ecamp_id
Left Outer Join ios_cc_det as T3
On T2.usr_sett_id = T3.sett_id and
T3.sel_typ_cd = 'ESG' and
T3.key7_id = 'L'
group by 1,2,3,4,5,6
order by 1,3,5
2) Legend & Ranges used in each segment
Select T1.ecamp_id
,T1.ecamp_nm
,T2.seg_id
,T2.seg_nm
,T3.key8_id as "Legend id"
,T3.key9_desc "Legend_desc"
,T3.key15_desc "Legend range"
,count(T3.key8_id)
From ios_ecamp as T1
Left outer join ios_ecamp_x_seg as T2
On T1.div_id = T2.div_id and
T1.ecamp_id = T2.ecamp_id
Left Outer Join ios_cc_det as T3
On T2.usr_sett_id = T3.sett_id and
T3.sel_typ_cd = 'ESG' and
T3.key7_id = 'L'
group by 1,2,3,4,5,6,7
order by 1,3,5,7
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/12/2006 5:47:00 PM
Posts: 2,
Visits: 1
|
|
Thank you very much, Carol.
If anyone's interested, I modified one of those queries (below) to return id's and names of all folders, campaigns, segments, legends, and legend ranges. It's nice to be able to quickly locate a legend.
Ryan
sel a.fldr_id
, a.fldr_nm
, b.ecamp_id
, b.ecamp_nm
, b.ecamp_cps_run_dt
, c.seg_id
, c.seg_nm
, d.key8_id as Legend_ID
, d.key9_desc as Legend_Desc
, d.key15_desc as Legend_Range
from ios_fldrs a
inner join ios_ecamp b on a.fldr_id = b.fldr_id
left join ios_ecamp_x_seg c on b.div_id = c.div_id
and b.ecamp_id = c.ecamp_id
left join ios_cc_det d on c.usr_sett_id = d.sett_id
and d.sel_typ_cd = 'ESG'
and d.key7_id = 'L'
where legend_desc is not null
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
order by legend_desc, seg_nm, b.ecamp_nm, a.fldr_nm;
|
|
|
|