where clause on data block

tayshaun

I use oracle_linux, and I have oracle forms 12.2.1.4 installed

I have a form and I want to filter the records of that form by the where clause of the data block, I have a subquery related to another table that is the one with the status that I do not want to show in that form

my subquery is : that I have in the block in the where clause

not exists(select * from movh h,movd d

where h.cod_mov=d.cod_mov

and h.ncf=d.ncf and not exists(select p.period from period p

where h.company=p.company

and d.company=p.company

and h.period=p.period

and h.period=p.period

and d.period=p.period

and p.period_st='B'))

by the database when I execute the query it works fine, but when I put the query in the data block of the form it doesn't show me any records, and when I press the Shift + F1 key, it shows FRM-42100:No errors encountered recently

Hub Tijhuis

Use the count_query key to check if the number of records found is really 0. If it's not 0 check your post_query trigger: when it fails for a record then that record is not shown,

If you omit the where do you get records?

In your where clause I don't see a reference to the table you want to filter , is that correct? (That does not explain your problem, but raises the question if you submitted only the where to the database or the complete query).

You are sure you connect to the same database and user when executing the form and the query?

tayshaun

the table that I want to filter is the one in the subquery, by the period

Hub Tijhuis

Can you add the create and insert statements for the tables so we can try to reproduce the problem.

What is the table your forms block queries without the where?

What is the table you use in the select .. from when you execute the query in the database.

And there are 3 more unanswered questions in my first comment.

tayshaun

I am connecting to the same database, my form has two tables, a header and a details, the header=movh,details=movd, and the table that I am making the relationship is period

tayshaun

when I try to insert a record, and pass the first field it shows me this error:

error.jpg

eliasfm

It could be some field in the block of type list-item or check-box where the values of the field in the table do not correspond to the possible values.

tayshaun

I resolved the problem, it was a when validate item, by inserting a record, but still does not present the information in the block

tayshaun

thanks for all the information provided, I was able to solve my problem, with this sentence:

not exists(select 1 from period p 

where p.company=movh.company 

and p.period=movh.period

and p.period_st='C')