• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

oracle forms value formatting question

I have a field in the results table( num_value) which is defined in the table as number
The values entered are 16.0 etc
In the post query of the form I am doing this conversion
If upper(:qc_task_results_poch.task_desc) = upper('WBC count')
or upper(:qc_task_results_poch.task_desc) = upper('WBC Count 0.3 x 10(3) ul or less?')then
  :qc_task_results_poch.num_value := to_char(:qc_task_results_poch.num_value,'fm99.0');
End if;
---------------Look at the attachment
value 17 has to be 17.0
and 47 has to be 47.0
I made the column in the form as char. But does not display. The column in the faorm properties can not be fiven a format mask since there are multiple values and each has it format. So its stored as a num_value.
screen-print.JPG
0
anumoses
Asked:
anumoses
  • 11
  • 7
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Based on your previous question 'fm99.0' will work so my guess is for some reason it is not entering your IF statement.

Can you verify that you are getting into the IF statement?
0
 
anumosesAuthor Commented:
Its not going into the if statement. I have the code in post query.
0
 
slightwv (䄆 Netminder) Commented:
Then I'm not understanding the question.  If the IF statement you posted isn't being called then what isn't working?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
anumosesAuthor Commented:
How can I write a decode statement

If substr(task_desc,1,3) = 'WBC' then
num_value = to_char(num_value,'fm99.0'
elsif substr(task_desc,1,3) = ' RBC' then
num_value = to_char(num_value,'fm99.90'  etc......
0
 
slightwv (䄆 Netminder) Commented:
a SQL decode would go like this:
decode(expression,if_this1,then_this1,if_this2,then_this_2,...,if_nothing_matches,then_this)

So from what you posted:

decode(substr(task_desc,1,3),'WBC',to_char(num_value,'fm99.0'),' RBC',to_char(num_value,'fm99.0'),num_value)
0
 
anumosesAuthor Commented:
can I use like WBC% in decode?
0
 
anumosesAuthor Commented:
Cursor C5 is select decode(substr(task_desc,1,3),'WBC',to_char(num_value,'fm99.0'),'RBC',to_char(num_value,'fm99.90'),
                     'HGB',to_char(num_value,'fm99.0'),'HCT',to_char(num_value,'fm999.0'),'PLT',num_value,num_value),alpha_value
   from qc_task_poch_results A,QC_TASKS B
  where a.task_id = b.task_id
    and a.procedure_id = :qc_task_results_poch.procedure_id
    and a.task_id = :qc_task_results_poch.task_id
    and drive_id = :qc_daily.drive_id;  

Is this correct as per you suggestion? of decode statement?
0
 
anumosesAuthor Commented:
Any Oracle forms experts???

select task_desc,decode(substr(task_desc,1,3),'WBC',to_char(num_value,'fm99.0'),'RBC',to_char(num_value,'fm99.90'),
                     'HGB',to_char(num_value,'fm99.0'),'HCT',to_char(num_value,'fm999.0'),'PLT',num_value,num_value) as value,alpha_value
   from qc_task_poch_results A,QC_TASKS B
  where a.task_id = b.task_id
   and a.procedure_id = 1127
  --  and a.task_id = :qc_task_results_poch.task_id
    and drive_id = 'DRV5528593'
-----------------
This runs fine is toad

TASK_DESC|VALUE|ALPHA_VALUE
WBC count|3.0|
RBC count|2.33|
HGB count|6.0|
HCT Count|17.0|
PLT count|56|

But when I run the same in oracle forms, I dont get the formatting. Any help from forms experts is appreciated
0
 
anumosesAuthor Commented:
I use open cursoe and fetch

Open C5;
Fetch C5 into :qc_task_results_poch.num_value
            ,:qc_task_results_poch.alpha_value;
CLose C5;

Is there a way to use formatting when I fetch?
0
 
flow01Commented:
De post-query trigger fires after getting a row of data from the database and provides a way to change data fetched from the database or fill data-items based on the fetched data (for example a description of a foreign key).
De trigger doesn't fire after entering data in the form:
So me quess is: if you commit the value 47  and requery the data is wil be shown as 47.0. : because de query the post-query  fires.
If you want to modify data you entered in the form try using the
when_validate_item_trigger.
0
 
anumosesAuthor Commented:
I am post querying the values frommthe results table. So its not when validate. The data ia already entered in that format, but the database drops the decimal if its a zero. So I have to post query in that format. I cannot give a format mask as there are four different format masks on the same field based on the value retrieved.
0
 
slightwv (䄆 Netminder) Commented:
>>The data ia already entered in that format, but the database drops the decimal if its a zero

This is normal and you cannot do anything about this.  Oracle does not store insignificant digits when storing a number.  If you require a specific format, you need to define it either in the form or when selecting it using to_char.
0
 
anumosesAuthor Commented:
As said by slightwv I did this

If upper(:qc_task_results_poch.task_desc) = upper('WBC count')
or upper(:qc_task_results_poch.task_desc) = upper('WBC Count 0.3 x 10(3) ul or less?')then
  :qc_task_results_poch.num_value := to_char(:qc_task_results_poch.num_value,'fm99.0');
End if;

But did not work
0
 
slightwv (䄆 Netminder) Commented:
I am getting confused here.  

What did not work?  Displaying the values or how they are stored?

You also stated way up top that the IF statement isn't being called?

0
 
anumosesAuthor Commented:
If statement is called in the post-query trigger. But the values are not being displayed as expected.
0
 
slightwv (䄆 Netminder) Commented:
Can you put some sort of message box in the if statement that shows the values of to_char(:qc_task_results_poch.num_value,'fm99.0') to make sure it is being called?
0
 
anumosesAuthor Commented:
After putting messages, I get the value but its 17 instead of 17.0
screen1.JPG
screen2.JPG
0
 
slightwv (䄆 Netminder) Commented:
If the msgbox is the value of the to_char call then you might be hitting a bug of some type or forms is overriding the to_char formatting.
0
 
anumosesAuthor Commented:
thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now