field validation for research PERCENTAGE

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
Post Reply
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

Guys, good afternoon.

I have a problem to validate the fields for research and would like to know if anyone has passed through this and how it solved it. I always valued the fields for research or even to register with the following syntax: ": block.campo is null", but I have a situation now, that this validation is failing. When I type for example "123456" in the field and research, the above validation works, however, if I put the percentage%, example

Select all

"12345%"
validation says the field is vary.

I even have the solution, but I'm sure not the solution I thought, it's not part of good practices. I thought about doing so "Length (: block.Campo) = 0", but I wanted to know if there is any other solution or if you have a better solution !!!

Att.

Tiago Pimenta
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good morning Tiago,

I do not know how you are mounting the query, but if you use the like should solve.

Are you using this in pre-query or the WHERE clause of the block?
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

Spernega, good morning.

In fact, my problem today is as follows, there is already a "pre-query" trigger in a base-table block, which verified if the fields are filled or not, if they are not , it gives a message and does not perform the search. When I type for example on an item "123456", it does the search / search normally, however, if I put the percentage "%" in the item to do the research, example "12345%", he understands how the field was Empty and does not fulfill the search. But the customer needs / wants to carry out the search with the "%".

I do not know if you have forms in hands there, but to view my problem, just create a forms with a simple base-table block (code, description) and put on trigger "Pre-Query" block the validation below and try to carry out the search in code with the "%".



I am not mounting the query or sets the default_where of the block, the search is using the "F7" and "F8".


spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

OK,

tries to do so:

Select all

if :bloco.campo is null then 
   message('É nulo'); 
elsif INSTRB(:bloco.campo, '%') > 0 then 
   message('usa LIKE'); 
else 
   raise form_trigger_failure; 
end if;
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

It does not roll because on the line

Select all

elsif INSTRB(:bloco.campo, '%') > 0 then
he is "finding" that: block.Campo is null, that is, it is as if the line was like this

Select all

elsif INSTRB(null, '%') > 0 then

MUIT'LOKO This Bug of Forms


spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Strange even ...
I did some tests and it works.

Do you want to post your trigger to take a look?
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

The trigger I just put the IF of the instr .. then it was like this

Select all

if instrb(:bloco.campo, '%') > 0 then 
   message('Passou'); 
end if;
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

Yesterday before sending the email, I tested the Length and it worked, but today it is not working, that is, any / bulto-in function that I use with this Number field containing the "%", it does not work.

I can not let the client do a search without at least 1 field as a criterion (filled), that is, if he reaches the block, press "F7" and then "F8", I can not allow, it has to fill at least 1 field and if it uses the "%".

I am accepting suggestions * rs *


spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Tiago,

I do as follows:

Create a block of parameters, not base table.
and create the Base Table Block

The pre-query I mount based on the parameter block.

Is more or less as soon as you are doing?
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

spernega,

No .. I do a simple forms with a simple base-table block ... look at this image

[IMG [0]]
Make a similar forms, just need an item number ... then you run it and do a search ... in trigger validation, he should show the message only when we squeeze f7 and soon Then F8, to bring all * the data, correct ??? However, when you enter any number with the "%", it gives an error of 50016 if I am not mistaken and zero the item, understand?


User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

Did you take the edit of the forum ???

- Edit -

Image Link: http://tinypic.com/r/1z4admd/9 For me, it only appears after it opens the page and click "zoom" ).
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

So if the block is base table, the user types something and clicks F7, the forms will validate the registry.
He understands he was trying to insert something.
That's why the 50600 error.
Probably the field is Number and the user typed%.
DAI tries to validate and the error

can you use the usual process?
F7 key, the block enters search mode, the user types in the field that he wants the value with or without% and F8 key.
Dai does not even need pre-query.

or I did not quite understand ...
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

We are not in the same tune * rs *

Yes, the user will do the processing "usual", what I wanted to exemplify and I could not be 3 situations, let me see if I can explain otherwise.

First situation (this situation can not happen)

1. Opens the Forms / Screen
2. F7
3. F8

Second situation (This situation works and can happen)

1. Opens the Forms / Screen
2. F7
3. Enter the entire value, eg, 123456
4. F8

Third situation (this * needs * happen and is not happening)

1. Opens the Forms / Screen
2. F7
3. Enter the whole value, eg, 12345%
4. F8

I was able to explain now? *LOL*
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

He explained perfectly.

But in this situation does not have to create pre-query, it is natural for the forms to perform the search and respect what was typed ...

You tried Run the forms without creating pre-query?
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

spernega wrote: explained perfectly.

But in this situation does not have to create pre-query, it is natural for the forms to perform the search and respect what was typed ...

You tried Run the forms without creating pre-query?
I need to create because I can not let the first situation happen, because it brings all the records of the table and this delays ... understood why the pre- Query ???
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good morning,

If you try to limit records in Maximum Records fetched from block properties and set the Query All Recods as not?

From the rest I do not see why% is not working.
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

So ... but I do not know if the validation it has is because of the amount of records (which was what happened to me), or if it is some business rule not to allow consultation without any filled field.

As for the "problem" of the percentage "%", it gives a 50016 error when you try to search in numeric field, * probably * it gives the mistake and zera the field, so with which The condition of the field is null. That's what I think, but I'm not sure.


spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Tiago,

It's time to appeal to ignorance, at least to solve the error message ...
In Pre-Query places

Select all

:System.Message_Level := 24
]

Then return pair

Select all

 :System.Message_Level := 0
I will think of testing if the field is null.
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

Dude, I laughed at your "time has come to appeal to ignorance" ... What I thought was in treating the error in "On-Error" with the following "logic" ... if it gave invalid error, It's because the field is not null ... tended ??
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

kkkkkkkkkkkkkkkkkkkkkkkkkk .... better yes

But I could not unravel the% in pre-query and I did not find anything on the net.
At no way the forms understands how not to null when it has%.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

James,

If you solve the problem of% in Pre-Query, account for us.
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

Spernega, good afternoon.

Dude, what was done was deactivated the trigger that made the validation if the field was null or not, that is, I denied a saint to wear another.

But I was not satisfied with that, but for now that's what we got :?
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Beauty, but think of creating a block of search parameters, you will suffer a little less.
But the important thing is to work.

Good luck
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

But even with the search block, the situation would be the same would not be? Would have an item in this Number block that the user would put the "%" and would give the same error :(
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good morning Tiago,

works Yes ...

Next, I set up a base table table on a table called client_proprietario and set up a non-base table block called control.

In the Control Block I created a field called value and set up a pre-query in the Proprietary Client block:

Select all

Declare 
	v_clau varchar2(2000); 
begin 
	if :controle.valor is null then 
		 message('Query full não permitida!'); 
		 pause; 
     -- 
     raise form_trigger_failure; 
     -- 
	else 
		if :controle.valor like '%' then 
  		 message('Query com %!'); 
	  	 pause; 
       set_block_property('cliente_proprietario', default_where, 'cliente_proprietario.cd_Clie_Fili like '||''''||:controle.valor||''''); 
		else 
  		 message('Query informada!'); 
	  	 pause; 
       set_block_property('cliente_proprietario', default_where, 'cliente_proprietario.cd_Clie_Fili = '||:controle.valor); 
		end if; 
	end if; 
end;
of this form it considers the% correctly.

See if you get this way.
User avatar
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Posts: 213
Joined: Wed, 29 Jun 2011 9:49 am
Location: Barretos / SP

Spernega,

First excuse for the delay, I made a simple test forms here with the non-base-table block and even then it did not roll ... Take a look there for you to see how I do it here , because I do not ride the default_where, understood?
https://drive.google.com/open?id=0B2SBc ... XU3UFpCS0U

spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Tiago,

I managed to download the file but it opens with error, it must be a lack of some PLL here, or something like that.
I am attaching the test I did and with the creation script of a table that I used with some inserts.

Renames the file to .zip and see if you can see the way I did.
Attachments
Temp_Programas.txt
renomear para .zip
(9.01 KiB) Downloaded 479 times
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 2 guests