Home » SQL & PL/SQL » SQL & PL/SQL » Merging " - " and "+" symbols
Merging " - " and "+" symbols [message #222452] Sun, 04 March 2007 22:09 Go to next message
choks
Messages: 4
Registered: March 2007
Location: PJ,Malaysia
Junior Member

Hi all,

i have a simple problem that need guidance. Every payment_amt values have where indicates whether +ve or -ve values but for +ve values there is no sign. for example, if payment_amt is 99.99 then no sign but if -99.99, there is, simple math, isn't?

i made a query which need every payment amount to be signed with - or + value. but it seemed that it doesn't work. here is the code:
select  '2 ', ac.acct_no ,to_char(ac.last_bill_date,'yyyymmdd') ,to_char(ic.due_date,'yyyymmdd'),trim(to_char(ic.payment_amt,'999,999,999.90')),'TRF', ac.bill_period , 
ac.acct_status , bi.code, '0002' 
from account ac, invoice ic, bill_info bi, payment p
where ic.acct_no = ac.acct_no
and ic.acct_no = p.acct_no
and ic.acct_no = bi.acct_no
AND ic.acct_no NOT like '_3____________'
AND ic.acct_no NOT like '_4____________'
and ic.bill_period = ac.bill_period
and ic.due_date = bi.due_date
and rownum < 3;


to_char(ic.due_date,'yyyymmdd'),/*trim(to_char(ic.payment_amt,'999,999,999.90'))*/

/* is where the problem is.

i want to view the query like this:
payment_sign payment_amt
----------- -----------
+               99.01
+               75.23
-               12.21
-               14.05 

payment_sign size is 1 and payment_amt is 11(2), where i wish could trim it, but maybe i'am, there must be a way without trimming the string, please anyone can help me, i'm still new about this.
Re: Merging " - " and "+" symbols [message #222473 is a reply to message #222452] Mon, 05 March 2007 00:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Here's one way:
select substr(to_char(ic.payment_amt, 'STM'),1,1) as SIGN
,      to_char(abs(ic.payment_amt),'999,999,999.90')


See this link for more info.

Ross Leishman
Re: Merging " - " and "+" symbols [message #222474 is a reply to message #222452] Mon, 05 March 2007 00:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select decode(sign(your_column), 1, '+', -1, '-') as payment_sign
,      abs(your_column)
from   your_table


[Edit: hm, too slow. Ross already anwered this.]

[Updated on: Mon, 05 March 2007 00:49]

Report message to a moderator

Re: Merging " - " and "+" symbols [message #222481 is a reply to message #222474] Mon, 05 March 2007 01:59 Go to previous messageGo to next message
choks
Messages: 4
Registered: March 2007
Location: PJ,Malaysia
Junior Member

Thank for the scripts but i keep trying until it come to this error which mean('i thought') the 2nd select will bring out more than a column. But, logically, the value of the 'SIGNED' should be directly from payment_amt, right? Maybe i'am too weak about queries.

here is the code that i mess up
select  '2 ', ac.acct_no ,to_char(ac.last_bill_date,'yyyymmdd') ,
to_char(ic.due_date,'yyyymmdd'),
	(select substr(to_char(ic.payment_amt,'STM'),1,1)as SIGNED from invoice ic),
to_char(abs(ic.payment_amt),'999,999,999.90')as payment

then the errors, as i thought...
(select substr(to_char(ic.payment_amt,'STM'),1,1)as SIGNED ),
 *
to_char(abs(ic.payment_amt),'999,999,999.90')as payment,
 
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row 


i used Ross's method first to test.
thanks for the help...
Re: Merging " - " and "+" symbols [message #222482 is a reply to message #222481] Mon, 05 March 2007 02:04 Go to previous message
choks
Messages: 4
Registered: March 2007
Location: PJ,Malaysia
Junior Member

Oh...sorry, i kept doing cycling around without noticing that it works. recent post was not totally checked. i just removed the 2nd select.

Well, thanks a lot to Ross, Frank for the helpful codes!
and others too.

[Updated on: Mon, 05 March 2007 02:07]

Report message to a moderator

Previous Topic: file open issue
Next Topic: select rows with stored procedure
Goto Forum:
  


Current Time: Sun Dec 04 02:43:37 CST 2016

Total time taken to generate the page: 0.04189 seconds