Teradata Addition Issue

Teradata Applications
BOA
Fan

Teradata Addition Issue

Hello!

Any ideas why the following would not work?

     (cast(totalweight as decimal(20,2))/60) + (cast(q4.totalwork as decimal (6,2))/60) + (cast(q5.totalloansqcd as decimal (6,2))/60) as TotalWork

Broken up, these two work separately....

     (cast(totalweight as decimal(20,2))/60) + (cast(q4.totalwork as decimal( (6,2))/60)

and

     (cast(q5.totalloansqcd as decimal (6,2))/60)

When I run them separately I get the correct numbers but when I run it all together no errors but the column is all zeros

3 REPLIES
Junior Contributor

Re: Teradata Addition Issue

Strange, can you show some actual data plus result?

BOA
Fan

Re: Teradata Addition Issue

select q1.reportdate, 
q1.eid,
(cast(totalweight as decimal (20,2))/60) + (cast(q4.totalwork as decimal (6,2))/60) as TotalWork,
q2.name,
q3.totalhrs as Totalhrs,
q3.all_hours_wkd,
cast (q4.meetingtime as decimal (6,2))/60 as meetings,
cast (TotalWork/Totalhrs as decimal (20,2)) as efficiencyfactor

from

(
SELECT
  last_day(LNDSpace.UW_Review_Ticket.Review_Date) as reportdate,
  LNDSpace.UW_Review_Ticket.Underwriter_EID as eid,
  ( sum(case when ( LNDSpace.UW_Review_Ticket.Loan_Type )='Conventional' and ( LNDSpace.UW_Review_Ticket.Review_State )='New' and ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No'
and  ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )<> 'Withdrawn' then 1 else 0 end)
+
sum(case when ( LNDSpace.UW_Review_Ticket.Loan_Type )='VA' and ( LNDSpace.UW_Review_Ticket.Review_Type ) in ('Purchase','Cash Out','IRRRL') and ( LNDSpace.UW_Review_Ticket.Review_State )='New'
and ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No' and ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )<>'Withdrawn' then 1 else 0 end)
+
sum(case when ( LNDSpace.UW_Review_Ticket.Loan_Type )='FHA' and ( LNDSpace.UW_Review_Ticket.Review_Type ) in ('Purchase','Refinance','Streamline') and ( LNDSpace.UW_Review_Ticket.Review_State )='New'
and ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No' and ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )<>'Withdrawn' then 1 else 0 end) )*18
+( sum(case when ( LNDSpace.UW_Review_Ticket.Loan_Type )='Conventional' and ( LNDSpace.UW_Review_Ticket.Review_State )='Resub' and ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No'
and  ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )<> 'Withdrawn' then 1 else 0 end)
+
sum(case when ( LNDSpace.UW_Review_Ticket.Loan_Type )='VA' and ( LNDSpace.UW_Review_Ticket.Review_Type ) in ('Purchase','Cash Out','IRRRL') and ( LNDSpace.UW_Review_Ticket.Review_State )='Resub'
and ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No' and ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )<>'Withdrawn' then 1 else 0 end)
+
sum(case when ( LNDSpace.UW_Review_Ticket.Loan_Type )='FHA' and ( LNDSpace.UW_Review_Ticket.Review_Type ) in ('Purchase','Refinance','Streamline') and ( LNDSpace.UW_Review_Ticket.Review_State )='Resub'
and ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No' and ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )<>'Withdrawn' then 1 else 0 end)
+
sum(case when ( LNDSpace.UW_Review_Ticket.SH_Exception_Reason )='Tax Return Review' then 1 else 0 end) )*30+( sum(case when ( LNDSpace.UW_Review_Ticket.Loan_Type )='Auto Denial'
and ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No' and ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )<> 'Withdrawn' then 1 else 0 end)
+
sum(case when ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No' and ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )='Withdrawn' then 1 else 0 end) )*8
+( sum(case when ( LNDSpace.UW_Review_Ticket.Loan_Type )='VA' and ( LNDSpace.UW_Review_Ticket.Review_State )='New' and ( LNDSpace.UW_Review_Ticket.Review_Type ) in ('Appraisal / LAPP')
and  ( LNDSpace.UW_Review_Ticket.SH_2R_Denial )='No' and ( LNDSpace.UW_Review_Ticket.SH_Appeal )='No' and ( LNDSpace.UW_Review_Ticket.Decision )<>'Withdrawn' then 1 else 0 end) )*40

as totalweight
FROM
  LNDSpace.UW_Review_Ticket
 
GROUP BY
  1,
  2) as q1
 
  left outer join
 
  (select rptdate, eid, qualifier, name, supervisor, group_id, authority
 
  from lndspace.uw_staffing_table) as q2
 
  on q1.reportdate = q2.rptdate
  and q1.eid = q2.eid
 
  left outer join
 
  (select last_day(rptdate) as reportdate, eid, sum(regular_hours+ot_hours) as totalhrs, sum(regular_hours) as all_hours_wkd
 
  from lndspace.uw_hours_table
  group by reportdate, eid) as q3
 
  on q1.reportdate = q3.reportdate
  and q1.eid = q3.eid
 
  left outer join
 
  (select last_day(assist_date) as reportdate, uw_eid,sum(emails+phone_calls+instant_message)*10 as totalwork, sum(meetings) as meetingtime
from lndspace.uw_assist_desk
group by reportdate,uw_eid) as q4

on q1.eid = q4.uw_eid
and q1.reportdate = q4.reportdate

left outer join

(select last_day(review_date) as reportdate, reviewer_eid, count(loan_nbr)*20 as totalloansqcd
from lndspace.uw_qc_data
group by reportdate, reviewer_eid) as q5

on q1.reportdate = q5.reportdate
and q1.eid = q5.reviewer_eid

where q1.reportdate >= date '2015-06-01'

---------------------------------------------------------------------------------------------------------
6/30/2015 93,889 0         Name 90.19 90.19 0 0
6/30/2015 17,491 195.63 Name      181.25 149.25 5.92 0.00
6/30/2015 31,049 133.33 Name 191.25 168.00 17.77 0.00
6/30/2015 90,783 151.17 Name 179.73 179.73 17.08 0.00
6/30/2015 45,748 258.97 Name 246.50 177.00 9.33 0.00
6/30/2015 8,117 190.93 Name 197.47 164.23 14.08 0.00
6/30/2015 7,393 3.37         Name 96.00 96.00 13.25 0.00
6/30/2015 37,540 224.03 Name 194.09 168.00 6.23 4.48

The above is the entire sql and the results with just totalweight and totalwork.  When I add totalloansqcd to the mix the third column is all zeros except for the three people (there are only three people that will have populated numbers with totalloansqcd), however the three numbers are incorrect.  They are correct when I run totalloansqcd by itself.  Hope this isn't too confusing.

Junior Contributor

Re: Teradata Addition Issue

You assign an alias "TotalWork", but there's also a column "q4.totalwork", when you use "TotalWork" in "cast (TotalWork/Totalhrs as decimal (20,2)" it's not referencing the alias, but the base column.

This might cause that strange result, use a different alias and check if this helps.

As a basic rule: never define an alias with the same name as an existing column in any of the tables used in your query if you want to refer to it. There's a reason why Standard SQL doesn't allow reusing aliases within the same query level :-)