Dear friends...

I have a script like this :

=============================================
select tdpur401.*
from tdpur401
where tdpur401.item = :item.pn
and tdpur401.odat between :date.f and :date.t
order by tdpur401.odat DESC
as set with 3 rows
selectdo

select tcibd001.dsca
from tcibd001
where tcibd001.item = :tdpur401.item
selectdo
endselect

rprt_send()

endselect
==============================================

from this script, i get 3 data ( because i use "as set with 3 rows" ).
this script works well.

but i have problem : how to get the 1st, 2nd and 3rd data and store it into a variable so i can manipulate it later for any operation.

Anybody can help me ?

Thank you...

Hi,

The simplest way would be like this:

domain tcitem item1, item2, item3

item1 = ""
item2 = ""
item3 = ""
select tdpur401.*
from tdpur401
where tdpur401.item = :item.pn
and tdpur401.odat between :date.f and :date.t
order by tdpur401.odat DESC
as set with 3 rows
selectdo
if isspace(item1) then
item1 = tdpur401.item
else
if isspace(item2) then
item2 = tdpur401.item
else
item3 = tdpur401.item
endif
endif

select tcibd001.dsca
from tcibd001
where tcibd001.item = :tdpur401.item
selectdo
endselect

rprt_send()
endselect

In general, I would advice using idex where possible, so for the second select use where tcibd001._index1 = {:tdpur401.item}. Also, since there is only one record in tcibd001 matching the query, you should use 'as set with 1 rows'.

Good luck!

En.

Dear En...

Thank you very much for your "miracle" script.
It works. My problem solved now.
Thank you.

But, if you don't mind, i want to ask more question.
I want to know about the logic of your script.
Can you explain how the script works, so it can get and store the query value into variable ?
How can a combination between SELECT - ENDSELECT and IF-THEN-ELSE-ENDIF read the value record by record ?

Thank you again...


Best Regards.

Hello,

In the first select you selected three records. For each one of these three record the program goes through the cycle in the selectdo section.

So: the first time, since the variables are being initialized before the select, item1, item2 and item3 all contain an empty string. so the first time the condition 'if isspace(item1) is met (isspace() function checks whether a string-field is empty or filled with spaces), thus item1 gets the value of the first selected record.

The second time, the condition if isspace(item1) is not met, since item1 has just been filled, thus the next part is 'else if isspace(item2)'; this condition is met, so item2 receives the value of the second record.

For the third record only the last condition 'else' is met, so item3 get the value of the third record.

Hopes this clears it up a little.

Good luck!

En.

Dear En...

Thank you again for your clear explanation.
I can understand now.

Thank you very much.


Best Regards.