OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » using operators

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

using operators [message #411502]

Sat, 04 July 2009 07:17

lacchhii
Messages: 151
Registered: May 2009
Location: bangalore

Senior 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

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

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

lacchhii
Messages: 151
Registered: May 2009
Location: bangalore

Senior 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

cookiemonster
Messages: 13925
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

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

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

lacchhii
Messages: 151
Registered: May 2009
Location: bangalore

Senior 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

cookiemonster
Messages: 13925
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

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 01 22:38:59 CDT 2024