0

I have a logic where I want to calculate and display numbers based upon some operations. The operations are below

V_CALPERCENT nvarhcar(100), v_yearMSA1 nvarchar(100),

So I want to calculate V_CALPERCENT and I have one value for v_yearMSA1 = 2

SO here goes my calculation,

V_CALPERCENT :=  (v_yearMSA1  * 2.25) / 100

Its returning me as .05

where as I want it should also display the number before decimal point. How to get it please help

  • 1
    The client software is typically responsible for formatting query output; so, what client are we talking about? – mustaccio Jan 12 at 14:37
  • @mustaccio: but the same I am trying using dual is giving me the expected format. What do you mean by client software ? – hud Jan 12 at 14:38
  • 1
    How can you "calculate V_CALPERCENT" when V_CALPERCENT is a character string, not a NUMBER. This is going to require implicit to_num and to_char operations. Use the correct data types. – EdStevens Jan 12 at 14:51
  • 1
    Why do you store numeric values in nvarchar(100) data type - it's almost the worst you can do. – Wernfried Domscheit Jan 12 at 19:03
1

Example:

SQL> select to_char((2 * 2.5) / 100) from dual;

TO_
---
.05

SQL> select to_char((2 * 2.5) / 100, '99990D99') from dual;

TO_CHAR((
---------
     0.05

SQL>

Update:

Number example:

SQL> select (2 * 2.5) / 100 as mynum from dual;

     MYNUM
----------
       .05

SQL> col mynum format 99990D99
SQL> select (2 * 2.5) / 100 as mynum from dual;

    MYNUM
---------
     0.05

SQL>

Number Format Models

  • so you mean to say, I need to use to_char for that ? – hud Jan 12 at 18:39
  • @hud No. I mean to say that you need to format the output with the required format string if you want to store it in a character type. Which you should not do anyway. Having v_yearMSA1 and V_CALPERCENT as char types - that is a huge red flag, this is poor quality code. Numbers should be stored in number types and formatting the output is the clients responsibility. – Balazs Papp Jan 12 at 21:26
  • So, shall I take the datatype as number, I tried it with that too but I was not getting any values before decimal.. WHY ? – hud Jan 13 at 10:14
  • @hud Because you did not set the proper display format. I added an example. – Balazs Papp Jan 13 at 19:43
  • Thanks Balazs, your explanation cleared my most of the doubts.. – hud Jan 14 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.