Baanboard.com

Go Back   Baanboard.com > Blogs > vivet2's blog

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

 
Easy SQL
By vivet2 at 31 Oct 2020 - 11:08

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 .. 
0
No votes yet

by bdittmar on October 31, 2020 - 11:26am
bdittmar's picture
Hello,

Try:
Code:
............
where tfgld018.stat = 3 and
tfgld018.dcdt  >= :tfgld018.dcdt.f and
tfgld018.dcdt  <= :tfgld018.dcdt.t
Regards

by ee05220 on November 2, 2020 - 11:02am
: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....

by DDPatel on November 2, 2020 - 11:03am
DDPatel's picture
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...

by vivet2 on November 7, 2020 - 10:54am
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.

by vivet2 on November 7, 2020 - 11:08am
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

by vivet2 on November 7, 2020 - 11:10am
thanks bdittmar

by mark_h on November 9, 2020 - 12:52am
mark_h's picture
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.

by DDPatel on November 9, 2020 - 6:09am
DDPatel's picture
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

by vivet2 on November 10, 2020 - 12:46pm
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

by vivet2 on November 10, 2020 - 12:47pm
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


All times are GMT +2. The time now is 06:57.


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