Dear All,

I want to make a new ERPLN SQL query which reads all sales data from tdsls400 and tdsls401. I want to sum the amount according to Sales Rep and Area and Invoice date and Item. All the amounts will group by Item.

functions:

function read.main.table()
{

select
tdsls401.item, | Item
sum(tdsls401.oamt):float01

from
tdsls401, | Sales Order Lines
tdsls400
where
tdsls400.crep INRANGE :crep.f and :crep.t and
tdsls400.creg INRANGE :creg.f and :creg.t and
tdsls401.invd INRANGE :invd.f and :invd.t and
tdsls401.item INRANGE :item.f and :item.t and
tdsls401.orno = tdsls400.orno and
tdsls401.invn <> 0

group by

tdsls401.item | Item
selectdo
rprt_send()
endselect

}


The problem is, I want to sum all the amount where item price = 0 and also where item price <> 0

How I can get the two amounts with the same SQL query.

Please advice

Not sure there is a way to do this. What I would probably do is break up the query and use nested queries. In this case the outside query would get the items - then the inside query would run the sums to get for item price not 0 and item price = 0. It would really depend on how you would want the data in the report.

Hi,

You can find how the data must be reported in attached file.

Regards,

Sorry, but the report does not help since I am not familiar with the sales or any related tables. What I recommend is breaking the query apart to simplify the problem so first get all the items. Second do the sum for the sales and then a sum for the returns. The queries would be identical except one would have item price >0 and one would have item price=0. Then do the rprt_send. This gives you each detail record for the report, assuming that the net sales is subtracting the two.

Thanks a lot for your reply.

But where I will put rprt_send, in which select statment. Can you please give me a example.

Thanks in advance

Below is a little report we used to use to compare some inventory balances between some parts. Basically - get item, get inventory data, send to report. You end up with a detail line for each item with a number for each calculated qty. Basically same thing you are asking.

| Init sums to zero
pgc001.inv = 0
pgc010.inv = 0
pgc040.inv = 0
inv001.inv = 0
ilc101.inv = 0
| Get item master total
select tiitm001.item, tiitm001.stoc, tiitm001.copr
from tiitm001
where tiitm001.item inrange :item.f and :item.t
selectdo
sum1 = 0.0
sum2 = 0.0
sum3 = 0.0
sum4 = 0.0
get.group.item()
if(chk.pgc = tcyesno.yes) then
get.pegged.inventory()
get.project.inventory()
sum1 = pgc010.inv - pgc040.inv
endif
get.warehouse.inventory()
if(chk.ilc = tcyesno.yes) then
get.location.inventory()
sum2 = tiitm001.stoc - ilc101.inv
endif
| Subtract EXC data from part - Eliminate tpes parts
get.exc.inventory()
tiitm001.stoc = tiitm001.stoc - exc101.inv
inv001.inv = inv001.inv - exc101.inv
ilc101.inv = ilc101.inv - exc101.inv

| Compare pegged data vs Inventory data
sum3 = inv001.inv - pgc010.inv
sum4 = ilc101.inv - pgc040.inv

| Eliminate parts where all are equal except item master
balance1 = sum1
+ sum2
+ sum3
+ sum4
if( double.cmp(balance1,0.0000,.00001) =0 )then
continue
endif

| Are the parts balanced
balance = (tiitm001.stoc - pgc001.inv)
+ (tiitm001.stoc - inv001.inv)
+ sum1
+ sum2
+ sum3
+ sum4
if(balance >0 or balance<0) then
rprt_send()
endif
pgc001.inv = 0
pgc010.inv = 0
pgc040.inv = 0
inv001.inv = 0
ilc101.inv = 0
endselect

}
function get.group.item()
{
pgc001.inv = 0
select sum(tipgc001.stoc):pgc001.inv
from tipgc001
where tipgc001.item = :tiitm001.item
selectdo
endselect
}
function get.pegged.inventory()
{
pgc010.inv = 0
select sum(tipgc010.stoc):pgc010.inv
from tipgc010
where tipgc010.item = :tiitm001.item
selectdo
endselect
}
function get.project.inventory()
{
domain tcqiv2 sum.loan
sum.loan = 0
pgc040.inv = 0
select sum(tipgc040.quan):pgc040.inv,sum(tipgc040.loan):sum.loan
from tipgc040
where tipgc040.item = :tiitm001.item
selectdo
endselect
pgc040.inv = pgc040.inv - sum.loan
}
function get.warehouse.inventory()
{
inv001.inv = 0
select sum(tdinv001.stoc):inv001.inv
from tdinv001
where tdinv001.item = :tiitm001.item
selectdo
endselect
}
function get.location.inventory()
{
ilc101.inv = 0
select sum(tdilc101.stks):ilc101.inv
from tdilc101
where tdilc101.item = :tiitm001.item
selectdo
endselect
}
function get.exc.inventory()
{
exc101.inv = 0
select sum(tdilc101.stks):exc101.inv
from tdilc101
where tdilc101.item = :tiitm001.item
and tdilc101.cwar = "EXC"
selectdo
endselect
}

Thanks a lot for your reply.

My main concern that I want all the sums to be calculated in one SQL query...

Please advice

Is there any particular reason why u want the same in 1 sql query. Baan sql queries dont support much calculations functions in sql query . U can do such things in SQL query in ms sql server.

I just want all the data to be calculated from the session then to be send to the report. How I can get the results?

Regards

There can be multiple ways u can do the calculation in baan program but outside SQL query and outside baan report . In-memory data transformationdll (http://www.baanboard.com/baanboard/showthread.php?t=28569) is one such efficient most method available in all baan version .

write it in raw SQL that is native to your database - it will run thousands of time quicker - I'll write it for you if you want (no fee) - just beware of the fact that Baan sometimes stores floats incorrectly - zero is not always zero, it might be 0.0000000000000001 or somesuch.

Hi Arthas,

I will grateful