HI ,

Can anybody help me in getting the code given below rectified as it is not working. Actually i am working on areport session in which i have to generate a report on total store credit done in the year of the fG items in monthly buckets.

report will look like..

Item Jan Feb Mar April May ............ dec
A 25 75 30 15 25 70
B
C
D
E

I have written a script for this by using tdinv700, tiitm001 & tfgld005 tables but there is some problem somewhere it is not working fine as it is repeating same values for all items.

code is :


|******************************************************************************
|* tdinv0700sm01 0 VRC B40l c4 hon
|* Store Credit Report
|* applic
|* 28-05-03 [16:24]
|******************************************************************************
|* Script Type: 4
|******************************************************************************

|****************************** DECLARATION SECTION ***************************
declaration:
table ttdinv700
table ttiitm001
table ttfgld005
long monthcounter
| long tq1,tq2,tq3,tq4,tq5,tq6,tq7,tq8,tq9,tq10,tq11,tq12
| string t(12)
| extern domain tcqnty monthcounter
extern domain tcqnty tq1
extern domain tcqnty tq2
extern domain tcqnty tq3
extern domain tcqnty tq4
extern domain tcqnty tq5
extern domain tcqnty tq6
extern domain tcqnty tq7
extern domain tcqnty tq8
extern domain tcqnty tq9
extern domain tcqnty tq10
extern domain tcqnty tq11
extern domain tcqnty tq12
extern domain tcqnty total
extern domain tcdate fromdate
extern domain tcdate enddate
extern domain tcyrno year
|****************************** PROGRAM SECTION ***************************
|****************************** ZOOM FROM SECTION ***************************
|****************************** FORM SECTION ***************************

|****************************** CHOICE SECTION ***************************
choice.cont.process:
on.choice:
t=""
monthcounter=1
tq1=0
tq2=0
tq3=0
tq4=0
tq5=0
tq6=0
tq7=0
tq8=0
tq9=0
tq10=0
tq11=0
tq12=0
total=0
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif

|****************************** FIELD SECTION ***************************
|****************************** MAIN TABLE SECTION ***************************
|****************************** FUNCTION SECTION ***************************i
functions:

function read.main.table()
{
select tiitm001.* from tiitm001
where tiitm001.cwar = "FG1"
selectdo
compute.month()
rprt_send()
endselect
total = tq1 + tq2 + tq3 + tq4 + tq5 + tq6 + tq7 + tq8+tq9+tq10+tq11+tq12
}

function compute.month()
{
select tfgld005.* from tfgld005
where tfgld005.year = :year
selectdo
fromdate = tfgld005.stdt
select tfgld005.* from tfgld005
where tfgld005.year = :year and
tfgld005.stdt > :fromdate
selectdo
enddate = tfgld005.stdt
break
endselect
calculate.data()
calculate.quantity()
endselect

}

function calculate.data()
{
select tdinv700.* from tdinv700
where tdinv700.trdt >= :fromdate and
tdinv700.trdt < :enddate and
tdinv700.item = :tiitm001.item and
tdinv700.cwar = "FG1" and
tdinv700.kost = tckost.prd.receipt and
( tdinv700.koor = tckoor.wrh.order or
tdinv700.koor = tckoor.act.sfc )
selectdo
total = total + tdinv700.quan
endselect
}


function calculate.quantity()
{
on case monthcounter
case 1: tq1 = total
break
case 2: tq2 = total
break
case 3: tq3 = total
break
case 4: tq4 = total
break
case 5: tq5 = total
break
case 6: tq6 = tq6 + tdinv700.quan
break
case 7: tq7 = tq7 + tdinv700.quan
break
case 8: tq8 = tq8 + tdinv700.quan
break
case 9: tq9 = tq9 + tdinv700.quan
break
case 10: tq10 = tq10 + tdinv700.quan
break
case 11: tq11 = tq11 + tdinv700.quan
break
case 12: tq12 = tq12 + tdinv700.quan
endcase
monthcounter= monthcounter + 1
total =0
}


it is very urgent ..i a working on it from last three days and result is still 0. it can harm by performance in management eyes.

Hope a fast response.
Sandy

It seems that you are not iterating the months. My approach would be:


select tiitm001.item
where tiitm001.cwar="FG1"
selectdo
for i = 1 to 12
compute.month()
endfor
total = ...
rprt_send()
endselect


Also, in compute.month() you do not include the month in the query, thus always getting the first period's start date. Probably best to make the month number a parameter to the function.

Lastly, for performance, do not use all those select * statements, but only select the fields you need!

Regards,
Nico

Modified function compute.month


function compute.month()
{
select tfgld005.* from tfgld005
where tfgld005.year = :year
order by tfgld005._index1
selectdo
fromdate = tfgld005.stdt
select tfgld005.*
from tfgld005
where tfgld005.year = :year and
tfgld005.stdt > :fromdate
order by tfgld005.stdt
as set with 1 rows
selectdo
enddate = tfgld005.stdt
selectempty
enddate = date.to.num(year,31,12)
endselect
monthcounter=tfgld005.prod
calculate.data()
calculate.quantity()
endselect
}

No! Will still return the first month only! You need:


select tfgld005.stdt
where tfgld005.year=:year
and tfgld005.prod=:monthcounter
...


Just something interesting: the from <table> is not needed when the query explicitly selects fields specified with <table>.<field>! Only necessary when using aliases:


select a.stdt
from tfgld005 a
where a.year=...


Regards,
Nico

I don't think so.

For every year in the table tfgld005 exists 12 records (if you have 12 periods) for every type of period. In my function select will return 36 records. This is wrong and additional where clause needed for fiscal type period only:




function compute.month()
{
select tfgld005.*
from tfgld005
where tfgld005._index1={tfgld.ptyp.financial,:year}
order by tfgld005._index1
selectdo
fromdate = tfgld005.stdt
select tfgld005.stdt
from tfgld005
where tfgld005.year = :year and
tfgld005.stdt > :fromdate
order by tfgld005.stdt
as set with 1 rows
selectdo
enddate = tfgld005.stdt-1
selectempty
enddate = date.to.num(year,31,12)
endselect
monthcounter=tfgld005.prod
calculate.data()
calculate.quantity()
endselect
}

Ok, sorry! My mistake. :p I overlooked the fact that you call the calculate functions from the selectdo. But, the inner query will destroy the outer's result set (same table!), and you might end up with less than twelve iterations? Maybe necessary to add a "as prepared set" to the outer query.

Regards,
Nico