Padding an implied $ amt field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Padding an implied $ amt field

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-07 : 09:46:43

HI Guys, I have a question. I have to output 2 implied decimal amount fields (AJ_Dollar_Amount & AJ_Amount). I have defined they as decimal (13, 0) and decimal (17, 0) respectively. I was told to use the following func to pad the field, but it is not working. How should I define field(s) or rewrite the Cast statement to get it to work?

Right('0000000000000' + Cast(AJ_Dollar_Amount as varchar (13)), 13),

Thank you,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-07 : 10:01:20

What output are you getting?

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-07 : 11:54:21

Hi Visakh16, I'm getting 200, 200, 1125 for the first field, and in the second field 1525 which is the sum of the three values in the first field.

Thanks for responding so quickly.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-08 : 17:57:10

Works fine on my box...

SELECT Right('0000000000000' + Cast(12.345 as varchar (13)), 13)              
------------- 
000000012.345

(1 row(s) affected)

--Jeff Moden

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 18:14:03

quote:
Originally posted by Trudye

HI Guys, I have a question. I have to output 2 implied decimal amount fields (AJ_Dollar_Amount & AJ_Amount). I have defined they as decimal (13, 0) and decimal (17, 0) respectively. I was told to use the following func to pad the field, but it is not working. How should I define field(s) or rewrite the Cast statement to get it to work?

Right('0000000000000' + Cast(AJ_Dollar_Amount as varchar (13)), 13),

Thank you,

No decimal places in the actual field?

What do you do for a number that might have 14 digits? (yeah, I know in dollars, that is unlikely ;))

Either way..

Select RIGHT(Replicate('0',13) + Cast(field as varchar),13)

If you have a 17 digit field and any more than 13 of those are use, you have some issues to deal with because you can't cast 14 digit number into a 13 character varchar


--this will error!
Select RIGHT(Replicate('0',13) + cast(12345689012345678 as varchar(13)),13)

--this loses value as the 1st 4 numbers get cutoff
Select RIGHT(Replicate('0',13) + cast(12345689012345678 as varchar(17)),13)

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 18:34:22

quote:
Originally posted by Trudye

Hi Visakh16, I'm getting 200, 200, 1125 for the first field, and in the second field 1525 which is the sum of the three values in the first field.

Thanks for responding so quickly.

Looking at this again...what is your required output from those values?

Poor planning on your part does not constitute an emergency on my part.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-08 : 19:19:59

why are you using decimal (13, 0) and decimal (17, 0) anyway? this way you don't have any numbers after the decimal point.
so why not just use an int?
and padding should be done on the client side.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-08 : 20:23:46

Yeah... I kinda missed the zero decimal place thing...

Also, I have to ask... is there actually a client side or is this for a report right straight out of a query?

--Jeff Moden

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-10 : 06:29:13

Hi guys thanks for responding. There is client side but the contract states we must present the data in a prescribed format and that includes padding.
Spirit1, I changed the field to int just to see what would happen and I still am not getting the padding. Here is my latest code:

DECLARE @Cnt int;
DECLARE @Amt int;

BEGIN --HDR
-- HDAJ ACSB 022208 082815 6 Byte date(mmddyy) 6 byte time (hhmss) 2 seperate fields
INSERT INTO dbo.tbl_Output (Record_Code, Institution, hdrDate, hdrTime)
Values ('HDAJ','ACSB', replace(CONVERT(CHAR(8), getdate(), 10), '-', ''),
replace(CONVERT(CHAR(8), GETDATE(), 108), ':', ''))

--DTL
INSERT INTO dbo.tbl_Output (Record_Code, AJ_Batch_Type, AJ_Batch_Code,
AJ_Batch_Num, AccountNumber, AJ_Tran_Code, Merchant_No, AJ_Dollar_Amt, --25
Reason_Code, AJ_Merch_Descr, [Timestamp])
SELECT AJ_Record_Code, AJ_Batch_Type, AJ_Batch_Code, AJ_Batch_Num,
AccountNumber, AJ_Tran_Code, Merchant_No,
Right('0000000000000' + Cast(AJ_Amount as varchar (13)), 13),
Reason_Code, AJ_Merch_Descr, [Timestamp]
FROM dbo.tblArchive

--TRL
SET @Cnt = (SELECT Count(AJ_Record_Code) FROM dbo.tblArchive GROUP by AJ_Record_Code) +
SET @Amt = (SELECT Sum(AJ_Amount) FROM dbo.tblArchive GROUP by AJ_Record_Code)
INSERT INTO dbo.tbl_Output
(Record_Code,
Institution,
Record_Count,
Adj_Amount)
SELECT 'TLAJ','ACSB', Right('00000' + Cast(@Cnt as varchar (5)), 5),
Right('00000000000000000' + Cast(@Amt as varchar (17)), 17)
END
END

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-10 : 07:15:43

What is the client application that is being used to present the data? Would it be too easy and simple to format your output there?

- Jeff
http://weblogs.sqlteam.com/JeffS

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-10 : 20:06:13

I'm sorry Guys I should have responded earlier I figured it out this morning. But I got busy trying to make my deadline. Thanks so much for hanging in there with me.

Be well,
Trudye

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 06:50:09

quote:
Originally posted by Trudye

I'm sorry Guys I should have responded earlier I figured it out this morning. But I got busy trying to make my deadline. Thanks so much for hanging in there with me.

Be well,
Trudye

You didnt give us enough information

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources