Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
19%
200 - 500 GB
27%
500 - 800 GB
4%
800 - 1200 GB
7%
1200 - 1500 GB
7%
1500 - 2000 GB
12%
> 2000 GB
25%
Total votes: 85

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 13th March 2020, 10:46
JanePapa's Avatar
JanePapa JanePapa is offline
Member
 
Join Date: Nov 2018
Posts: 39
JanePapa is on a distinguished road
Baan: 5 - DB: SQL server - OS: Window
Post SUM Aggregate function
Baan: Infor ERP 10.5
C/S: None/Unknown

Hi all,

I need to use SUM function to calculate the cost of Assembly Order.
I try to develop in LN Studio source code as below but I got the error message "SQLState 42I51: Aggregate function applied to array column (error 302)"

Could you please advise me about this one ?

Select tiasc710.orno,
SUM(tiasc710.amto):r.summary
from tiasc710
where (tiasc710.tror = 38) and (tiasc710.fitr = 52)
and ( tiasc710.cocf = "MATTH01")
group by tiasc710.orno

Many thanks
__________________
Regards,
JanePapa
Reply With Quote
  #2  
Old 13th March 2020, 11:19
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,844
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox Tools 10.7 - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
ARRAY-fields

Hello,

amfr and amto are array fields !

amfr Amount from tcamnt Double 3 x 8 No Yes Not applicab
amto Amount tcamnt Double 3 x 8 No Yes Not applicab

Code:
42I51 - Aggregate function applied to array column
An SQL statement contains an expression in which a set function (for example SUM or MAX) is applied to an array column. A set function is also called an aggregate function. 

Example 1
select max(phoneno)
from   dbtst120
   Column phoneno is an array column. A set function like MAX cannot be applied to an array column. The expression MAX(phoneno) will cause the error: 

SQLState 42I51: Aggregate function applied to array column
Regards
__________________
//Bernd

Last edited by bdittmar : 13th March 2020 at 11:25.
Reply With Quote
Sponsored Links
  #3  
Old 16th March 2020, 03:14
JanePapa's Avatar
JanePapa JanePapa is offline
Member
 
Join Date: Nov 2018
Posts: 39
JanePapa is on a distinguished road
Baan: 5 - DB: SQL server - OS: Window
Hi Bernd,

Could you please advise can I summary array fields ?

I need to use summary amount to show on the report.

Many thanks.
__________________
Regards,
JanePapa
Reply With Quote
  #4  
Old 16th March 2020, 08:03
tmannais's Avatar
tmannais tmannais is offline
Senior Member
 
Join Date: Jul 2017
Location: Bangkok, Thailand
Posts: 274
tmannais is on a distinguished road
Baan: LN 10.5, 10.6, 10.7 - DB: MSSQL - OS: Windows
You can do something like this:

Code:
domain tcamnt sum.amto
select ...
from ...
where ...
selectdo
  sum.amto = sum.amto + tiasc710.amto(1)
endselect
As a result, you will get the sum amount of the field when the query ends.
It's not the best solution though but it works as expected.
Reply With Quote
  #5  
Old 16th March 2020, 08:40
JanePapa's Avatar
JanePapa JanePapa is offline
Member
 
Join Date: Nov 2018
Posts: 39
JanePapa is on a distinguished road
Baan: 5 - DB: SQL server - OS: Window
Hi tmannais,

Thank you for your advise.
I try to this solution, it's work.
__________________
Regards,
JanePapa
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create an AFS pralash AFS/DDC/OLE: Function servers 7 11th October 2018 16:13
Error while opening opening purchase order naga ch Tools Administration & Installation 6 17th June 2016 22:42
How to call AFS in sp24? PV Ramone AFS/DDC/OLE: Function servers 4 2nd September 2008 11:06
xml question steveauckly Tools Development 4 27th July 2004 10:41


All times are GMT +2. The time now is 13:28.


©2001-2018 - Baanboard.com - Baanforums.com