RE: Legends Report

Carol Barker — Tue, 13 Mar 2007 09:26:21 GMT

Glad to help! And, I'll thank our PS Consultant as well who provided the SQL. I hope so far, you've found this message board to be helpful and if so, please spread the word!!
thanks,
Carol

RE: Legends Report

kafrynrich — Tue, 13 Mar 2007 09:23:03 GMT

The SQL that you provided is really neat. It is exactly what I was looking for. Thank you Carol!

RE: Legends Report

Carol Barker — Tue, 13 Mar 2007 09:05:13 GMT

Hello Kathryn,
Thanks for your post. I have asked one of our senior PS consultants to help answer your question and they responded with the following. Please let me know if this works for you.
thanks,
Carol

select
g.lgnd_grp_desc ,
a.lgnd_desc ,
max(case when g.lgnd_typ_Cd = 'S' then 'S' else '' end) as _Stand ,
max(case when g.lgnd_typ_Cd = 'M' then 'M' else '' end) as _Filter ,
max(case when g.lgnd_typ_Cd = 'L' then 'L' else '' end) as _link ,
max(case when modify_effect_cd = 'D' THEN 'D' else '' end) _Display ,max(case when modify_effect_cd = 'R' THEN 'A' else '' end) _Attrib ,
max(case when a.fld_typ_cd = 'C' THEN 'C' else '' end) as _char ,
max(case when a.fld_typ_cd = 'D' THEN 'D' else '' end) as _date ,max(case when a.fld_typ_cd = 'N' THEN 'N' else '' end) as _num ,
max(case WHEN a.cust_rollup_func = '*' then '*' else ''end) as _None ,
max(case WHEN a.cust_rollup_func = 'sum' then 'S' else ''end) as _sum ,
max(case WHEN a.cust_rollup_func = 'avg' then 'A' else '' end) as _avg ,max(case WHEN a.cust_rollup_func = 'count' then 'C' else '' end) as _cnt ,
max(case WHEN a.cust_rollup_func = 'min' then 'min' else '' end) as _min ,
max(case WHEN a.cust_rollup_func = 'max' then 'max' else ''end) as _max,
max(case when a.rollup_typ_cd = 'A' then 'A' else '' end) as agg ,
max(case when a.rollup_typ_cd = 'E' then 'E' else '' end) as _ever ,
max(case when a.lgnd_fld_typ_cd = 'C' then 'C' else '' end) as _con,
max(case when a.lgnd_fld_typ_cd = 'E' then 'E' else '' end) as _enum ,
max(case when a.lgnd_fld_typ_cd = 'M' then 'M' else '' end) as _mult ,
max(case when b.freq_lev_cd = 'C' then 'C' else '' end) as _current ,
max(case when b.freq_lev_cd = 'D' then 'D' else '' end) as _day ,
max(case when b.freq_lev_cd = 'W' then 'W' else '' end) as _week ,
max(case when b.freq_lev_cd = 'M' then 'M' else '' end) as _month ,
max(case when b.freq_lev_cd = 'Q' then 'Q' else '' end) as _quarter ,
max(case when b.freq_lev_cd = 'Y' then 'Y' else '' end) as _year ,
max(case when b.cust_lev_cd = 'C' then 'C' else '' end) as _Client ,
max(case when b.cust_lev_cd = 'A' then 'A' else '' end) as _account,
t.tbl_nm ,
t.fld_nm,
count(*)
from ios_lgnd_mast a
left outer join ios_lgnd_det b
on a.div_id = b.div_id
and a.lgnd_mast_id = b.lgnd_mast_id
left outer join ios_lgnd_grps g
on a.div_id = g.div_id
and a.lgnd_grp_id = g.lgnd_grp_id
left outer join ios_lgnd_tbls t
on b.div_id = b.div_id
and b.lgnd_mast_id = t.lgnd_mast_id
and b.lgnd_det_id = t.lgnd_det_id
left outer join ios_lgnd_usr_dts u
on u.div_id = b.div_id
and u.lgnd_mast_id = t.lgnd_mast_id
and u.lgnd_det_id = t.lgnd_det_id
left outer join ios_lgnd_rngs r
on u.div_id = r.div_id
and u.lgnd_mast_id = r.lgnd_mast_id
and u.lgnd_det_id = r.lgnd_det_id
and u.lgnd_usr_dt_id = r.lgnd_usr_dt_id where a.div_id = 1
group by
g.lgnd_grp_desc
,a.lgnd_desc
,a.lgnd_fld_typ_cd
,t.tbl_nm
,t.fld_nm

Legends Report

kafrynrich — Mon, 12 Mar 2007 10:08:39 GMT

I have a manual report that was created to help show where the database fields are in the legends mapping. I have gotten behind on it since legends and columns are changed and added frequently. Does any one have SQL that they use for a report that is similar to this or do you have any suggestions on a better way to keep up with this report?

Here are the columns that I use and a small sample (I had to pst it this way so that it would show correctly in the posting page):

Columns:
Module
Tab
Table / View
TCRM Segmentation Selection
SQL Field added through TCRM
Category Definition
Time Interval
Field Reference for Time Interval

Data Row 1:
Customer
Absolute
I0023B_ADR
State
ST_PVN_CD
Household
Current
NA

Data Row 2:
Store Location
Region/District
i0050C_LCT
District
DIT_NBR
Store Activity
Year
CAL_DT

Thanks,
Kathryn

Teradata Forums / Marketing Tips and CRM Best Practices / Teradata / Legends Report / Latest Posts

RE: Legends Report

RE: Legends Report

RE: Legends Report

Legends Report