Hello all,
Im trying to obtain the opening balances in a report,
Here is my code
select tfgld206.year, tfgld206.dim3, tfgld206.leac, sum(tfgld206.fobh):imp.erogado1, sum(tfgld206.nobh):imp.erogado2
from tfgld206
where tfgld206.leac between :tfgld804.lacm and :tfgld804.lact
and tfgld206.year = :year.c
and tfgld206.dim3 inrange :tfgld804.di3f and :tfgld804.di3t
group by tfgld206.year, tfgld206.dim3, tfgld206.leac
selectdo
sa = imp.erogado1 - imp.erogado2
endselect

imp.erogado = round((sa + imp.erogado )/tc.prom,0,2)

but it seems that I´m only obtaining the last dimension defined by tfgld804.di3t, that is wrong due to I want to obain from tfgld804.di3f and tfgld804.di3t range...
Any ideas?
Thanks in advance...

First from what I have read inrange and between are the same unless using combined fields. So make sure those tfgld804 fields are structured correct. Next validate some data for that range. Then I would take turn commenting out each of the lines to see which one is causing it to fail. As long as year.c is filled correct I do not see in being a problem, but I can see where if those inrange checks are not correct then you might have this problem.

Hello Mark,
The fields tfgld804.di3f and tfgd804.di3t are not combined fields. I have done some tests and when tfgld804.di3f = tfgld804.di3t, I mean, there is only one dimension, this problem not appear.
So I don´t know why only the tfgld804.di3t are used...
any othe idea?
Thanks.

What I was saying was - in this case inrange and between are the same. You use between when using a combined field. Since these are not combined fields then I would use inrange just to make it consistent.

Second here is how I debug a select statement - first I would comment out the or year piece and the in range on dim3. Did I get what I expected. If no - then the problem might be with the where on leac. If yes- then add in year. Again did I get what I expected? If no then problem might be with year. If yes - add in the dim3 inrange piece. Again -did I get what I expected? No - then there is the problem. If yes then the query was correct - could it be the report?

In this case the first thing you need to do is make sure the inrange variables are formatted correctly. I have seen field not formatted the same - so are dim and leac same lenght, same justification? These are basic things that I check - since I do not have a 804 table I do not have a clue how things are formatted.

WHERE are you printing - where is your rprt_send() command?

It has to be INSIDE the selectdo to get all the dimensions you require.

If it is AFTER the

imp.erogado = round((sa + imp.erogado )/tc.prom,0,2)

then, you WILL get only the last dimension.

Hello Mark and sha_bs,
I debuged the code and I realize that I have used the built in SUM in the select, so i eliminated that SUM and use the old-fashion algorithm to do that sum manually.

I never have realized that SUM function do that.
But at least my problem was solved.

Thank you for all your help.

You should be able to use the sum function - it would just need to be inside the select do as shah suggest. I might not be understanding what the end results are. Glad you solved it.