Skip to Content
0
Former Member
Apr 28, 2014 at 08:29 PM

New Field not Showing up in Report, part II.

48 Views

I had this issue with another report, and I was told that I hadn't brought my new field down all the way through to the final Select. With this report, I thought I had done just that, but the new field is still not showing up. I have pasted the code below with my additions highlighted. Can someone tell me what I am doing wrong? Thanks.

WITH SEL_ACCTS AS(
SELECT
CASE
WHEN RMSCUSTTYP = 'I'
THEN
CASE
WHEN
Trim(TRIM(RMSLASTNM) || ', ' || TRIM(RMSFIRSTNM)) <> ','
THEN
Trim(TRIM(RMSLASTNM) || ', ' || TRIM(RMSFIRSTNM))
ELSE
''
END
ELSE
RMSCMPNAME
END AS "DEBTOR NAME",
RMSACCTNUM AS "CLIENT ACCOUNT#",
rmslastpmt,rmsjdgdate,
CASE WHEN RMSJDGDATE = 0 THEN (RMSCHGAMT+RMSASSCCST+RMSACRDINT+UDCCOSTS1+UDCCOSTS2)-(RMSRCVPCPL+
RMSXCSRCVS+RMSRCVDCST+RMSRCVDINT+UDCRECCS1+UDCRECCS2)
ELSE (RMSJDGCHOF+RMSJDGCSTS+RMSJDGINTS+JDGCOSTS1+JDGCOSTS2)-(JDGRECVRYS+
JDGXCSRCVS+JDGRCVCSTS+JDGRCVINTS+JDGRECCS1+JDGRECCS2)
END AS BALANCE,
A.RMSFILENUM,
RMSDATEASG AS PLC_DT,
ATTRNYCODE,
RMSSTATECD As "State Code",
RAND() AS RANDNUMGEN
FROM
REPORTING.RMASTER As A
left join reporting.rcomker b
on a.rmsfilenum = b.rmsfilenum
left join reporting.rprdbal c
on a.rmsfilenum = c.rmsfilenum
WHERE
RMSDATEASG < REPLACE(CHAR(CURRENT DATE - 180 DAYS, ISO),'-','')
AND recordtype = 'D'
and ATTRNYCODE IN ({?AGCY_CODE})),


GET_ACCTS AS(
SELECT
"DEBTOR NAME",
"CLIENT ACCOUNT#",plc_dt,
case when plc_dt > 0 then Cast(Timestamp(plc_dt||'000000') As Date) end AS "PLACED DATE",
ATTRNYCODE,
"State Code",
A.RMSFILENUM,case when rmslastpmt > 0 then Cast(Timestamp(rmslastpmt||'000000') As Date) end as rmslastpmt, rmsjdgdate,balance
FROM
SEL_ACCTS AS A
ORDER BY RANDNUMGEN
FETCH FIRST {?#_of_Accts} Rows Only),

financials as (
select "DEBTOR NAME",
"CLIENT ACCOUNT#",
"PLACED DATE",
a.ATTRNYCODE,
A.RMSFILENUM,rmslastpmt,case when rmsjdgdate > 0 then Cast(Timestamp(rmsjdgdate||'000000') As Date) end as rmsjdgdate,balance,
sum(case when e.rmstrancde between '3A' and '39' or e.rmstrancde in ('1G','1U') then e.rmstranamt end) as costspent,
sum(case when (e.rmstrancde between '5A' and '69' or e.rmstrancde = '1P') and postdate > rmsjdgdate then
e.rmstranamt
end) as pmtamt,
sum(case when e.rmstrancde = '5C' then e.rmstranamt end) as costrecvd

from get_accts a
left join reporting.rfinanl e
on a. rmsfilenum = e.rmsfilenum
and (e.rmstrancde between '3A' and '69'
or e.rmstrancde in ('1P','1G','1U'))
and postdate > plc_dt and a.attrnycode = e.attrnycode
group by
"DEBTOR NAME",
"CLIENT ACCOUNT#", "PLACED DATE",
a.ATTRNYCODE,
A.RMSFILENUM,rmslastpmt,case when rmsjdgdate > 0 then Cast(Timestamp(rmsjdgdate||'000000') As Date) end ,balance )

select "DEBTOR NAME",
"CLIENT ACCOUNT#",
"PLACED DATE",
"State Code",
a.ATTRNYCODE,rmslastpmt, rmsjdgdate,balance,c.rmsbalance as plc_bal,costspent, pmtamt,costrecvd,
Cast(Timestamp(max(case when b.hstnewvalu in ( 'L31') then b.rmstrandte else null end)||'000000') As Date) as servicedate,
Cast(Timestamp(max(case when b.hstnewvalu in ( 'L13','P13') then b.rmstrandte else null end)||'000000') As Date) as garnishdate,
Cast(Timestamp(max(case when b.hstnewvalu in ( 'L14','P14') then b.rmstrandte else null end)||'000000') As Date) as levydate,
Cast(Timestamp(max(case when b.hstnewvalu in ( 'L17','P17') then b.rmstrandte else null end)||'000000') As Date) as suitfiledate,
Cast(Timestamp(max(case when b.hstnewvalu in ( 'L12','P12') then b.rmstrandte else null end)||'000000') As Date) as liendate

from financials a
left join reporting.rhistfl b
on a.rmsfilenum = b.rmsfilenum
and b.RMSTRANCDE = 'MT' And b.RMSFLDIMPL = 'RMSSTATUS'
and b.hstnewvalu in ('L12','L13','L14','L17','P12','P13','P14','P17','L19','PJC','L31')
left join reporting.rhistfl c
on a.rmsfilenum = c.rmsfilenum
and c.RMSTRANCDE = 'MR' AND c.RMSFLDIMPL = 'ATTRNYCODE' and c.hstnewvalu in ({?AGCY_CODE})
group by
"DEBTOR NAME",
"CLIENT ACCOUNT#", "PLACED DATE",
a.ATTRNYCODE,rmslastpmt,rmsjdgdate ,balance,c.rmsbalance,costspent, pmtamt,costrecvd, "State Code"