Site Map
just temp:
select (select count(*) from rdb$database where ((m4.deptab <> 'NDX'))) as seq1
  , upper(m2.deptfolder) as seq2
 /* next two same but provides right shape for union */
  , (select count(*) from rdb$database where (m2.deptfolder <> m2.deptfolder)) as seq3
  , upper(m2.deptfolder) as seq4
 , cast(
     case when (m4.deptab = 'NDX') then /* the home page */
       'index/'
     else /* dept, not the home page */
       'L2/+' || m4.langab || '+' || m4.deptab
     end
   as varchar(250)) as dyn
 , cast(
     case when (m4.deptab = 'NDX') then /* the home page */
       'http://' || j.homeurl || '/index.html'
       /* only needed if extra languages besides E
       'http://' || j.homeurl || '/index_' || m4.langab || '.html'
       */
     else
       'http://' || j.homeurl || '/' || m2.deptfolder || '/index_' || m4.langab || '.html'
   end
   as varchar(250)) as url
 , (select max(m5.modstamp) from m5mrpagearticle m5 /* most recent - including void or pending ones */
      where (m5.JOBID = m4.JOBID)
        and (m5.DEPTAB = m4.DEPTAB)
        and (m5.SUBDEPTAB = m4.SUBDEPTAB)
        and (m5.LANGAB = m4.LANGAB)
   ) as modstamp
 , cast(
     case when (m4.deptab = 'NDX') then /* the home page */
       'daily'
     else /* dept, not the home page */
       'weekly'
     end
   as varchar(8)) as changefreq
 , cast(
     case when (m4.deptab = 'NDX') then /* the home page */
       '0.9'
     else /* dept, not the home page */
       '0.7'
     end
   as varchar(6)) as priority
from m4mrpage m4
  inner join m3subdept m3 on (m3.JOBID = m4.JOBID)
    and (m3.DEPTAB = m4.DEPTAB)
    and (m3.SUBDEPTAB = m4.SUBDEPTAB)
    and (m3.VOID = '-')
    and (m3.PENDING = '-')
  inner join m2dept m2 on (m2.JOBID = m3.JOBID)
    and (m2.DEPTAB = m3.DEPTAB)
    and (m2.VOID = '-')
    and (m2.PENDING = '-')
  inner join m1job j on (j.jobid = m4.jobid) and (j.dopagepull = 'X')
where (m4.jobid = :iTempJobid)
  and (m4.deptab = m4.subdeptab) /* L2 goodness */
  and (m4.PENDING = '-')
  and ((m4.ACCESSRIGHTS is null) or (m4.ACCESSRIGHTS <> 'L'))
  and exists(select * from m5mrpagearticle m5
      where (m5.JOBID = m4.JOBID)
        and (m5.DEPTAB = m4.DEPTAB)
        and (m5.SUBDEPTAB = m4.SUBDEPTAB)
        and (m5.LANGAB = m4.LANGAB)
        and (m5.VOID = '-')
        and (m5.PENDING = '-')
        and ( (m5.HPDT = '-')
           or (
                ( (m5.STARTDATE is null) or (m5.STARTDATE < cast('NOW' as date) ) )
            and ( (m5.ENDDATE   is null) or (m5.ENDDATE   > cast('NOW' as date) ) )
              )
            )
      )
union
select (select count(*) from rdb$database where ((m4.deptab <> 'NDX'))) as seq1
  , upper(m2.deptfolder) as seq2
  , (select count(*) from rdb$database where (m3.subdeptfolder <> m2.deptfolder)) as seq3
  , upper(m3.subdeptfolder) as seq4
 , cast(
     'L3/+' || m4.langab || '+' || m4.deptab || '+' || m4.subdeptab
   as varchar(250)) as dyn
 , cast(
     'http://' || j.homeurl || '/' || m2.deptfolder || '/' || m3.subdeptfolder || '/' || 'index_' || m4.langab || '.html'
   as varchar(250)) as url
 , (select max(m5.modstamp) from m5mrpagearticle m5 /* most recent - including void or pending ones */
      where (m5.JOBID = m4.JOBID)
        and (m5.DEPTAB = m4.DEPTAB)
        and (m5.SUBDEPTAB = m4.SUBDEPTAB)
        and (m5.LANGAB = m4.LANGAB)
   ) as modstamp
 , cast(
       'weekly'
   as varchar(8)) as changefreq
 , cast(
       '0.5'
   as varchar(6)) as priority
from m4mrpage m4
  inner join m3subdept m3 on (m3.JOBID = m4.JOBID)
    and (m3.DEPTAB = m4.DEPTAB)
    and (m3.SUBDEPTAB = m4.SUBDEPTAB)
    and (m3.VOID = '-')
    and (m3.PENDING = '-')
  inner join m2dept m2 on (m2.JOBID = m3.JOBID)
    and (m2.DEPTAB = m3.DEPTAB)
    and (m2.VOID = '-')
    and (m2.PENDING = '-')
  inner join m1job j on (j.jobid = m4.jobid) and (j.dopagepull = 'X')
where (m4.jobid = :iTempJobid)
  and (m4.deptab <> m4.subdeptab) /* L3 goodness */
  and (m4.PENDING = '-')
  and ((m4.ACCESSRIGHTS is null) or (m4.ACCESSRIGHTS <> 'L'))
  and exists(select * from m5mrpagearticle m5
      where (m5.JOBID = m4.JOBID)
        and (m5.DEPTAB = m4.DEPTAB)
        and (m5.SUBDEPTAB = m4.SUBDEPTAB)
        and (m5.LANGAB = m4.LANGAB)
        and (m5.placementab <> 'S') /* not sponsors or affiliates items - they dont have urls as L3s */
        and (m5.VOID = '-')
        and (m5.PENDING = '-')
        and ( (m5.HPDT = '-')
           or (
                ( (m5.STARTDATE is null) or (m5.STARTDATE < cast('NOW' as date) ) )
            and ( (m5.ENDDATE   is null) or (m5.ENDDATE   > cast('NOW' as date) ) )
              )
            )
      )
union
select (select count(*) from rdb$database where ((m4.deptab <> 'NDX'))) as seq1
  , upper(m2.deptfolder) as seq2
  , (select count(*) from rdb$database where (m3.subdeptfolder <> m2.deptfolder)) as seq3
  , upper(m3.subdeptfolder) as seq4
 , cast(
     'L3/Display+' || m5.langab || '+' || m5.deptab || '+' || m5.subdeptab || '+' || cast(m5.mrid as varchar(10))
   as varchar(250)) as dyn
 , cast(
     'http://' || j.homeurl || '/' || m2.deptfolder
     || case when (m5.deptab = m5.subdeptab) then '' else '/' || m3.subdeptfolder end
     || '/' || cast(m5.mrid as varchar(10)) || '.html'
   as varchar(250)) as url
 , (select mr.modstamp from mediarel mr where (mr.job = m5.jobid) and (mr.id = m5.mrid)
   ) as modstamp
 , cast(
       'monthly'
   as varchar(8)) as changefreq
 , cast(
       '0.4'
   as varchar(6)) as priority
from m5mrpagearticle m5
  inner join m4mrpage m4 on (m4.JOBID = m5.JOBID)
    and (m4.DEPTAB = m5.DEPTAB)
    and (m4.SUBDEPTAB = m5.SUBDEPTAB)
    and (m4.LANGAB = m5.LANGAB)
    and (m4.PENDING = '-')
    and ((m4.ACCESSRIGHTS is null) or (m4.ACCESSRIGHTS <> 'L'))
  inner join m3subdept m3 on (m3.JOBID = m4.JOBID)
    and (m3.DEPTAB = m4.DEPTAB)
    and (m3.SUBDEPTAB = m4.SUBDEPTAB)
    and (m3.VOID = '-')
    and (m3.PENDING = '-')
  inner join m2dept m2 on (m2.JOBID = m3.JOBID)
    and (m2.DEPTAB = m3.DEPTAB)
    and (m2.VOID = '-')
    and (m2.PENDING = '-')
  inner join m1job j on (j.jobid = m5.jobid) and (j.dopagepull = 'X')
where (m5.jobid = :iTempJobid)
  and (m5.VOID = '-')
  and (m5.PENDING = '-')
  and ( (m5.PLACEMENTAB = 'B') /* accept pages for side articles */
    or ((m5.PLACEMENTAB = 'A') /* accept pages for center articles having shorttext */
         and exists (select * from mediarel mr
                   where (mr.job = m5.jobid) and (mr.id = m5.mrid)
                     and (strlen(cast(mr.shorttext as varchar(100))) > 10) )
        )
/* accept outside links */
/*
    or ((m5.PLACEMENTAB = 'L')
         and exists (select * from mediarel mr
                   where (mr.job = m5.jobid) and (mr.id = m5.mrid)
                     and (mr.text containing j.homeurl) )
        )
*/
   )
  and ( (m5.HPDT = '-')
     or (
          ( (m5.STARTDATE is null) or (m5.STARTDATE < cast('NOW' as date) ) )
      and ( (m5.ENDDATE   is null) or (m5.ENDDATE   > cast('NOW' as date) ) )
        )
      )