SUM Function formatted

[lintow]

I have this sql script

select
upper(spriden_first_name)||' '||substr(upper(spriden_mi),1,1),26,' ')||
lpad(xpersre_curr_gross*100,9,0)||lpad(xpersre_employee_contr*100,7,0)||
decode(xpersre_payroll_id,'MN',4,7)||MIN(xpersre_beg_date)||MAX(xpersre_end_date)
from spriden,xpers
where spriden_change_ind is null

I would like to SUM a field with the xpers table.

This is the field: (xpersre_curr_gross) but I would like to keep the format that I have setup in the script.

How can this be done?

Thank you

 

[LKBrwnDBA]

Try this:

Select The_Name
    || To_Char(Curr_Gross,'Fm000000000')
    || To_Char(Employee_Contr,'Fm0000000')
    || Pid||Beg_Dt||End_Dt
  From ( 
Select 
    Upper(Spriden_First_Name)||' '||Substr(Upper(Spriden_Mi),1,1),26,' ') The_Name
  , Sum(Xpersre_Curr_Gross*100) Curr_Gross 
  , Sum(Xpersre_Employee_Contr*100) Employee_Contr
  , Decode(Xpersre_Payroll_Id,'MN','4','7') Pid
  , Min(Xpersre_Beg_Date) Beg_Dt
  , Max(Xpersre_End_Date) End_Dt
  From Spriden,Xpers
 Where Spriden_Change_Ind Is Null
 Group By
    Upper(Spriden_First_Name)||' '||Substr(Upper(Spriden_Mi),1,1),26,' ')
   ,Decode(Xpersre_Payroll_Id,'MN','4','7'))

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[lintow]

I tried that but I was getting some errors when I ran that script.

here is the code I have that works and formats the data the way I want it.

I want to use the SUM, MIN, MAX and Group By to get the results I need

here is the original

select 'E163508R'||xpersre_id||rpad(upper(spriden_last_name)||','||
upper(spriden_first_name)||' '||substr(upper(spriden_mi),1,1),26,' ')||
lpad(xpersre_curr_gross*100,9,0)||lpad(xpersre_employee_contr*100,7,0)||
decode(xpersre_payroll_id,'MN',4,7)||xpersre_beg_date||xpersre_end_date
from spriden,xpers
where spriden_change_ind is null
and spriden_id=xpersre_id
and xpersre_employee_contr<>0

 

[LKBrwnDBA]

What errors?

Did you try the code I posted?

Select The_Name
    || To_Char(Curr_Gross,'Fm000000000')
    || To_Char(Employee_Contr,'Fm0000000')
    || Pid||Beg_Dt||End_Dt
  From ( 
Select 
      'E163508R'||Xpersre_Id||Rpad(Upper(Spriden_Last_Name)||','
    || Upper(Spriden_First_Name)||' '||Substr(Upper(Spriden_Mi),1,1),26,' ') The_Name
     , Sum(Xpersre_Curr_Gross*100) Curr_Gross 
     , Sum(Xpersre_Employee_Contr*100) Employee_Contr
     , Decode(Xpersre_Payroll_Id,'MN','4','7') Pid
     , Min(Xpersre_Beg_Date) Beg_Dt
     , Max(Xpersre_End_Date) End_Dt
  From Spriden,Xpers
 Where Spriden_Change_Ind Is Null
   And Spriden_Id=Xpersre_Id
   And Xpersre_Employee_Contr<>0
 Group By
      'E163508R'||Xpersre_Id||Rpad(Upper(Spriden_Last_Name)||','
    || Upper(Spriden_First_Name)||' '||Substr(Upper(Spriden_Mi),1,1),26,' ') 
      ,Decode(Xpersre_Payroll_Id,'MN','4','7'))

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb