Convert $ to varchar decimal problem

I'm trying to convert a check amount to a fixed length string with
leading zeros and no decimal point. All is well, except for the pesky
decimal point. Here is what I have:
COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
(CONVERT(varchar(12),ckamt))
Thanks for any ideas on how to accomplish this.What exactly do you need? Could you give an example of the expected result?
E.g. I have 3.04, I want 003...
ML
http://milambda.blogspot.com/|||Multiply the number by 100 (or which ever multiple of 10 will remove the
decimal place), turn that number into an integer and then convert it into a
string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
Ta,
M. E. Houston
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Take a look at this
declare @.d decimal(12,2)
select @.d =3.04
select @.d, right('000000000000' +
convert(varchar,replace(@.d,'.','')),12)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
birdbyte@.gmail.com wrote:
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.|||Try,
declare @.m money
declare @.i int
set @.m = 12345.54
set @.i = 12
select replace(str(round(@.m, 0, 1), @.i, 0), ' ', '0')
go
AMB
"birdbyte@.gmail.com" wrote:

> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Replicate to 13, and then REPLACE({your stuff below}, '.', '')
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||I don't think this this idea is quite right. If you REPLACE() the decimal in
the decimal value, it will round.
I think you need to REPLACE() the decimal after it is converted to a
varchar().
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1151514826.136274.129340@.x69g2000cwx.googlegroups.com...
> Take a look at this
> declare @.d decimal(12,2)
> select @.d =3.04
> select @.d, right('000000000000' +
> convert(varchar,replace(@.d,'.','')),12)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> birdbyte@.gmail.com wrote:
>|||Or convert the decimal to an int then convert to varchar. These two
methods are assuming you want to always round down.
On Wed, 28 Jun 2006 11:52:35 -0500, "M. E. Houston"
<m.e.houston@.gmail.com> wrote:

>Multiply the number by 100 (or which ever multiple of 10 will remove the
>decimal place), turn that number into an integer and then convert it into a
>string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
>Ta,
>M. E. Houston
><birdbyte@.gmail.com> wrote in message
>news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
>|||Great suggestion. Thanks.
Arnie Rowland wrote:
> Replicate to 13, and then REPLACE({your stuff below}, '.', '')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <birdbyte@.gmail.com> wrote in message
> news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...

Controlling errors in Stored Procedure

Hi everyone:

I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like theTRY-CATCH-FINALLY statement (inSQL-92 forSQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error israised.

Can it be done?

Thanks for any help.

No, I'm afraid in SQL2000 we can not do the error handling like usingTRY-CATCH-FINALLY block. If you only want to limit the rows returned by SELECT statements, you can use TOP key word instead. For example:

select top 1 * from sysobjects

|||

Ok, thanks Lori_jay.

Control/Limit Memory usage

Hello,

I was wondering if there was a setting or a way to limit or restrict the amount of Memory an SSIS package can use? I know that by default the windows OS limits a process (a package in this case) to 2GB and up to 3GB with AWE enable but what if I wanted to say Limit it to 1GB of memory is there anyway to do that? Is there an SSIS Engine setting or Package property somewhere?

Thanks!

See if this helps you. Check out the Buffer size section in this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

Construction of view or sp

I have the following tables:
tblAccount:
-Account
tblAmount:
-ProjectID
-Account
-Amount1
-Amount2
tblOrder:
-OrderID
-ProjectID
-Account
-Amount
tblTransaction:
-TransactionID
-ProjectID
-Account
-Amount
I would like to show all accounts in tblAccount and if there are amount
values on the accounts in the other tables they should be shown next to
the account number. If there are no values in the other tables the
account without value should still be shown.
Which is the best way to do this, a view or sp and with which syntax?
Regards,
SThis is a multi-part message in MIME format.
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Something like:
SELECT
ProjectID,
Account,
OrderAmt =3D isnull(( SELECT sum( Amount ) FROM tblOrder WHERE =Account =3D a.Account GROUP BY Account ), 0 )
TransAmt =3D isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE =Account =3D a.Account GROUP BY Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends upon how you will use this, and =how often you will use it.
On Another Note: [tbl] as a table prefix is 'old school'. Actually 3 =wasted keystrokes since they provide no additional value. (Make every =keystroke useful.) You know it is a table because it follows the FROM =keyword.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<staeri@.gmail.com> wrote in message =news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...
>I have the following tables:
> > tblAccount:
> -Account
> > tblAmount:
> -ProjectID
> -Account
> -Amount1
> -Amount2
> > tblOrder:
> -OrderID
> -ProjectID
> -Account
> -Amount
> > tblTransaction:
> -TransactionID
> -ProjectID
> -Account
> -Amount
> > I would like to show all accounts in tblAccount and if there are =amount
> values on the accounts in the other tables they should be shown next =to
> the account number. If there are no values in the other tables the
> account without value should still be shown.
> > Which is the best way to do this, a view or sp and with which syntax?
> > Regards,
> > S
>
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Something like:
SELECT
=ProjectID,
=Account,
OrderAmt =3D =isnull(( SELECT sum( Amount ) FROM tblOrder WHERE Account =3D a.Account GROUP BY =Account ), 0 )
TransAmt =3D =isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE Account =3D a.Account GROUP BY =Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends =upon how you will use this, and how often you will use it.
On Another Note: [tbl] as a table =prefix is 'old school'. Actually 3 wasted keystrokes since they provide no additional =value. (Make every keystroke useful.) You know it is a table because it follows =the FROM keyword.
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
wrote in message news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...>I =have the following tables:> > tblAccount:> -Account> => tblAmount:> -ProjectID> -Account> -Amount1> -Amount2> > tblOrder:> -OrderID> =-ProjectID> -Account> -Amount> > tblTransaction:> -TransactionID> -ProjectID> -Account> =-Amount> > I would like to show all accounts in tblAccount and if there =are amount> values on the accounts in the other tables they should be =shown next to> the account number. If there are no values in the other =tables the> account without value should still be shown.> => Which is the best way to do this, a view or sp and with which syntax?> => Regards,> > S>

---

--=_NextPart_000_1050_01C6EBFD.FFAA9360--