This is odd to me.

I am editing the Finalized Trans (by Ledger Account) with Supplier report. TFGLD140611001.

Simple script addition to already customized code ...

The first select statemetn WORKS FINE ....

select tccom020.*
from tccom020
where tccom020.suno=:tfgld106.suno
selectdo
supname=tccom020.nama
selectempty
supname=""
endselect

The SECOND statement below follows the above statement and will not execute at all ! When I step through the code, it gets to the "Select tfgld" line, then jumps completely past the end select stmt like as if nothing was even there.


select tfgld410.*
from tfgld410
where tfgld410.leac=:tfgld106.leac
and tfgld410.ttyp=:tfgld106.otyp
and tfgld410.docn=:tfgld106.odoc
and tfgld410.amnt=:tfgld106.amnt
and tfgld410.suno=:tfgld106.suno
selectdo
ponum=tfgld410.orno
selectempty
endselect

We originally had a declaration in for the table that matched the working syntax for tccom020 above, but no dice ...

I can't figure out why it does this ! it is a simple query but it appears to not execute the select stmt.



Code as it sits in the report :


detail.1:
before.layout:

| ** EHC Code Begins **
select tccom020.*
from tccom020
where tccom020.suno=:tfgld106.suno
selectdo
supname=tccom020.nama
selectempty
supname=""
endselect

select tfgld410.*
from tfgld410
where tfgld410.leac=:tfgld106.leac
and tfgld410.ttyp=:tfgld106.otyp
and tfgld410.docn=:tfgld106.odoc
and tfgld410.amnt=:tfgld106.amnt
and tfgld410.suno=:tfgld106.suno
selectdo
ponum=tfgld410.orno
selectempty
endselect

| ** EHC Code Ends **


ANY SUGGESTIONS ????

If it jumps passed the 'endselect' it mean no record was found; since you didn't program any code in the 'selectempty' section, the program jumps on.

As for WHY the required record was not found: maybe the tfgld106-fields are not all filled, for instance by not all being selected in its query? I suggest you run the report in debug mode, and check the values of tfgld106 before the query on tfgld410, and see if they contain what you'd expect.


Regards,
Eli Nager

OK, yes, the code did drop through because I did not put in a condition at selectempty, thanks for that tip....

It does stop there now that I told it to just make the value 0 if empty ...

The values for TFGLD106 are showing up in debug so it knows what it needs to match in gld410

The values for the TFGLD410 are all blank though in debug ....

If I run that exact query in SQL Developer I do get a result ...

Is it possible the script is not recognizing the gld410 table or something ?

Under Declarations, I had added

table ttfgld410.

I removed some of my conditions and it will work on some. I think I just need to spend a little more time on the criteria to make sure they are picked up properly. Thanks for your insight ....

JMM.

I would probably pull out the "and tfgld410.amnt=:tfgld106.amnt" and check the lines using 'double.cmp' with the desired precision ".0001" within selectdo --if (double.cmp(tfgld410.amnt, tfgld106.amnt, .0001) = 0) then. You could be running into a rounding-type issue when comparing doubles in the select statement. I normally don't use doubles in 'where' clauses since the internal representation may vary slightly. If they didn't, there wouldn't be a need for 'double.cmp'.

Thanks for all the suggestions. The problem ended up being that the leac values were not equal in the 2 tables, I didn't even notice it. I removed the condition for leac from the query and it picked up the correct record... Finance will test fully this morning ....

I'll know to put in a value for selectempty too, and will look at the double.cmp entry.


JMM.