Baanboard.com

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

User login

Frontpage Sponsor

Main

Poll
As a Customer What would do to keep your ERP Implementation intact
Proactively define Business Process-- Take the Project Ownership
56%
Handover everything to System Integrator from drawing BP till implementation of ERP
11%
Hire more inhouse skilled & capable IT Resource to work directly with SI
33%
Rely on SI Architects/Consultants
0%
Total votes: 9

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 31st October 2020, 12:08
vivet2 vivet2 is offline
Junior Member
 
Join Date: Oct 2020
Posts: 7
vivet2 is on a distinguished road
Baan: 10.3 - DB: INFOR LN - OS: WINDOWS 10
Easy SQL

A new blog entry has been added:

Easy SQL

Quote:
hi,
I want to fech some data through easy sql but i am not able to make query in where condition for date range. my query is.

 select tfgld018.ttyp,tfgld018.docn,tfgld018.dcdt,tfgld018.stat
from tfgld018
where tfgld018.stat = 3 and
tfgld018.dcdt  >= :tfgld018.dcdt.t and
tfgld018.dcdt  <= :tfgld018.dcdt.f 
 system throuwing error.  ttadv3280m000: ttadv31853 : Execute Query: Range should be date(YYYY,MM,DD) Please help .. 
Reply With Quote
  #2  
Old 31st October 2020, 12:26
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,884
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, 12c, 19c - OS: HP-UX, W2K3, SLES, RHLE
From - To

Hello,

Try:
Code:
............
where tfgld018.stat = 3 and
tfgld018.dcdt  >= :tfgld018.dcdt.f and
tfgld018.dcdt  <= :tfgld018.dcdt.t
Regards
__________________
//Bernd
Reply With Quote
  #3  
Old 2nd November 2020, 12:02
ee05220 ee05220 is offline
Junior Member
 
Join Date: Aug 2017
Posts: 8
ee05220 is on a distinguished road
Baan: infor ln - DB: sqlserver - OS: w10
:tfgld018.dcdt.t and :tfgld018.dcdt.t are the form fields?

Could you post the domains you use for them?

It seems you using different domains because the error....
Reply With Quote
  #4  
Old 2nd November 2020, 12:03
DDPatel's Avatar
DDPatel DDPatel is offline
Member
 
Join Date: Dec 2018
Location: India,Gujarat,Umbergaon 396150
Posts: 59
DDPatel is on a distinguished road
Baan: LN10.3 - DB: SQL SERVER 2008 R2 - OS: WINDOW SERVER 2008 R2
Try this code

HTML Code:
select tfgld018.ttyp, tfgld018.docn, tfgld018.dcdt, tfgld018.stat
from tfgld018
where tfgld018.stat = 3 
and tfgld018.dcdt  >= tfgld018.dcdt.f and
and tfgld018.dcdt  <= tfgld018.dcdt.t

This would be working...
Reply With Quote
  #5  
Old 7th November 2020, 11:54
vivet2 vivet2 is offline
Junior Member
 
Join Date: Oct 2020
Posts: 7
vivet2 is on a distinguished road
Baan: 10.3 - DB: INFOR LN - OS: WINDOWS 10
Quote:
Originally Posted by DDPatel View Post
Try this code

HTML Code:
select tfgld018.ttyp, tfgld018.docn, tfgld018.dcdt, tfgld018.stat
from tfgld018
where tfgld018.stat = 3 
and tfgld018.dcdt  >= tfgld018.dcdt.f and
and tfgld018.dcdt  <= tfgld018.dcdt.t

This would be working...
Its working DDPatel
Thanks alot.
Reply With Quote
Sponsored Links
  #6  
Old 7th November 2020, 12:08
vivet2 vivet2 is offline
Junior Member
 
Join Date: Oct 2020
Posts: 7
vivet2 is on a distinguished road
Baan: 10.3 - DB: INFOR LN - OS: WINDOWS 10
Hy DDPatel

can you help me for one more query. Actually I want to print sum of all documents in my query report.my query is

Code:
select tfacr200.itbp,tccom100.nama,tfacr200.dim1,tfacr200.ttyp,tfacr200.ninv,tfacr200.line,tfacr200.docd,tfacr200.dued,tfacr200.ccur,tfacr200.amti,tfacr200.amth,tfacr200.balc,tfacr200.balh
from tfacr200,tccom100
where 
tfacr200.docd  >= tfacr200.docd.f and
tfacr200.docd  <= tfacr200.docd.t and
tfacr200.itbp >= tfacr200.itbp.f and 
tfacr200.itbp <= tfacr200.itbp.t and 
tfacr200.balc <> 0 and
tfacr200.itbp refers to tccom100

i am unable to do this please add logic for sum

Last edited by patvdv : 8th November 2020 at 01:52. Reason: Added CODE tags
Reply With Quote
  #7  
Old 7th November 2020, 12:10
vivet2 vivet2 is offline
Junior Member
 
Join Date: Oct 2020
Posts: 7
vivet2 is on a distinguished road
Baan: 10.3 - DB: INFOR LN - OS: WINDOWS 10
thanks bdittmar
Reply With Quote
  #8  
Old 9th November 2020, 01:52
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,528
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Is this an easy sql? Or a query in an easy sql report? If an easy sql then go to the report edit it and add sums that way. Depending how you group the data on where the sum would be or in which layout it might occur.

If this is a sum in an easy sql report script that is a whole new ball game, because it looks like you are printing more than just a sum.
__________________
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
  #9  
Old 9th November 2020, 07:09
DDPatel's Avatar
DDPatel DDPatel is offline
Member
 
Join Date: Dec 2018
Location: India,Gujarat,Umbergaon 396150
Posts: 59
DDPatel is on a distinguished road
Baan: LN10.3 - DB: SQL SERVER 2008 R2 - OS: WINDOW SERVER 2008 R2
Quote:
Originally Posted by vivet2 View Post
Hy DDPatel

can you help me for one more query. Actually I want to print sum of all documents in my query report.my query is

Code:
select tfacr200.itbp,tccom100.nama,tfacr200.dim1,tfacr200.ttyp,tfacr200.ninv,tfacr200.line,tfacr200.docd,tfacr200.dued,tfacr200.ccur,tfacr200.amti,tfacr200.amth,tfacr200.balc,tfacr200.balh
from tfacr200,tccom100
where 
tfacr200.docd  >= tfacr200.docd.f and
tfacr200.docd  <= tfacr200.docd.t and
tfacr200.itbp >= tfacr200.itbp.f and 
tfacr200.itbp <= tfacr200.itbp.t and 
tfacr200.balc <> 0 and
tfacr200.itbp refers to tccom100

i am unable to do this please add logic for sum

I think one small example will be better to understand this..

Example. We have purchase Order line table, we want to print purchase order total amount (SUM) base on purchase order no. (We must be use grope by statement because one purchase order have multiple Purchase Order line)

HTML Code:
select tdpur401.orno, sum(tdpur401.oamt):float0 
from tdpur401
| where (any condition)
group by   
	tdpur401.orno	
Order by   
	tdpur401.orno	
|Sample O/P
PUR000001 | 100000
PUR000002 | 300000
PUR000003 | 500000

Hope this will clear your doubt...

Regards,
DDPatel
Reply With Quote
  #10  
Old 10th November 2020, 13:46
vivet2 vivet2 is offline
Junior Member
 
Join Date: Oct 2020
Posts: 7
vivet2 is on a distinguished road
Baan: 10.3 - DB: INFOR LN - OS: WINDOWS 10
Hy DDPatel,

i understatnd what are you explaining. but i want sum with all the line values like:
PUR000001
1/ item 1 10
2/ item 2 20
SUM 30
Reply With Quote
  #11  
Old 10th November 2020, 13:47
vivet2 vivet2 is offline
Junior Member
 
Join Date: Oct 2020
Posts: 7
vivet2 is on a distinguished road
Baan: 10.3 - DB: INFOR LN - OS: WINDOWS 10
hi Mark,

"If this is a sum in an easy sql report script that is a whole new ball game, because it looks like you are printing more than just a sum."

You are right
Reply With Quote
  #12  
Old 10th November 2020, 14:42
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,528
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I am not positive of this, but if this code:
Code:
select tfacr200.itbp,tccom100.nama,tfacr200.dim1,tfacr200.ttyp,tfacr200.ninv,tfacr200.line,tfacr200.docd,tfacr200.dued,tfacr200.ccur,tfacr200.amti,tfacr200.amth,tfacr200.balc,tfacr200.balh
from tfacr200,tccom100
where 
tfacr200.docd  >= tfacr200.docd.f and
tfacr200.docd  <= tfacr200.docd.t and
tfacr200.itbp >= tfacr200.itbp.f and 
tfacr200.itbp <= tfacr200.itbp.t and 
tfacr200.balc <> 0 and
tfacr200.itbp refers to tccom100
is in the report script then I do not think tfacr200.docd.f are variables that make it to the report itself. If you are doing it in a report script I would expext this to run for each item in that range
Code:
select tfacr200.itbp,tccom100.nama,tfacr200.dim1,tfacr200.ttyp,tfacr200.ninv,tfacr200.line,tfacr200.docd,tfacr200.dued,tfacr200.ccur,tfacr200.amti,tfacr200.amth,tfacr200.balc,tfacr200.balh
from tfacr200,tccom100
where 
tfacr200.docd  = :tfacr200.docd and  |Get current docd and itbp being printed.
tfacr200.itbp = :tfacr200.itbp and  
tfacr200.balc <> 0 and
tfacr200.itbp refers to tccom100

Again if the about code is in the report script then if you want more information than just a sum to print then you need to look at layout.again(). With layout again you will run that query in the same layout repeatedly until you run out of records. BUT you have to track which record you are on and make sure you get next record in line.

My recommendation - is if you can add these tables to the main easy sql. As much as you are tying to do in this easy sql an actual session might be better.
__________________
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
Easy SQL - to Print Bar Code - LNUI leonchin Tools Development 8 5th September 2017 13:02
Varianten mit Easy SQL auslesen aeschen Blogs and Web links 0 22nd October 2010 17:09
Easy SQL date() Granholm Tools Development 6 23rd June 2009 23:29
USE "Query By Easy SQL..." ,But Run ERROR zhangguowu0506 Tools Development 1 7th October 2008 03:40
Strange error while importing tables: Can not create index 9999 eppesuiG Tools Administration & Installation 8 17th March 2008 05:35


All times are GMT +2. The time now is 19:46.


©2001-2020 - Baanboard.com - Baanforums.com