I'm trying to sum amounts in two currencies.
This query works
SELECT -- 68,334
tr.ID AS ID,
TransactionAccountingLine.netamount,
TransactionAccountingLine.amount AS amount, -- for sum
BUILTIN.CURRENCY_CONVERT(transactionLine.foreignamount), -- for sum
TO_NUMBER(BUILTIN.CURRENCY_CONVERT(transactionLine.foreignamount)), -- for sum
--TO_BINARY_FLOAT(TO_NUMBER(CURRENCY_CONVERT(TransactionAccountingLine.amount))) as A_EUR, -- for sum
(TO_NUMBER(BUILTIN.CONSOLIDATE(TransactionAccountingLine.netamount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 252, 'DEFAULT'))) as A_eur,
tr.trandisplayname AS trandisplayname,
tr.trandate AS trandate,
accountingPeriod.periodname,
accountingPeriod.enddate,
tr.TYPE AS transcation_type,
account.custrecord1400 as account_type_order,
account.accttype as account_type,
account.custrecord1399 as verdichting_quicksight,
subsidiary_tl.fullname as subsidiary_fullname,
subsidiary_tl.name as subsidiary_name,
subsidiary_tl.custrecord1403 as gcc_based_view,
subsidiary_tl.custrecord1404 as voys_united_or_windshaft,
transactionLine.entity AS entity,
transactionLine.memo AS memo,
BUILTIN.DF(transactionLine.subsidiary) AS subsidiary,
BUILTIN.DF(TransactionAccountingLine.account) AS account,
TransactionAccountingLine.exchangerate AS exchangerate
FROM
TRANSACTION tr
left join accountingPeriod on
tr.postingperiod = accountingPeriod.id
left join transactionLine on
tr.ID = transactionLine.TRANSACTION
join TransactionAccountingLine on
(transactionLine.TRANSACTION = TransactionAccountingLine.TRANSACTION
and transactionLine.ID = TransactionAccountingLine.transactionline)
left join account on
TransactionAccountingLine.account = account.ID
left join classification class on
transactionLine.class = class.id
left join Subsidiary subsidiary_tl on
transactionLine.subsidiary = subsidiary_tl.ID
WHERE
(
(
TransactionAccountingLine.amount IS NOT NULL
AND NOT(
UPPER(ACCOUNT.accttype) IS NULL
OR UPPER(ACCOUNT.accttype) IN ('NONPOSTING')
)
AND (
(
UPPER(subsidiary_tl.name) IN ('VOYS TELECOM SA')
)
OR UPPER(subsidiary_tl.name) IS NULL
)
AND ACCOUNT.accttype IN ('COGS', 'Expense', 'Income')
AND tr.postingperiod IN ('210')
)
)
;
However when summing I'm getting an error :
SQL Error [10136] [42000]: [NetSuite][SuiteAnalytics Connect JDBC Driver][OpenAccess SDK SQL Engine]Invalid SQL syntax. Set functions (AVG, SUM, VAR_SAMP, VAR_POP, STDDEV_SAMP, STDDEV_POP) are applicable to integer-based fields and numeric data types[10136]
SELECT -- 68,334
-- tr.ID AS ID,
--TransactionAccountingLine.netamount,
sum(TransactionAccountingLine.amount) AS amount, -- for sum
sum(BUILTIN.CURRENCY_CONVERT(transactionLine.foreignamount)), -- for sum
sum(TO_NUMBER(BUILTIN.CURRENCY_CONVERT(transactionLine.foreignamount))), -- for sum
--TO_BINARY_FLOAT(TO_NUMBER(CURRENCY_CONVERT(TransactionAccountingLine.amount))) as A_EUR, -- for sum
sum(TO_NUMBER(BUILTIN.CONSOLIDATE(TransactionAccountingLine.netamount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 252, 'DEFAULT'))) as A_eur,
tr.trandisplayname AS trandisplayname,
tr.trandate AS trandate,
accountingPeriod.periodname,
accountingPeriod.enddate,
tr.TYPE AS transcation_type,
account.custrecord1400 as account_type_order,
account.accttype as account_type,
account.custrecord1399 as verdichting_quicksight,
subsidiary_tl.fullname as subsidiary_fullname,
subsidiary_tl.name as subsidiary_name,
subsidiary_tl.custrecord1403 as gcc_based_view,
subsidiary_tl.custrecord1404 as voys_united_or_windshaft,
transactionLine.entity AS entity,
transactionLine.memo AS memo,
BUILTIN.DF(transactionLine.subsidiary) AS subsidiary,
BUILTIN.DF(TransactionAccountingLine.account) AS account,
TransactionAccountingLine.exchangerate AS exchangerate
FROM
TRANSACTION tr
left join accountingPeriod on
tr.postingperiod = accountingPeriod.id
left join transactionLine on
tr.ID = transactionLine.TRANSACTION
join TransactionAccountingLine on
(transactionLine.TRANSACTION = TransactionAccountingLine.TRANSACTION
and transactionLine.ID = TransactionAccountingLine.transactionline)
left join account on
TransactionAccountingLine.account = account.ID
left join classification class on
transactionLine.class = class.id
left join Subsidiary subsidiary_tl on
transactionLine.subsidiary = subsidiary_tl.ID
WHERE
(
(
TransactionAccountingLine.amount IS NOT NULL
AND NOT(
UPPER(ACCOUNT.accttype) IS NULL
OR UPPER(ACCOUNT.accttype) IN ('NONPOSTING')
)
AND (
(
UPPER(subsidiary_tl.name) IN ('VOYS TELECOM SA')
)
OR UPPER(subsidiary_tl.name) IS NULL
)
AND ACCOUNT.accttype IN ('COGS', 'Expense', 'Income')
AND tr.postingperiod IN ('210')
)
)
group By
tr.trandisplayname ,
tr.trandate ,
accountingPeriod.periodname,
accountingPeriod.enddate,
tr.TYPE ,
account.custrecord1400 ,
account.accttype ,
account.custrecord1399 ,
subsidiary_tl.fullname ,
subsidiary_tl.name ,
subsidiary_tl.custrecord1403 ,
subsidiary_tl.custrecord1404 ,
transactionLine.entity ,
transactionLine.memo ,
BUILTIN.DF(transactionLine.subsidiary) ,
BUILTIN.DF(TransactionAccountingLine.account) ,
TransactionAccountingLine.exchangerate ;
Is there a better way of doing this?