Easy SQL - Baanboard

		Baanboard.com > Blogs > vivet2's blog
Easy SQL

User login
Username: * Password: Remember me Create new account Request new password

Frontpage Sponsor

Main
Frontpage Forum Jobs News Stories RSS Newsfeeds Categories ERP Geek Industry & Technology Opinion Security Sources Calendar Contact Polls Web Links

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

Baanboard at LinkedIn

Reference Content
BaanERP Tutorial Programmer's Manual

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 ..

Average:

No votes yet

by bdittmar on October 31, 2020 - 11:26am

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

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

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

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

Quote:

Originally Posted by vivet2

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

Read full thread
Login or register to post comments

All times are GMT +2. The time now is 09:43.