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
18%
200 - 500 GB
29%
500 - 800 GB
3%
800 - 1200 GB
8%
1200 - 1500 GB
8%
1500 - 2000 GB
12%
> 2000 GB
22%
Total votes: 76

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 9th August 2019, 16:11
RavCOder's Avatar
RavCOder RavCOder is offline
Member
 
Join Date: Jul 2019
Posts: 87
RavCOder is on a distinguished road
Baan: 10.3 - DB: SQL Server - OS: Windows
Smile Sql query sum
Baan: Other/Unknown
C/S: None/Unknown

Hi,

I declared this function in program section in a script ( General library):

Code:
function sum.quantity(){
select tdsls400.item, tccom100.bpid
from tdsls400, tccom100

selectdo

db.set.to.default(ttdsls400)
db.set.to.default(ttccom100)

(select sum(tdsls400.qoor)
where tdsls400.item > 0)

endselect

}
In the declaration I put tables (tdsls400,tdsls401,tccom100 and tdsls900)
My objctive is to sum quantity for each article selected by id (business partner), and insert the result in field created in other table (tdsls900).

Regards,

---RavCoder----

Last edited by mark_h : 12th August 2019 at 02:46. Reason: code tags for readability
Reply With Quote
  #2  
Old 9th August 2019, 22:19
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,808
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
Purchase statistic ?

Hello,

this requirement can be solved with the LN purchise/sales statistics.

Purchised/Sold quantity by business partner and item per period or item per period a.s.o.
(tdpst and tdsst)

Or is there any other reason to do it with an own developed session ?

Regards
__________________
//Bernd

Last edited by bdittmar : 10th August 2019 at 18:26. Reason: Also sales
Reply With Quote
  #3  
Old 26th August 2019, 09:42
RavCOder's Avatar
RavCOder RavCOder is offline
Member
 
Join Date: Jul 2019
Posts: 87
RavCOder is on a distinguished road
Baan: 10.3 - DB: SQL Server - OS: Windows
Sql query sum

Hi ,
I want create a new script that sum quantities ordered for article and customer.
But I don't idea how to create this.
Sorry but I read the documentation and I don't know how to develop in this.

Regards,
RavCoder
Reply With Quote
  #4  
Old 26th August 2019, 13:30
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,365
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I would check into what Bernd recommended. Search the board.

In the meantime in 4c4 I would user something like this.
Code:
function sum.quantity(){
select tdsls400.item, tccom100.bpid
from tdsls400, tccom100
where ......whatever filter you have and the link between 2 tables...
group by select tdsls400.item, tccom100.bpid     | My guess you would have multiples
selectdo

select sum(a.qoor):sum.qoor  | Declare sum.qoor as same domain as tdsls400.qoor
from tdsls400 a, tccom100 b
where a.item = :tdsls400.item
and link tdsls400 and tccom100 using the aliases
and b.bpid = :tccom100.bpid
selectdo
endselect

| Send to report or go write the sum.qoor into another table.

endselect

}

You might have to work some bugs out of it depending on needs, but this is the basics I would do in 4c4. But I would search this forum for purchase or sales statistics to see what comes up.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #5  
Old 26th August 2019, 14:39
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,808
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
tssls400 is sales header !

Hello,
table tdsls400 is sales header, the item is stored in tdsls401.

Regards
__________________
//Bernd
Reply With Quote
Sponsored Links
  #6  
Old 26th August 2019, 15:13
RavCOder's Avatar
RavCOder RavCOder is offline
Member
 
Join Date: Jul 2019
Posts: 87
RavCOder is on a distinguished road
Baan: 10.3 - DB: SQL Server - OS: Windows
Sql query sum

I created this session but don't show me datas from both tables (tdsls400 and tdsls401).
My objective is read these tables in a new session , then created a script (maybe?) to sum quantity ordered for item and customer e memorize in another table ( that I have created yet).
But this session should be a print report that show me a summary of data who I have created before ( they aren't tdsls400 and tdsls401).
Regards,
RavCoder
Attached Images
File Type: png Capture.PNG (35.7 KB, 7 views)
Reply With Quote
  #7  
Old 26th August 2019, 16:21
RavCOder's Avatar
RavCOder RavCOder is offline
Member
 
Join Date: Jul 2019
Posts: 87
RavCOder is on a distinguished road
Baan: 10.3 - DB: SQL Server - OS: Windows
Do you write your code into a script right?
Reply With Quote
  #8  
Old 26th August 2019, 19:56
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,365
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Yes - my code would go into a 4gl script to load the table. Then I would typically have display and report sessions for the data.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
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
SQL Query for SUM king1980 Tools Development 4 3rd April 2019 08:59
sql query srikarthy Tools Development 6 25th August 2010 09:46
Strange error while importing tables: Can not create index 9999 eppesuiG Tools Administration & Installation 8 17th March 2008 04:35
Sum by year in Baan SQL Query TAAtic Tools Administration & Installation 1 16th November 2002 01:49


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


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