Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

validate a number entered in oracle forms

 select to_char('12.0',99.90) as WBC from dual

WBC
12.0
In the form, the column is char. I do not want to give a format mask and then convet rt it automatically, but when the user enters, 12 then I should give an error message to enter the data in the format 12.0 . How can I write the code in the when validate trigger?
block.field name is :qc_tasks_poch.result_result

0
anumoses
Asked:
anumoses
  • 8
  • 8
  • 6
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
From this and all your previous questions:  I'm curious why all of your columns appear to be varchar2.

I assume this is also in the 9.2 database?

you can add an if statement using the number entered and the results of to_char(number,99.90)

something like:

if number_entered != to_char(number_entered,99.90) then
    -- invalid format
end if;

0
 
anumosesAuthor Commented:
Some of the tasks are entered as char and some are number. So the forms has a non database filed that is defined as char.
0
 
sdstuberCommented:
your mask should be a string too

99.90  is just 99.9

it  should be

'99.90'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
OK, I'm not a forms person but as long as it makes sense to you.

Will the if statement work (even with my typo missing the quotes)?
0
 
anumosesAuthor Commented:
Columns in the database.
 
WB_HIGH        NUMBER,
  WB_LOW         NUMBER,
  RBC_HIGH       NUMBER,
  RBC_LOW        NUMBER,

If I have to save the data entered in the forms as 12.4, or 12.0, it has to be saved as entered. So do I have to alter the table? If so is  this the correct way?
WB_HIGH        NUMBER(2,1),
  WB_LOW         NUMBER(2,1),
  RBC_HIGH       NUMBER(2.2),
  RBC_LOW        NUMBER (2,2),
0
 
sdstuberCommented:
if you need 3 digits total  with one of them to the right of the decimal point...

NUMBER(3,1)
0
 
slightwv (䄆 Netminder) Commented:
Just like your previous questions, store it one way, force the layout when you select it.

If you have the columns as number in the database it will store 12, 12.1, etc...

then when you go to display it, use to_char.

If you change the column definition, you need to account for entered values that fall outside the layout.
0
 
sdstuberCommented:
NUMBER(2,1)  means  you can store values like this   1.2
NUMBER(2.2)  is a syntax error
NUMER(2,2)  means you can store values like this .12  (or 0.12  the leading 0 is optional)
0
 
slightwv (䄆 Netminder) Commented:
To put into words what sdstuber is saying:  when you define a number with params syntax is(max length, precision)

So, 2,1 says maximum of two numbers and one of these is for the decimal places.

Therefore inserting 12 will fail.
0
 
johnsoneSenior Oracle DBACommented:
It is not the maximum length that is defined in the precision, it is the number of significant digits.

There is a good example of this in the documentation:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#sthref120

If you define your column as number(2,2) putting 0.2345 is not an error.
0
 
sdstuberCommented:
>>> If you define your column as number(2,2) putting 0.2345 is not an error.

yes no error,  but the stored value  is only 0.23

0
 
johnsoneSenior Oracle DBACommented:
Correct.  Just wanted to point out that it really isn't max length and precision.  It is significant digits and decimal places.

Theoretically, you can store a number with more than 38 digits, it just drops significant digits and add an exponent.  For that matter you can store a number larger than the field with exponents as well.
0
 
sdstuberCommented:
>>> significant digits and decimal places.

actually that's not quite correct either

it's precision (significant digits) and scale (which is basically the negative of the exponent for exponential notation)


NUMBER(2,-2)  is correct syntax , the only legal values are 100, 200, 300,..., 9900
0
 
anumosesAuthor Commented:
So If I define the column in the database as Number(2,1) then storing either
16.0 or 16.2 should be ok right?
In case of the value 16.0, I want that to be saved in the database as 16.0

  WB_HIGH        NUMBER(2,1) value may be 16.0 or 16.2
  WB_LOW         NUMBER(2,1), value may be 10.0 or 11.2
   RBC_HIGH       NUMBER(2,2), value may be 2.34 or 2.30
   RBC_LOW        NUMBER (2,2) valuer may be 1.10 or 1.12

0
 
sdstuberCommented:
>> I want that to be saved in the database as 16.0

no

numbers don't have trailing 0's.   as mentioned above,  that's a formatting issue when you display it
0
 
sdstuberCommented:
>>NUMBER(2,2), value may be 2.34 or 2.30
>>> NUMBER (2,2) valuer may be 1.10 or 1.12

no  as already explained above... http:#36563153

2,2 means 2 digits total,  2 to the right of the decimal     0.10 or 0.12



>>> NUMBER(2,1) value may be 16.0 or 16.2
>>> NUMBER(2,1), value may be 10.0 or 11.2

no, as already explained above ....  http:#36563153

2,1 means 2 digits total,  1 to the right of the decimal



as stated above, (http:#36563134 )  if you want 16.2  that's 3 digits total, 1 to the right of the decimal  so NUMBER(3,1)

0
 
anumosesAuthor Commented:
Number(3,1) - value that can be store is 16.2
Number(4,2) - value that can be stored is 16.45
 Is this correct?
0
 
slightwv (䄆 Netminder) Commented:
Correct.

The docs have the detail on numbers and how they are stored.

This is the 11g link but numbers haven't changed since 9i in dealing with scale and precision:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#SQLRF00222
0
 
anumosesAuthor Commented:
thanks
0
 
anumosesAuthor Commented:
if :qc_tasks_poch.task_result != to_char(:qc_tasks_poch.task_result,'99.0') then
msgbox.show('WBC Count', 'Invalid Format'||chr(10)||'Format allowed is 99.0', 'OK');
      go_item('qc_tasks_poch.task_result');
      :qc_tasks_poch.task_result := null;
   raise form_trigger_failure;
end if;  

Failing at all levels
pic-1.JPG
pic-2.JPG
0
 
slightwv (䄆 Netminder) Commented:
Would you like this question to be unaccepted?

What data type is task_result?

As a quick test try:
if to_char(:qc_tasks_poch.task_result) != to_char(:qc_tasks_poch.task_result,'99.0') then
0
 
anumosesAuthor Commented:
char
I tried
if to_char(:qc_tasks_poch.task_result) != to_char(:qc_tasks_poch.task_result,'99.0') then


Still get the same error
0
 
sdstuberCommented:
sorry, when I first pointed out the mistake in your format,  I didn't give the complete string


change  '99.0'  to  'fm99.0'  

0
 
anumosesAuthor Commented:
Thanks sdstuber.. that worked.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 8
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now