Dear All,

What I basically need is to retrieve data from the table tlgld200 (customized table) whether respective data is available or not in the table tfgld106 (outer join). My query is given below but it works only with the available data in tfgld106. How do I achieve this? Pl advice

select tlgld200.*, tfgld106.*
from tlgld200, tfgld106
where tlgld200._index1 inrange {:leac.f} and {:leac.t}
and tlgld200.leac = tfgld106.leac
and tfgld106.cuno between :cuno.f and :cuno.t
and tfgld106.dcdt between :dcdt.f and :dcdt.t
and not exists (
select tlgld200.*
from tlgld200
where tlgld200._index1 = inrange {:leac.f} and {:leac.t}
)
order by tlgld200._index1
selectdo
rprt_send()
endselect



Regards
Srilal

This simplest thing to do is to separate the queries. So first find the data in tfgld200 as the outer query. Then an inner query tfgld106 - you can use selectempty to zero out or init tfgld106 fields as needed.

Hello Mark,

Thanks for the quick response. Appreciate if you could show how to adjust the script

Regards
Srilal

It would look something like this.

select tlgld200.*
from tlgld200
where tlgld200._index1 inrange {:leac.f} and {:leac.t}
and not exists (
select tlgld200.*
from tlgld200
where tlgld200._index1 = inrange {:leac.f} and {:leac.t}
)
order by tlgld200._index1
selectdo
get.tfgld106.data()
rprt_send()
endselect


function get.tfgld106.data()
select tfgld106.*
from tfgld106
where tfgld106.leac = :tfgld200.leac
and tfgld106.cuno between :cuno.f and :cuno.t
and tfgld106.dcdt between :dcdt.f and :dcdt.t
selectdo
endselect

Hi

Thanks.

The problem here is that the tlgld200 is a sort of master data table, means one record per each ledger account (leac) but gld106 has N number of records per one Ledger Acc (leac).

I have already tired but it doesn’t provide the requited result even If use SELECDO and call gld106.

select tlgld200.*
from tlgld200
where tlgld200._index1 inrange {:leac.f} and {:leac.t}
order by tlgld200._index1
selectdo
select tfgld106.*
from tfgld106
where tfgld106.leac = :tlgld200.leac
and tfgld106.cuno between :cuno.f and :cuno.t
and tfgld106.dcdt between :dcdt.f and :dcdt.t
as set with 1 rows
endselect
rprt_send()
endselect

Regards
Srilal

If there are multiple gld106 records to be reported then use something like this:



select tlgld200.*
from tlgld200
where tlgld200._index1 inrange {:leac.f} and {:leac.t}
order by tlgld200._index1
selectdo
select tfgld106.*
from tfgld106
where tfgld106.leac = :tlgld200.leac
and tfgld106.cuno between :cuno.f and :cuno.t
and tfgld106.dcdt between :dcdt.f and :dcdt.t
selectdo
rprt_send()
selectempty
rprt_send()
endselect
endselect

Hello Mark,

Thanks. This is very good. The is only one problem.

Even if there are no records found in gld106 table for the respective LEAC in tlgld200 table, still the report shows the details of the very last record of the gld106. Pl refer the attachment

Regards
Srilal

Then what you do is clear out any tfgld106 fields that you use in the report:

selectempty
tfgld106.otyp = ""
tfgld106.leac = ""
endselect

Just clear out any fields you are using in the report.

Thanks a lot Mark. Its OK now.

Regards
Srilal