Solved

oracle forms value formatting question

Posted on 2011-09-29
19
294 Views
Last Modified: 2012-05-12
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
Comment
Question by:anumoses
  • 11
  • 7
19 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36817400
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
 
LVL 6

Author Comment

by:anumoses
ID: 36817484
Its not going into the if statement. I have the code in post query.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36817512
Then I'm not understanding the question.  If the IF statement you posted isn't being called then what isn't working?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36817576
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 36817603
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
 
LVL 6

Author Comment

by:anumoses
ID: 36892304
can I use like WBC% in decode?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36892344
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
 
LVL 6

Author Comment

by:anumoses
ID: 36892518
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
 
LVL 6

Author Comment

by:anumoses
ID: 36892795
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Expert Comment

by:flow01
ID: 36899497
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
 
LVL 6

Author Comment

by:anumoses
ID: 36902764
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36903044
>>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
 
LVL 6

Author Comment

by:anumoses
ID: 36903114
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36903160
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
 
LVL 6

Author Comment

by:anumoses
ID: 36903164
If statement is called in the post-query trigger. But the values are not being displayed as expected.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 36903251
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
 
LVL 6

Author Comment

by:anumoses
ID: 36903506
After putting messages, I get the value but its 17 instead of 17.0
screen1.JPG
screen2.JPG
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36904022
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36904261
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now