Members   Search   Help    Register    Login   Home
Home » Developer & Programmer » Reports & Discoverer » using operators
using operators [message #411502] Sat, 04 July 2009 07:17 Go to next message
lacchhii
Messages: 22
Registered: May 2009
Location: bangalore
Junior Member
hi all,

In one of my reports the values of amount column has to be specified as follows

(+) 32,54,123
(-) 22,55,012

and so on.

the problem i face is if i put the + and - sign within brackets, i am not able to put punctuation in the amount and if i use punctuation i am not able to put the + and - sign within brackets.

Is there a way to get the output of numbers in the same format as mentioned above

narayan
Report message to a moderator
Re: using operators [message #411503 is a reply to message #411502] Sat, 04 July 2009 07:36 Go to previous messageGo to next message
vamsi kasina
Messages: 1597
Registered: October 2003
Location: Hyderabad, India
Senior Member
I think you can have another filed to show with the following content.
decode(sign(amount),1,'(+) ' || amount
                   ,-1, '(-) ' || abs(amount)
                   ,amount);
By
Vamsi

[Updated on: Sat, 04 July 2009 07:36]

Report message to a moderator
Re: using operators [message #411527 is a reply to message #411503] Sat, 04 July 2009 13:40 Go to previous messageGo to next message
Littlefoot
Messages: 9166
Registered: June 2005
Location: Croatia, Europe
Senior Member
Sounds good. Though, "amount" might need TO_CHAR in order to set the punctuation, something like 
TO_CHAR(amount, '99,99,999')
Report message to a moderator
Re: using operators [message #411646 is a reply to message #411503] Mon, 06 July 2009 03:34 Go to previous messageGo to next message
lacchhii
Messages: 22
Registered: May 2009
Location: bangalore
Junior Member
hi,

i used your code and i got the result of + and - within brackets, but the amount does not have punctuation and in the property palette also the format mask does not come to use the punctuation.

the result after using the decode function is like this

amount

(+) 1000
(+) 2000
(-)-500

I dont want the minus sign to repeat again and i also want the numbers to be like this

(+) 1,000
(+) 2,000
(-) 500

could you pl let me know the necessary corrections

lacchhii
Report message to a moderator
Re: using operators [message #411672 is a reply to message #411502] Mon, 06 July 2009 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 1340
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post the code you used
Report message to a moderator
Re: using operators [message #411680 is a reply to message #411646] Mon, 06 July 2009 05:50 Go to previous messageGo to next message
Littlefoot
Messages: 9166
Registered: June 2005
Location: Croatia, Europe
Senior Member
Quote:
but the amount does not have punctuation
How would it, if you didn't specify any format? Did you use TO_CHAR function?

Quote:
in the property palette also the format mask does not come to use the punctuation
I bet it doesn't; strings don't have format - numbers do. But after concatenating (-) with 500, it is not a number any more but a string. Therefore, back to previous lines - use TO_CHAR.
Report message to a moderator
Re: using operators [message #411723 is a reply to message #411680] Mon, 06 July 2009 08:03 Go to previous messageGo to next message
lacchhii
Messages: 22
Registered: May 2009
Location: bangalore
Junior Member
hi,

i used the code this way using decode and to_char, but i got the error message "ORA-017222--INVALID NUMBER"

CODE USED

SELECT MJH,
DECODE(SIGN(TO_CHAR(PAMT,'99,99,999')),1,'(+)'||' '||PAMT,-1,'(-)'||' '||ABS(PAMT),PAMT)

I DID TRY WRITING IT THIS WAY ALSO

SELECT MJH,
DECODE(SIGN(TO_CHAR(PAMT,'99,99,999')),1,'(+)'||' '||(TO_CHAR(PAMT,'99,99,999')),-1,'(-)'||' '||ABS((TO_CHAR(PAMT,'99,99,999'))),(TO_CHAR(PAMT,'99,99,999')))

I get the desired result if i use the decode and to_char function seperately.

Kindly help me out and correct the mistake.

lacchhii
Report message to a moderator
Re: using operators [message #411731 is a reply to message #411502] Mon, 06 July 2009 08:30 Go to previous messageGo to next message
cookiemonster
Messages: 1340
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sign accepts numbers as arguments, not chars.
You need to to_char the bits that are going to be displayed, nothing else.
Lose the first to_char.
Report message to a moderator
Re: using operators [message #411761 is a reply to message #411723] Mon, 06 July 2009 10:59 Go to previous message
Littlefoot
Messages: 9166
Registered: June 2005
Location: Croatia, Europe
Senior Member
Here's how it goes: as Cookiemonster has said, SIGN function accepts numbers:
SQL> select sign(123456) from dual;

SIGN(123456)
------------
           1

In order to convert number to a character with desired format, we use TO_CHAR function:
SQL> select to_char(123456, '999,999') from dual;

TO_CHAR(
--------
 123,456

So far, so good.

Now let's try to apply SIGN to this character:
SQL> select sign(to_char(123456, '999,999')) from dual;
select sign(to_char(123456, '999,999')) from dual
            *
ERROR at line 1:
ORA-01722: invalid number
Nope, won't work. It is the same as
SQL> select sign('123,456') from dual;
select sign('123,456') from dual
            *
ERROR at line 1:
ORA-01722: invalid number
which is - as we are talking about strings - the same as
SQL> select sign('abcd') from dual;
select sign('abcd') from dual
            *
ERROR at line 1:
ORA-01722: invalid number

Do you understand it now? You can't determine SIGN of a string!
Report message to a moderator
Previous Topic:Datesas paramaeters
Next Topic:report layout in 6i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 7 21:38:03 CST 2009

Total time taken to generate the page: 0.33419 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.