I am using Session Extension.
I created a calculated field to query a field from a table using Query Extension. It is a very simple query.

select tfcmg101.plan
from tfcmg101
where tfcmg101.btno = :tfcmg109.btno
as set with 1 rows

The select, from, and where are fine. It works just fine.
The only part in question is how can I provide the part "as set with 1 rows" to the Query Extension?
I tried putting it in the "where" part but it gave me errors.

Why don't you use Calculated field Function type to complete this reqirement? You can use as set with 1 rows there

It needs to be able to filter the column, which Calculated Field does not provide the required functionality.

I mean you can use Calculated Field of Expression Type Function and in that function you can query the selected record



function extern void ext.batch.plan.calculate()
{

domain tfgld.date ext.batch.plan

ext.batch.plan = 0

select tfcmg101.plan:ext.batch.plan
from tfcmg101
where tfcmg101.btno = :tfcmg109.btno
as set with 1 rows
selectdo

endselect
}

I mean this filter. And that Calculated Field of Expression Type Function does not have the ability to make that.

You can use something like


query.extend.select("select tfcmg101.plan")
query.extend.from("tfcmg101")
query.extend.where("tfcmg101.btno = :tfcmg109.btno and tfcmg101.btno
in (select min(t101.btno) from tfcmg101 t101 where t101.btno = :tfcmg109.btno)")


In this case it will select the minimum most Batch Number.

You can use something like


query.extend.select("select tfcmg101.plan")
query.extend.from("tfcmg101")
query.extend.where("tfcmg101.btno = :tfcmg109.btno and tfcmg101.btno
in (select min(t101.btno) from tfcmg101 t101 where t101.btno = :tfcmg109.btno)")


In this case it will select the minimum most Batch Number.

It doesn't work. It's behavior is weird too.
At first, every record has the same value, which is the value from the first record displayed on the screen. After some scrolling, records are hidden until nothing shows on the screen -- only an empty session.

Whats the Main table for your session? Its a Maintain Session right??

Whats the Main table for your session? Its a Maintain Session right??

Its Main Table is tfcmg109 and it is a Maintain Session.

Try this


query.extend.where("exists (select tfcmg101.btno " &
"from tfcmg101 " &
"where tfcmg101._index1 = tfcmg109.btno)")

Try this


query.extend.where("exists (select tfcmg101.btno " &
"from tfcmg101 " &
"where tfcmg101._index1 = tfcmg109.btno)")



The field is empty in every record now.

The field is empty in every record now.

I suppose its customized session.

What you can do is add the field on Form,tfcmg101.plan

and add this part in the field section


field.tfcmg101.plan:
before.display:
select tfcmg101.plan
from tfcmg101
where tfcmg101._index1 = {:tfcmg109.btno}
as set with 1 rows
selectdo
endselect



This works for me

I suppose its customized session.

What you can do is add the field on Form,tfcmg101.plan

and add this part in the field section


field.tfcmg101.plan:
before.display:
select tfcmg101.plan
from tfcmg101
where tfcmg101._index1 = {:tfcmg109.btno}
as set with 1 rows
selectdo
endselect



This works for me

It is a standard session, not customized.

So add a calculated field in the Standard Session extension?

That's exactly what I did and described in the question.

I think you can use a calculated field with expression type 'nested select':

select min(tfcmg101.plan) from tfcmg101 where tfcmg101.btno = tfcmg109.btno

I think you can use a calculated field with expression type 'nested select':

select min(tfcmg101.plan) from tfcmg101 where tfcmg101.btno = tfcmg109.btno


This almost makes it work.
The goal is to get the date from the first Payment Advice Line to display here but your solution picks the minimum date when there are more than 1 lines.

I tried extending what you suggested by editing out the min() and adding as set with 1 rows. It gave me empty value in the field.

I doubt whether there is a clear definition of 'first Payment Advice Line' in this table, because of the fields in the index...
Alternative is to have a CDF in tfcmg109 with the date field and fill it at the moment the first tfcmg101 record is created for the batch.

I doubt whether there is a clear definition of 'first Payment Advice Line' in this table, because of the fields in the index...
Alternative is to have a CDF in tfcmg109 with the date field and fill it at the moment the first tfcmg101 record is created for the batch.

Yeah. The alternative way works.
But I have to add a custom form command to fill-in the date for existing records, and execute the command once.
Thanks everyone.
Case closed.

You can do it through a Combination of Table extension and Session Extension.

In Table Extension, before.open.object.set you can write the query extension and in session, you can add calculated field


function extern long before.open.object.set()
{
| hook code
query.extend.where("exists(select t101.btno from tfcmg101 t101 " &
"where t101._index1 = {tfcmg109.btno})",EXTEND_APPEND)


return (0)
}



But the only drawback would be that the Table extension would be applicable for all the sessions.

I am also facing the same issue. anyone please guide me.

Instead of creating cdf fields, is there any option to filter the records using extensions.

Instead of creating cdf fields, is there any option to filter the records using extensions.

You can you Extensions and add a Calculated Field using SQL expression types.

Don't use function type.

Thanks.
But i need to compare the two tables and fetch the record. I can able to achieve this using function only.