OraFAQ Forum: SQL & PL/SQL » I tried , adn tried, and tried, and for that reason, I'm notgoint to give up...

Home » SQL & PL/SQL » SQL & PL/SQL » I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... (windows 7, oracle 11g toad)

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

I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630072]

Fri, 19 December 2014 11:10

xebec
Messages: 37
Registered: July 2014
Location: Miraflores

Member

Hi my friends :

I have tis select statement:

SELECT LPAD(CTDCNROPER,2,'0') DMES, 
        CTDCNROANNO||LPAD(CTDCNROPER,2,'0')||CTDCCODTIPC||CTDCNROCOMP DNUMASIOPE,  
        CTDCCODCTA DNUMCTACON,
        TO_CHAR(CTDCFECLIB,'DD/MM/YYYY') DFECOPE,
        UPPER(SUBSTR(replace(replace(CTCCDESGLO,'|','-'),chr(10),''),1,100)) DGLOSA,
        NVL(CTDCCODCCO,'NULL') DCENCOS,
        decode(CTDCFLGDEB,'D', NVL(CTDCIMS,0), 0) DDEBE, 
        decode(CTDCFLGDEB,'C', NVL(CTDCIMS,0), 0) DHABER,
        CTDCNROANNO||LPAD(CTDCNROPER,2,'0')||CTDCCODTIPC||CTDCNROCOMP DINTREG
  FROM     (select ctctcodcta, ctctdescta, ctctcoddim from CTDMCTAS 
            where CTCTCODSUC  = '001'
              and CTCTCODCIA  = '002'),
               ctdccomb,ctdicomb

This part:
decode(CTDCFLGDEB,'D', NVL(CTDCIMS,0), 0) DDEBE,
Give me as a result a number... : 0 , 1,2 , 1.23, 0.2 etc...
But
I need that it will give me a especific result

number with 2 decimals .. and if it's 0... it will be 0.00

for example
it give me 0.4 and I will need is 0.40 ..... no Sad

it give me 1 and I will need is 1.00 ...... no Sad

it give me 0.41 and I will need is 0.41 ... ok

Please sombody may help me ...

Thanks you so much!! Very Happy

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630074 is a reply to message #630072]

Fri, 19 December 2014 11:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use TO_CHAR

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630075 is a reply to message #630074]

Fri, 19 December 2014 11:48

xebec
Messages: 37
Registered: July 2014
Location: Miraflores

Member

Dear @BlackSwan
Thanks you so much for your quickly answer Smile

Thanks you

But Would you be able to bbe more especific please..

I was thinking about one posibility:
To separate Integer and Decimals, but how?

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630076 is a reply to message #630075]

Fri, 19 December 2014 11:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

xebec wrote on Fri, 19 December 2014 09:48

Dear @BlackSwan
Thanks you so much for your quickly answer
Thanks you

But Would you be able to bbe more especific please..

I was thinking about one posibility:
To separate Integer and Decimals, but how?

are you incapable or just unwilling to Read The Fine Manual yourself?
Why should I write here what has been already documented by Oracle?

http://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_x_stddev018.htm#sthref4919

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630077 is a reply to message #630076]

Fri, 19 December 2014 11:56

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Or you could just set the "numeric format" in your reporting tool.

How to do it in SQL*Plus is in the documentation, too:

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12013.htm#BACHCABF

[Updated on: Fri, 19 December 2014 11:58]

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630078 is a reply to message #630076]

Fri, 19 December 2014 11:57

xebec
Messages: 37
Registered: July 2014
Location: Miraflores

Member

I read a lot of manuals.
But some manuals don't give me a details about how to do it Sad

What I'm going to do is.
Separate REC.DDEBE into enteros and decimales
then i will join both together...

Will it be ok???

Thanks for your answer.

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630080 is a reply to message #630078]

Fri, 19 December 2014 12:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Yes, you can make 3 left turns instead of making 1 right turn.
It is your database & code & you are free to abuse it any way you deem appropriate.

You can lead some folks to knowledge, but you can't make them think.

http://www.orafaq.com/wiki/TO_CHAR

[Updated on: Fri, 19 December 2014 12:23]

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630083 is a reply to message #630080]

Fri, 19 December 2014 14:53

xebec
Messages: 37
Registered: July 2014
Location: Miraflores

Member

People

I have my procedure on oracle form 6i:

PROCEDURE EXPORTA_DIARIO (Cia IN CHAR, SUC IN CHAR, ANIO IN NUMBER) IS
  LF_file  Text_IO.File_Type;  
  LV_c     VARCHAR2(10) := '''';
  nroint varchar2(20);
  serie varchar2(5);
  numero varchar2(25);
  ruc varchar2(15);
  razon varchar2(200);
  codigo varchar2(20);
  ENTERO     VARCHAR2(9);
  DECIMaLes     VARCHAR2(2);
  total varchar2(12);

CURSOR LC_cuenta IS
  SELECT LPAD(CTDCNROPER,2,'0') DMES, 
        CTDCNROANNO||LPAD(CTDCNROPER,2,'0')||CTDCCODTIPC||CTDCNROCOMP DNUMASIOPE,  
        CTDCCODCTA DNUMCTACON,
        TO_CHAR(CTDCFECLIB,'DD/MM/YYYY') DFECOPE,
        UPPER(SUBSTR(replace(replace(CTCCDESGLO,'|','-'),chr(10),''),1,100)) DGLOSA,
        NVL(CTDCCODCCO,'NULL') DCENCOS,
        decode(CTDCFLGDEB,'D', NVL(CTDCIMS,0), 0) DDEBE, 
	      decode(CTDCFLGDEB,'C', NVL(CTDCIMS,0), 0) DHABER,
        CTDCNROANNO||LPAD(CTDCNROPER,2,'0')||CTDCCODTIPC||CTDCNROCOMP DINTREG
  FROM 	(select ctctcodcta, ctctdescta, ctctcoddim from CTDMCTAS 
            where CTCTCODSUC  = SUC
              and CTCTCODCIA  = CIA),
               ctdccomb,
	ctdicomb
 WHERE CTCTCODCTA  = CTDCCODCTA
	    AND CTCCCODEST  in ('2','3')
			AND CTCCNROCOMP = CTDCNROCOMP
			AND CTCCCODTIPC = CTDCCODTIPC
			AND CTCCNROANNO = ANIO 
	    and CTCCCODSUC  = SUC
      AND CTCCCODCIA  = CIA
	    AND CTDCNROANNO = CTCCNROANNO
	    AND CTDCCODSUC  = CTCCCODSUC
	    AND CTDCCODCIA  = CTCCCODCIA
        ORDER BY 1,4,2,3;


BEGIN   --here I star the operation
 	entero := LPAD(RTRIM(TRUNC(REC.DDEBE)),9,'0');
	decimales := SUBSTR(LTRIM(RTRIM(SUBSTR(RPAD(TO_CHAR(REC.DDEBE-TRUNC(REC.DDEBE)),3,'0'),2,2))),1,2);
	total := entero||'.'||decimales;
 
   --The File is openned
  LF_file := Text_IO.Fopen(:B02.T_NOMBRE||'DIARIO.TXT', 'w');      Text_IO.Put_Line(LF_file,'DMES'||
:B01.SP||'DNUMASIOPE'||:B01.SP||'DNUMCTACON'||:B01.SP||'DFECOPE'||
:B01.SP||'DGLOSA'||:B01.SP||'DCENCOS'||:B01.SP||'DDEBE'||:B01.SP||'DHABER'||:B01.SP||'DINTREG');
  
  FOR rec IN LC_cuenta LOOP Text_IO.Put_Line(LF_file,REC.DMES||:B01.SP||REC.DNUMASIOPE||:B01.SP||REC.DNUMCTACON||
:B01.SP||REC.DFECOPE||:B01.SP||REC.DGLOSA||:B01.SP||NVL(REC.DCENCOS,'NULL')||:B01.SP||ROUND(REC.DDEBE,2)||
:B01.SP||ROUND(REC.DHABER,2)||:B01.SP||REC.DINTREG);
  
  END LOOP;
  Text_IO.Put_Line(LF_file, '');
  Text_IO.FClose(LF_file);
END;

I did this:

entero := LPAD(RTRIM(TRUNC(REC.DDEBE)),9,'0');
decimales := SUBSTR(LTRIM(RTRIM(SUBSTR(RPAD(TO_CHAR(REC.DDEBE-TRUNC(REC.DDEBE)),3,'0'),2,2))),1,2);
total := entero||'.'||decimales;

[Updated on: Sat, 20 December 2014 01:05] by Moderator

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630084 is a reply to message #630083]

Fri, 19 December 2014 14:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1* select 1, to_char(1,'9.99') from dual
SQL> /

         1 TO_CH
---------- -----
         1  1.00

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630086 is a reply to message #630084]

Sat, 20 December 2014 00:40

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

But FOR 0 it will NOT WORK.

 SELECT 0, to_char(0,'9999999990.99') FROM dual;

[Updated on: Sat, 20 December 2014 00:42]

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630087 is a reply to message #630086]

Sat, 20 December 2014 00:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It works for me.

SQL> SELECT 0, to_char(0,'9999999990.99') FROM dual;
  
         0 TO_CHAR(0,'999
---------- --------------
         0           0.00

Report message to a moderator

Re: I tried , adn tried, and tried, and for that reason, I'm notgoint to give up... [message #630088 is a reply to message #630086]

Sat, 20 December 2014 00:52

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Or better:

SELECT 0, to_char(0,'0.00') FROM dual;

And even better:

SELECT 0, to_char(0,'0D00') FROM dual;

[Updated on: Sat, 20 December 2014 01:03]

Report message to a moderator

Previous Topic:	How to fetch a single record in an array/nested table
Next Topic:	Open cursor for select, based on another cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 25 01:14:51 CDT 2026