I'm trying to create calculations that require me to pull fields from two tables, but am receiving an error (the calculations are highlighted in red below). Also, it would be great to have all of this data in one table to save as a QVD. Any tips or suggestions?
OLEDB CONNECT TO [...]
//-------- Start Multiple Select Statements ------
LOAD
[AcctIndex],
[PeriodID],
[Debits],
[Credits],
[Net]
;
SQL SELECT
[ACTINDX] as [AcctIndex],
[PERIODID] as [PeriodID],
[PERDBLNC] as [Net],
[CRDTAMNT] as [Credits],
[DEBITAMT] as [Debits]
FROM CMH.dbo.GL10110;
LOAD
[Account Number] as [GP Acct],
Left([Account Number],4) as [GP CC],
Right([Account Number],4) as [GP Class],
[TRX Date] as [TrxDate],
Month([TRX Date]) as [TrxMonth],
Year([TRX Date]) as [TrxYear],
Day([TRX Date]) as [TrxDay],
Today() as [Today],
Today()-1 as [Yesterday],
Month(Today())-1 as [Prior Month],
Year(Today())-1 as [Prior Year],
[Open Year] as [GL Year],
[Account Description] as [Description],
[Account Index] as [AcctIndex],
[User Defined 1] as [CPSI Acct],
Right([User Defined 1],3) as [CPSI CC],
Left([User Defined 1],5) as [CPSI Class],
IF([Account Number]=Previous([Account Number]), Previous(Debits)-Previous(Credits),0) as [Opening],
IF([Account Number]=Previous([Account Number]), RangeSum((Debits-Credits),Peek('Balance')),Debits-Credits) as [Balance]
;
SQL SELECT
[Account Number],
[TRX Date],
[Open Year],
[Account Description],
[Account Index],
[User Defined 1]
FROM CMH.dbo.AccountTransactions;
//-------- End Multiple Select Statements ------
