Easy SQL

[vivet2]

A new blog entry has been added:

Easy SQL

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

[bdittmar]

Hello,

Try:

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

Regards

[ee05220]

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

[DDPatel]

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

[vivet2]

Originally Posted by DDPatel

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

[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

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

[vivet2]

thanks bdittmar

[mark_h]

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.

[DDPatel]

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

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)

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

[vivet2]

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

[vivet2]

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

[mark_h]

I am not positive of this, but if this 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

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.