CREATE OR REPLACE VIEW viwcasecube as /*customer 1*/ select c.clientnum,c.casenum,c.casereferencenum, coalesce ((select sum(quantity * amount) as merchandiseamount from tblmerchandise m where m.caseid = c.caseid and m.clientnum=c.clientnum), 0) || coalesce(c.totaladmitted, 0) as casevalue, coalesce(c.totaladmitted, 0) as admitted, coalesce(c.totalrecovery, 0) as recovered, coalesce(c.ageatcase, 0) as ageatcase, 1 as numberofcase, coalesce(ct.value,'na') as casetype, s.value as status, c.opendate, c.closedate, a.value as action, u2.completename as closebyuser, cs.value as casesource, m.value as method, m2.value as method2, c.reportingagentfirstinitial, c.reportingagentlastname, case when c.monthsemployedatcase is null then 'na' else cast(c.monthsemployedatcase as varchar(3)) end as monthsemployedatcase, u1.completename as createdby, st.value as subjecttype, ot.value as offensetype, /*cust*/ custpt.value as patrontype, 'na' as jobtitle, 0 as testscore, coalesce(cust.firstname,'na') as firstname, coalesce(cust.lastname,'na') as lastname, coalesce(cust.address,'na') as address, coalesce(cust.city,'na') as city, coalesce(cust.state,'na') as state, coalesce(cust.zip,'na') as zip, coalesce(crtt.value,'na') as restitutiontype, /* type of restitution tracking */ coalesce(tblsex.value,'na') as gender, coalesce(eth.value,'na') as ethnicity, custmbt.value as militarybranch, custmst.value as militarystatus, coalesce(secagentnum,'not recorded') as secagentnum, l.locationnum, l.name as store, coalesce(l.address,'na') as locationaddress, coalesce(l.city,'na') as locationcity, coalesce(l.state,'na') as locationstate, coalesce(l.zip,'na') as locationzip, d .districtnum, d .districtname as district, r.regionnum, r.regionname as region, dv.divisionnum, dv.divisionname as division, case when c.apprehdate is null then c.opendate else c.apprehdate end as apprehdate, to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year, to_char( coalesce(c.apprehdate, c.opendate),'q') as quarter, to_char( coalesce(c.apprehdate, c.opendate),'MM') as month, to_char( coalesce(c.apprehdate, c.opendate),'D') as weekday, to_char( coalesce(c.apprehdate, c.opendate),'WW') as week, to_char( coalesce(c.apprehdate, c.opendate),'HH24:MI') as time, coalesce(c.sourcedocnum,'none') as sourcedocnum, case coalesce(c.sourcemodule,'n') when 'n' then 'none' when 'i' then 'incident' when 'g' then 'general investigation' when 'e' then 'employee investigation' else 'none' end as sourcemodule, case coalesce(tblcase1.clientnum, 'no') || coalesce(cdacase.clicasenumber, 'no') when 'nono' then 'no' else 'yes' end as civilcase, coalesce(lpr.lpregionnum,'na')as lpregionnum,coalesce(lpr.managername,'na') as lpmanager from tblcase c left outer join tblaction a on c.actionid = a.id and c.clientnum = a.clientnum and 1= a.presentationid left outer join tblmethod m on c.methodid = m.id and c.clientnum = m.clientnum and 1= m.presentationid left outer join tblmethod m2 on c.methodid2 = m2.id and c.clientnum = m2.clientnum and 1= m2.presentationid left outer join tblcasesource cs on c.casesourceid = cs.id and c.clientnum = cs.clientnum and 1= cs.presentationid inner join tbllocation l left outer join tbllpregion lpr on l.lpregionid = lpr.lpregionid and l.clientnum = lpr.clientnum and 1= lpr.presentationid on c.clientnum = l.clientnum and c.locationid = l.locationid inner join tbldistrict d on c.clientnum = d.clientnum and l.districtid = d.districtid and l.regionid = d.regionid and l.divisionid = d.divisionid inner join tblregion r on c.clientnum = r.clientnum and l.regionid = r.regionid and l.divisionid = r.divisionid inner join tbldivision dv on c.clientnum = dv.clientnum and l.divisionid = dv.divisionid left outer join tblcaseresttracktype crtt on c.resttracktypeid = crtt.id and c.clientnum = crtt.clientnum and 1= crtt.presentationid left outer join tblstatus s on c.statusid = s.id and 1= s.presentationid left outer join tblsubjecttype st on c.subjecttypeid = st.id and 1= st.presentationid left outer join tblcasetype ct on c.casetypeid = ct.id and c.clientnum = ct.clientnum and 1= ct.presentationid left outer join tbluser u1 on c.createdbyid = u1.userid and c.clientnum = u1.clientnum and 1= u1.presentationid left outer join tbluser u2 on c.closedby = u2.userid and c.clientnum = u2.clientnum and 1= u2.presentationid left outer join tbloffensetype ot on c.offensetypeid = ot.id and c.clientnum = ot.clientnum and 1= ot.presentationid left outer join tblcustomer cust on c.subjectid = cust.customerid and c.clientnum = cust.clientnum left outer join tblethnicity eth on cust.ethnicityid = eth.id and cust.clientnum = eth.clientnum and 1= eth.presentationid left outer join tblsex on cust.sexid = tblsex.id and 1= tblsex.presentationid left outer join tblmilitarybranch custmbt on cust.militarybranchid = custmbt.id and cust.clientnum = custmbt.clientnum and 1= custmbt.presentationid left outer join tblmilitarystatus custmst on cust.militarystatusid = custmst.id and cust.clientnum = custmst.clientnum and 1= custmst.presentationid left outer join tblpatrontype custpt on cust.patrontypeid = custpt.id and cust.clientnum = custpt.clientnum and 1= custpt.presentationid left join tblexportmarkedrecords tblcase1 on c.clientnum = tblcase1.clientnum and c.caseid = tblcase1.fieldvalue and 'cda'= tblcase1.exportentitynum left join tblcdacases cdacase on c.clientnum = cdacase.clientnum and c.casenum = cdacase.clicasenumber where c.isdeleted = false and c.subjecttypeid = 1/* and c.clientnum = 'waz'*/ union /*assoc 6*/ select c.clientnum,c.casenum, c.casereferencenum,coalesce ((select sum(quantity * amount) as merchandiseamount from tblmerchandise m where m.caseid = c.caseid and m.clientnum=c.clientnum), 0) || coalesce(c.totaladmitted, 0) as casevalue, coalesce(c.totaladmitted, 0) as admitted, coalesce(c.totalrecovery, 0) as recovered, coalesce(c.ageatcase, 0) as ageatcase, 1 as numberofcase, coalesce(ct.value,'na') as casetype, s.value as status, c.opendate, c.closedate, a.value as action, u2.completename as closebyuser, cs.value as casesource, m.value as method, m2.value as method2, c.reportingagentfirstinitial, c.reportingagentlastname, case when c.monthsemployedatcase is null then 'na' else cast(c.monthsemployedatcase as varchar(3)) end as monthsemployedatcase, u1.completename as createdby, st.value as subjecttype, ot.value as offensetype, pt.value as patrontype, jt.value as jobtitle, coalesce(ac.testscore,0) as testscore, coalesce(ac.firstname,'na') as firstname, coalesce(ac.lastname,'na') as lastname, coalesce(ac.address,'na') as address, coalesce(ac.city,'na') as city, coalesce(ac.state,'na') as state, coalesce(ac.zip,'na') as zip, coalesce(crtt.value,'na') as restitutiontype, coalesce(tblsex.value,'na') as gender, coalesce(eth.value,'na') as ethnicity, mbt.value as militarybranch, mst.value as militarystatus, coalesce(secagentnum,'not recorded') as secagentnum, l.locationnum, l.name as store, coalesce(l.address,'na') as locationaddress, coalesce(l.city,'na') as locationcity, coalesce(l.state,'na') as locationstate, coalesce(l.zip,'na') as locationzip, d .districtnum, d .districtname as district, r.regionnum, r.regionname as region, dv.divisionnum, dv.divisionname as division, case when c.apprehdate is null then c.opendate else c.apprehdate end as apprehdate, to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year, to_char( coalesce(c.apprehdate, c.opendate),'q') as quarter, to_char( coalesce(c.apprehdate, c.opendate),'MM') as month, to_char( coalesce(c.apprehdate, c.opendate),'D') as weekday, to_char( coalesce(c.apprehdate, c.opendate),'WW') as week, to_char( coalesce(c.apprehdate, c.opendate),'HH24:MI') as time, coalesce(c.sourcedocnum,'none') as sourcedocnum, case coalesce(c.sourcemodule,'n') when 'n' then 'none' when 'i' then 'incident' when 'g' then 'general investigation' when 'e' then 'employee investigation' else 'none' end as sourcemodule, case coalesce(tblcase1.clientnum, 'no') || coalesce(cdacase.clicasenumber, 'no') when 'nono' then 'no' else 'yes' end as civilcase, coalesce(lpr.lpregionnum,'na')as lpregionnum,coalesce(lpr.managername,'na') as lpmanager from tblcase c left outer join tblaction a on c.actionid = a.id and c.clientnum = a.clientnum and 1= a.presentationid left outer join tblmethod m on c.methodid = m.id and c.clientnum = m.clientnum and 1= m.presentationid left outer join tblmethod m2 on c.methodid2 = m2.id and c.clientnum = m2.clientnum and 1= m2.presentationid left outer join tblcasesource cs on c.casesourceid = cs.id and c.clientnum = cs.clientnum and 1= cs.presentationid inner join tbllocation l left outer join tbllpregion lpr on l.lpregionid = lpr.lpregionid and l.clientnum = lpr.clientnum and 1= lpr.presentationid on c.clientnum = l.clientnum and c.locationid = l.locationid inner join tbldistrict d on c.clientnum = d.clientnum and l.districtid = d.districtid and l.regionid = d.regionid and l.divisionid = d.divisionid inner join tblregion r on c.clientnum = r.clientnum and l.regionid = r.regionid and l.divisionid = r.divisionid inner join tbldivision dv on c.clientnum = dv.clientnum and l.divisionid = dv.divisionid left outer join tblcaseresttracktype crtt on c.resttracktypeid = crtt.id and c.clientnum = crtt.clientnum and 1= crtt.presentationid left outer join tblstatus s on c.statusid = s.id and 1= s.presentationid left outer join tblsubjecttype st on c.subjecttypeid = st.id and 1= st.presentationid left outer join tblcasetype ct on c.casetypeid = ct.id and c.clientnum = ct.clientnum and 1= ct.presentationid left outer join tbluser u1 on c.createdbyid = u1.userid and c.clientnum = u1.clientnum and 1= u1.presentationid left outer join tbluser u2 on c.closedby = u2.userid and c.clientnum = u2.clientnum and 1= u2.presentationid left outer join tbloffensetype ot on c.offensetypeid = ot.id and c.clientnum = ot.clientnum and 1= ot.presentationid left outer join tblassociate ac on c.subjectid = ac.associateid and c.clientnum = ac.clientnum left outer join tblethnicity eth on ac.ethnicityid = eth.id and ac.clientnum = eth.clientnum and 1= eth.presentationid left outer join tblsex on ac.sexid = tblsex.id and 1= tblsex.presentationid left outer join tblmilitarybranch mbt on ac.militarybranchid = mbt.id and ac.clientnum = mbt.clientnum and 1= mbt.presentationid left outer join tblmilitarystatus mst on ac.militarystatusid = mst.id and ac.clientnum = mst.clientnum and 1= mst.presentationid left outer join tblpatrontype pt on ac.patrontypeid = pt.id and ac.clientnum = pt.clientnum and 1= pt.presentationid left outer join tbljobtitle jt on ac.jobtitleid = jt.id and ac.clientnum = jt.clientnum and 1= jt.presentationid left join tblexportmarkedrecords tblcase1 on c.clientnum = tblcase1.clientnum and c.caseid = tblcase1.fieldvalue and 'cda'= tblcase1.exportentitynum left join tblcdacases cdacase on c.clientnum = cdacase.clientnum and c.casenum = cdacase.clicasenumber where c.isdeleted = false and c.subjecttypeid = 6/* and c.clientnum = 'waz'*/ /*other 7*/ union select c.clientnum,c.casenum, c.casereferencenum,coalesce ((select sum(quantity * amount) as merchandiseamount from tblmerchandise m where m.caseid = c.caseid and m.clientnum=c.clientnum), 0) || coalesce(c.totaladmitted, 0) as casevalue, coalesce(c.totaladmitted, 0) as admitted, coalesce(c.totalrecovery, 0) as recovered, coalesce(c.ageatcase, 0) as ageatcase, 1 as numberofcase, coalesce(ct.value,'na') as casetype, s.value as status, c.opendate, c.closedate, a.value as action, u2.completename as closebyuser, cs.value as casesource, m.value as method, m2.value as method2, c.reportingagentfirstinitial, c.reportingagentlastname, case when c.monthsemployedatcase is null then 'na' else cast(c.monthsemployedatcase as varchar(3)) end as monthsemployedatcase, u1.completename as createdby, st.value as subjecttype, ot.value as offensetype, otherpt.value as patrontype, 'na' as jobtitle, 0 as testscore, coalesce(other.firstname,'na') as firstname, coalesce(other.lastname,'na') as lastname, coalesce(other.address,'na') as address, coalesce(other.city,'na') as city, coalesce(other.state,'na') as state, coalesce(other.zip,'na') as zip, coalesce(crtt.value,'na') as restitutiontype, coalesce(tblsex.value,'na') as gender, coalesce(eth.value,'na') as ethnicity, othermbt.value as militarybranch, othermst.value as militarystatus, coalesce(secagentnum,'not recorded') as secagentnum, l.locationnum, l.name as store, coalesce(l.address,'na') as locationaddress, coalesce(l.city,'na') as locationcity, coalesce(l.state,'na') as locationstate, coalesce(l.zip,'na') as locationzip, d .districtnum, d .districtname as district, r.regionnum, r.regionname as region, dv.divisionnum, dv.divisionname as division, case when c.apprehdate is null then c.opendate else c.apprehdate end as apprehdate, to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year, to_char( coalesce(c.apprehdate, c.opendate),'q') as quarter, to_char( coalesce(c.apprehdate, c.opendate),'MM') as month, to_char( coalesce(c.apprehdate, c.opendate),'D') as weekday, to_char( coalesce(c.apprehdate, c.opendate),'WW') as week, to_char( coalesce(c.apprehdate, c.opendate),'HH24:MI') as time, coalesce(c.sourcedocnum,'none') as sourcedocnum, case coalesce(c.sourcemodule,'n') when 'n' then 'none' when 'i' then 'incident' when 'g' then 'general investigation' when 'e' then 'employee investigation' else 'none' end as sourcemodule, case coalesce(tblcase1.clientnum, 'no') || coalesce(cdacase.clicasenumber, 'no') when 'nono' then 'no' else 'yes' end as civilcase, coalesce(lpr.lpregionnum,'na')as lpregionnum,coalesce(lpr.managername,'na') as lpmanager from tblcase c left outer join tblaction a on c.actionid = a.id and c.clientnum = a.clientnum and 1= a.presentationid left outer join tblmethod m on c.methodid = m.id and c.clientnum = m.clientnum and 1= m.presentationid left outer join tblmethod m2 on c.methodid2 = m2.id and c.clientnum = m2.clientnum and 1= m2.presentationid left outer join tblcasesource cs on c.casesourceid = cs.id and c.clientnum = cs.clientnum and 1= cs.presentationid inner join tbllocation l left outer join tbllpregion lpr on l.lpregionid = lpr.lpregionid and l.clientnum = lpr.clientnum and 1= lpr.presentationid on c.clientnum = l.clientnum and c.locationid = l.locationid inner join tbldistrict d on c.clientnum = d.clientnum and l.districtid = d.districtid and l.regionid = d.regionid and l.divisionid = d.divisionid inner join tblregion r on c.clientnum = r.clientnum and l.regionid = r.regionid and l.divisionid = r.divisionid inner join tbldivision dv on c.clientnum = dv.clientnum and l.divisionid = dv.divisionid /* left outer join tbllocation l left outer join tbldistrict d left outer join tblregion r left outer join tbldivision dv on r.divisionid = dv.divisionid and r.clientnum = dv.clientnum on d .regionid = r.regionid and d.clientnum = r.clientnum on l.districtid = d .districtid and l.clientnum = d.clientnum on c.locationid = l.locationid and c.clientnum = l.clientnum */ left outer join tblcaseresttracktype crtt on c.resttracktypeid = crtt.id and c.clientnum = crtt.clientnum and 1= crtt.presentationid left outer join tblstatus s on c.statusid = s.id and 1= s.presentationid left outer join tblsubjecttype st on c.subjecttypeid = st.id and 1= st.presentationid left outer join tblcasetype ct on c.casetypeid = ct.id and c.clientnum = ct.clientnum and 1= ct.presentationid left outer join tbluser u1 on c.createdbyid = u1.userid and c.clientnum = u1.clientnum and 1= u1.presentationid left outer join tbluser u2 on c.closedby = u2.userid and c.clientnum = u2.clientnum and 1= u2.presentationid left outer join tbloffensetype ot on c.offensetypeid = ot.id and c.clientnum = ot.clientnum and 1= ot.presentationid left outer join tblotherperson other on c.subjectid = other.otherpersonid and c.clientnum = other.clientnum left outer join tblethnicity eth on other.ethnicityid = eth.id and other.clientnum = eth.clientnum and 1= eth.presentationid left outer join tblsex on other.sexid = tblsex.id and 1= tblsex.presentationid left outer join tblmilitarybranch othermbt on other.militarybranchid = othermbt.id and other.clientnum = othermbt.clientnum and 1= othermbt.presentationid left outer join tblmilitarystatus othermst on other.militarystatusid = othermst.id and other.clientnum = othermst.clientnum and 1= othermst.presentationid left outer join tblpatrontype otherpt on other.patrontypeid = otherpt.id and other.clientnum = otherpt.clientnum and 1= otherpt.presentationid left join tblexportmarkedrecords tblcase1 on c.clientnum = tblcase1.clientnum and c.caseid = tblcase1.fieldvalue and 'cda'= tblcase1.exportentitynum left join tblcdacases cdacase on c.clientnum = cdacase.clientnum and c.casenum = cdacase.clicasenumber where c.isdeleted = false and c.subjecttypeid = 7 /*and c.clientnum = 'waz'*/ ;