Analytics Software

This is my first SQR report so I am probably missing something very simple. I have been working to solve this error on and off over the last two weeks without success.
I have the SQR for peoplesoft manual and have looked through it, ittoolbox and the forums here without success. I have however narrowed it down to what is causing it.

If I do not enter in the JES2 value into the array (see code below) the code will run as it should. So the error has to do with the way that the code is entering or extracting the JES2 value into or from the array.


I have attached my the error and SQR Code below.

Any help you can provide would be greatly appreciated!!
Jason



(SQR 5528) ODBC SQL dbexec: SQLExecute error 0 in cursor 1:
[DataDirect][ODBC Oracle driver]Data type for parameter 1 has changed since first SQLExecute call.

Error on line 78:
(SQR 3723) Problem executing cursor.

SQR: Program Aborting




#define col1 1 !Team
#define col2 6 !Supervisor

#define Srcs 199,200
#define PrInd 'H'

#define Lpp_Array_Size 3 !----This is the number of teams you have listed in your array. it is the total number of rows in your array. if you have more rows listed than are in your array you will get a precision error from oracle.
#define srclog IN({Srcs})
#define purrefi In({PrInd})

#define FDPMnth '2008/02/01' !yyyy/mm/dd First Day Previous Month
#define FDCMnth '2008/03/01' !yyyy/mm/dd First Day Current Month
#define MDPMnth '2008/02/15' !yyyy/mm/dd Middle Day (15th) Previous Month For Pulling Open Pipe In Previous Month
#define CnlDtPvMnth '2008/02/16' !yyyy/mm/dd Middle Day +1 (16th) Previous Month For Pulling Open Pipe In Previous Month (used as cancel date and Closing Date)
#define FDPYear '2007/01/01' !yyyy/mm/dd First Day Previous Year
#define FdFrthMthPv '2007/10/01' !This must be from 4th Month Previous
#define FdThdMthPv '2007/11/01' !This must be from 4th Month Previous
#define FdPvMthFore 'Feb 01 2008 12:00AM' !First Day Previous Month Forecast, Format MUST be in single 'quotes' and 'Mar 01 2008 12:00AM'
#define FdCtMthFore 'Mar 01 2008 12:00AM' !First Day Current Month Forecast, Format MUST be in single 'quotes' and 'Mar 01 2008 12:00AM'

!--------------------------------------------------------------------------- Do_Begin-Program
!---------------------------------------------------------------------------
!---------------------------------------------------------------------------
Begin-Program
Do create_LPC_array
Do add_teams_to_array
Do get_team
End-Program
!--------------------------------------------------------------------------- Do create_LPC_array
!---------------------------------------------------------------------------
!---------------------------------------------------------------------------
Begin-Procedure create_LPC_array
Create-array
name=LPP_array
size={Lpp_Array_Size}
extent=10000
field=lpc_team:char

End-Procedure create_LPC_array
!--------------------------------------------------------------------------- Do add_teams_to_array
!---------------------------------------------------------------------------
!---------------------------------------------------------------------------
Begin-Procedure add_teams_to_array
let #LppRow=0
let $aryteam = 'JES2'
put $aryteam into Lpp_Array(#LppRow) Lpc_Team

Add 1 to #LppRow
let $aryteam = 'JE4'
put $aryteam into Lpp_Array(#LppRow) Lpc_Team

Add 1 to #LppRow
let $aryteam = 'JH1'
put $aryteam into Lpp_Array(#LppRow) Lpc_Team

Add 1 to #LppRow
let $aryteam = 'JH4'
put $aryteam into Lpp_Array(#LppRow) Lpc_Team

let #LppRow=0 !resets the cursor/row position to the first record again
End-Procedure add_teams_to_array
!--------------------------------------------------------------------------- Do get_team
!---------------------------------------------------------------------------
!---------------------------------------------------------------------------
Begin-Procedure get_team
let #t = 0
while #t < {Lpp_Array_Size}
Get $Team from LPP_array(#t) Lpc_team
!Print $Team (,40)
Do get_open_loans
add 1 to #t
end-while
End-Procedure get_team
!--------------------------------------------------------------------------- Do get_open_loans
!---------------------------------------------------------------------------
!---------------------------------------------------------------------------
Begin-Procedure get_open_loans
Begin-Select !distinct
AL1.Loan_Cnslr &LppLpName (+1,1)
count(AL1.Reg_No) &LppOpen_loans (,35)
FROM DM_CLOSING.CLOSING_DATA AL1, DM_REFERENCE_INFO.OCCUP_TYPE AL2
WHERE (AL1.OCC_STAT=AL2.OCC_STAT)
AND AL1.LOAN_SR_CNSLR=$Team
AND AL1.TIER IN ('1','2','4')
And AL1.reg_dtr1 > to_date ({FDPYear}, 'yyyy/mm/dd') !to_date ('2007/01/01', 'yyyy/mm/dd')
And AL1.reg_dtr1 <= to_date ({MDPMnth}, 'yyyy/mm/dd') !to_date ('2008/02/15', 'yyyy/mm/dd')
And (AL1.Closing_Dtr1 > to_date ({CnlDtPvMnth}, 'yyyy/mm/dd') or AL1.Closing_Dtr1 IS NULL) !to_date ('2008/02/16', 'yyyy/mm/dd')
And (Cnl_Dtr1 > to_date ({CnlDtPvMnth}, 'yyyy/mm/dd') or AL1.cnl_dtr1 IS NULL) !to_date ('2008/02/16', 'yyyy/mm/dd')
And AL1.ID_SRC {srclog}!IN (118, 119)
And AL2.PURCH_REFI_IND {purrefi}!IN ('P', 'R')
And (NOT AL2.CONSTRUCTION_FLAG='True')
GROUP BY AL1.Loan_Cnslr
End-Select

End-Procedure get_open_loans