SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql select comment requirement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Guruprasath.cub
Starting Member

India
8 Posts

Posted - 03/29/2012 :  10:14:01  Show Profile  Reply with Quote
In one of the column of the table the values (amount) ranging from 562500.00 to 8956251.00. When using select comment the amount 562500.00 should be represented as 0000562500 and 8956251 should be reported as 0008956251. That is the amount need to be padl with '0' on left side and total char should be 10. How can I get it through select comment ?

Guru

X002548
Not Just a Number

15586 Posts

Posted - 03/29/2012 :  10:16:27  Show Profile  Reply with Quote
Guru,

Can you tell us WHY you need to do this in SQL and not displayed this way in a report or on the front end?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Guruprasath.cub
Starting Member

India
8 Posts

Posted - 03/29/2012 :  10:24:46  Show Profile  Reply with Quote
Sir,
This is actually for a report. If I get the comment, I will use the same and get the output as TXT file. Could you please help me ?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/29/2012 :  10:24:48  Show Profile  Reply with Quote

DECLARE @m decimal(15,2) = 562500.00
SELECT RIGHT(REPLICATE('0',10)+CONVERT(varchar(25),CONVERT(int,@m)),10)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/29/2012 :  10:25:49  Show Profile  Reply with Quote
Just curious though..doesn't your reporting tool allow you to do whatever formatting you want?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Guruprasath.cub
Starting Member

India
8 Posts

Posted - 03/29/2012 :  10:36:06  Show Profile  Reply with Quote
Thank Brett. I will use the comment and revert back
Once again thanks for your guidance !
Go to Top of Page

Guruprasath.cub
Starting Member

India
8 Posts

Posted - 03/29/2012 :  11:21:06  Show Profile  Reply with Quote
Brett !

I have used the following

select scheme, ac_type, country, currency,
right(replicate('0',10))+convert(varchar(25),convert(int,sum(amount)),10) as amount,
sum(no_ac) as no_ac from nrd_br_data where r_period=201111 and currency='AUD' group by scheme, country, currency order by scheme, country, currency

but it throws error message as 'The right function requires 2 arguments.'

Could you please come for help ?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000