VIEW: APPS.JTF_PF_HOST_SUMMARY_VL

Source

View Text - Preformatted

SELECT master.day day, master.servername servername, master.serverport serverport, master.jservs jservs, sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, nvl(errata.fails,0) fails, master.pages pages, nvl(errata.badpages,0) badpages from (select day, servername, serverport, count(unique(jservport)) jservs, sum(decode(TECH_STACK, 'JTF', execT, 0)) sum_execT_jtf, sum(decode(TECH_STACK, 'OAF', execT, 0)) sum_execT_oaf, sum(decode(TECH_STACK, 'JTF', 1, 0)) pagehits_jtf, sum(decode(TECH_STACK, 'OAF', 1, 0)) pagehits_oaf, count(unique(pagename)) pages from JTF_PF_ALL_ACTIVITY_VL group by day, servername, serverport ) master, (select day, servername, serverport, count(*) fails, count(unique(pagename)) badpages from JTF_PF_ALL_ACTIVITY_VL where statuscode = -100 group by day, servername, serverport ) errata where master.day = errata.day(+) and master.servername = errata.servername(+) and master.serverport = errata.serverport(+)

View Text - HTML Formatted

SELECT MASTER.DAY DAY
, MASTER.SERVERNAME SERVERNAME
, MASTER.SERVERPORT SERVERPORT
, MASTER.JSERVS JSERVS
, SUM_EXECT_JTF
, SUM_EXECT_OAF
, PAGEHITS_JTF
, PAGEHITS_OAF
, NVL(ERRATA.FAILS
, 0) FAILS
, MASTER.PAGES PAGES
, NVL(ERRATA.BADPAGES
, 0) BADPAGES
FROM (SELECT DAY
, SERVERNAME
, SERVERPORT
, COUNT(UNIQUE(JSERVPORT)) JSERVS
, SUM(DECODE(TECH_STACK
, 'JTF'
, EXECT
, 0)) SUM_EXECT_JTF
, SUM(DECODE(TECH_STACK
, 'OAF'
, EXECT
, 0)) SUM_EXECT_OAF
, SUM(DECODE(TECH_STACK
, 'JTF'
, 1
, 0)) PAGEHITS_JTF
, SUM(DECODE(TECH_STACK
, 'OAF'
, 1
, 0)) PAGEHITS_OAF
, COUNT(UNIQUE(PAGENAME)) PAGES
FROM JTF_PF_ALL_ACTIVITY_VL GROUP BY DAY
, SERVERNAME
, SERVERPORT ) MASTER
, (SELECT DAY
, SERVERNAME
, SERVERPORT
, COUNT(*) FAILS
, COUNT(UNIQUE(PAGENAME)) BADPAGES
FROM JTF_PF_ALL_ACTIVITY_VL
WHERE STATUSCODE = -100 GROUP BY DAY
, SERVERNAME
, SERVERPORT ) ERRATA
WHERE MASTER.DAY = ERRATA.DAY(+)
AND MASTER.SERVERNAME = ERRATA.SERVERNAME(+)
AND MASTER.SERVERPORT = ERRATA.SERVERPORT(+)