oracle sql doubt - experts plz

[KakiJanaky]

please help,

oracle sql lo ela format cheyyali column ni:

If the dollar amount is $125 for total charges, then it should display with a 7 digit number padded with leading zeroes followed by 2 decimal places and the sign (+/-).

 

For eg: 

--> If amount is $125, then

000012500+

--> If amount is -$155, then

 

000015500-

[teluguraja]

select statement lo idhi padeyyi

 

lpad(coulm_name, 7, '0')  || decode(sign(column_name), -1, '-', 1, '+', ' ') final_value

[KakiJanaky]

4 minutes ago, teluguraja said:

select statement lo idhi padeyyi

 

lpad(coulm_name, 7, '0') || decode(sign(column_name), -1, '-', 1, '+', ' ') final_value

what's this?

[Lakhan]

2 minutes ago, KakiJanaky said:

what's this?

iPad kadu LPad adhi 

[KakiJanaky]

nevermind i got it let me try

[teluguraja]

1 minute ago, Lakhan said:

iPad kadu LPad adhi 

adhi kooda cheppala

[CheGuevara]

25 minutes ago, KakiJanaky said:

please help,

oracle sql lo ela format cheyyali column ni:

If the dollar amount is $125 for total charges, then it should display with a 7 digit number padded with leading zeroes followed by 2 decimal places and the sign (+/-).

 

For eg: 

--> If amount is $125, then

000012500+

--> If amount is -$155, then

 

000015500-

Does the dollar amount always be in hundreds? If the amount Differs which is other than hundres, then tou still want the format to be seven digit? If yes how should the padding of zeroes differs in the front and at the back for each differed amount?

[KakiJanaky]

33 minutes ago, teluguraja said:

select statement lo idhi padeyyi

 

lpad(coulm_name, 7, '0')  || decode(sign(column_name), -1, '-', 1, '+', ' ') final_value

7 digit number padded with leading zeroes followed by 2 decimal places and the sign (+/-). 

It worked for 7 digits and sign but, When the amount is 653.8, final value displays as 00653.8+ which is not what I want. It should display 000065380+. 

7 digits total along with 2 decimal places and a sign.

[KakiJanaky]

2 minutes ago, CheGuevara said:

Does the dollar amount always be in hundreds? If the amount Differs which is other than hundres, then tou still want the format to be seven digit? If yes how should the padding of zeroes differs in the front and at the back for each differed amount?

,correct, the amount varies, it can be any number. Ultimately, the final value should be 7 digits (coz it cant be more than 7 digits) and 2 zeros for decimal replacement and sign.

[Satabdi]

18 minutes ago, Lakhan said:

iPad kadu LPad adhi 

Ipad lo work chesthe alav rayali code 

nuvvu ukovayya 

[KakiJanaky]

in sql server, i can do this, but not in oracle. also no privileges to create temp tables

 

declare @amt as decimal(9,2) = -125.05

 

SELECT

Convert(varchar(9),convert(bigint,abs(@amt)*100)) as amount

,@amt as amount_numeric

into #temp

 

select right('0000000000'+amount, 9)

+ case when amount_numeric >=0 then '+' else '-' end

,amount_numeric

from #temp

 

drop table #temp

[prabhu_pramod]

SELECT CASE 
         WHEN y >= 0 THEN Lpad(To_char(y * 100), 9, 0) 
                          ||'+' 
         ELSE Lpad(To_char(Abs(y * 100)), 9, 0) 
              ||'-' 
       END z 
FROM   (SELECT -125.25 y 
        FROM   dual); 

 

Change the Y to your column

[kingcasanova]

On 08/06/2018 at 2:21 AM, KakiJanaky said:

in sql server, i can do this, but not in oracle. also no privileges to create temp tables

 

declare @amt as decimal(9,2) = -125.05

 

SELECT

Convert(varchar(9),convert(bigint,abs(@amt)*100)) as amount

,@amt as amount_numeric

into #temp

 

select right('0000000000'+amount, 9)

+ case when amount_numeric >=0 then '+' else '-' end

,amount_numeric

from #temp

 

drop table #temp

3 decimal value isthe theda koduthundemo?