|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/15/2007 4:10:00 PM
Posts: 4,
Visits: 1
|
|
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
|
|
|
|
|
Junior Member
Group: Moderators
Last Login: 7/21/2008 2:27:50 PM
Posts: 24,
Visits: 9
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/15/2007 4:10:00 PM
Posts: 4,
Visits: 1
|
|
|
The SQL that you provided is really neat. It is exactly what I was looking for. Thank you Carol!
|
|
|
|
|
Junior Member
Group: Moderators
Last Login: 7/21/2008 2:27:50 PM
Posts: 24,
Visits: 9
|
|
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
|
|
|
|