Report on slotted stories

report-on-slotted-stories.sql

/*
   Tweak of unified namespace query for use in plain ol' namespace. Based on query by Aaron Kuehn.
   May 9, 2013: Added dt_cms_schema.Mapping.version to SELECT.
*/

select
    dt_cms_schema.publication.name "Publication Name",
    dt_cms_schema."section".name "Section Name",
    dt_cms_schema.slotreference.name "Slot Name",
    dt_cms_schema.Mapping.version "Version", 
    dt_cms_schema.pagelayout.name "Page Layout Name",
    dt_cms_schema.layoutfull.name "Full Page Layout Name",
    dt_cms_schema.layoutsummary.name "Summary Page Layout Name",
    dbo.story.storyname "Story Name",
    dbo.story.storyid "Story ID",
    dt_cms_schema.Mapping.cmsStory "CMS Story ID",
    dt_cms_schema.cmsStory.story "StoryID From CMSStory",
    NVL(dt_cms_trace.popular.storyHits,0) "Number of Hits"
from
    dt_cms_schema.publication,
    dt_cms_schema."section",
    dt_cms_schema.Mapping,
    dt_cms_schema.cmsStory,
    dt_cms_schema.slotreference,
    dt_cms_schema.pagelayout,
    dbo.story,
    dt_cms_schema.layoutfull,
    dt_cms_schema.layoutsummary,
    dt_cms_trace.popular
where
    dt_cms_schema."section".publicationid = dt_cms_schema.publication.id
and
    dt_cms_schema.Mapping.sectionid = dt_cms_schema."section".id
and
    dt_cms_schema.Mapping.cmsstory = dt_cms_schema.cmsstory.id
and
    dt_cms_schema.Mapping.slotreferenceid = dt_cms_schema.slotreference.id
and
    dt_cms_schema.SlotReference.pageLayoutID = dt_cms_schema.pagelayout.id
and
    dt_cms_schema.cmsstory.story = dbo.story.id
and 
   dt_cms_schema.pagelayout.defaultfulllayoutid = dt_cms_schema.layoutfull.id
and 
   dt_cms_schema.pagelayout.defaultlayoutsummaryid = dt_cms_schema.layoutsummary.id
and
    dt_cms_schema.Mapping.cmsstory=* dt_cms_trace.popular.storyId 
and
    dt_cms_schema.publication.name like '%${Enter Part Of The Desired Publication Name||web}$%'
order by 
    dt_cms_schema.publication.name,
    dt_cms_schema."section".name,
    dt_cms_schema.slotreference.name,
    dt_cms_schema.pagelayout.name

TO DO: Query doesn't distinguish between versions for each slot.