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

x
?
Solved

oracle forms value formatting question

Posted on 2011-09-29
19
Medium Priority
?
338 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 78

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 78

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
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!

 
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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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
 
LVL 21

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 78

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 78

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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 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 78

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

877 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