Prev: How to pivot this in SQL 2K
Next: Help with DateTimeOffset and Floor/Ceiling?
From: SQL Programmer on 25 May 2010 09:39
Thanks, Dan! Unlike this "CELKO" person, whatever that name means, I'm glad
that there are some kind and patient people on this newsgroup.

"Dan Guzman" wrote:

> > Below is my query, and I need help with concatenating. I am trying to get
> > each field in this query to be concatenated together. But, the results of
> > running this query displays a huge space between the field "CMTrxNum" and
> > "CASE WHEN
> > TRXAMNT....". How do I get rid of this spacing?
>
> Perhaps CMTrxNum is char rather than varchar. Try casting as varchar and
> trimming as needed. For example:
>
> SELECT '2000038028654' + '0000' + LTRIM(RTRIM(CAST(CMTrxNum AS
> varchar(20)))) +
> CASE WHEN TRXAMNT < 0 THEN LTRIM(STR(-1 * TRXAMNT, 20, 2))
> ELSE LTRIM(STR(TRXAMNT, 20, 2)) END
> + CONVERT(VARCHAR(8), TRXDATE, 112) +
> CASE WHEN VOIDED = '1' THEN 'V' ELSE '' END
> ...
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "SQL Programmer" <SQLProgrammer(a)discussions.microsoft.com> wrote in message
> news:6559114E-87D4-4C8D-99E3-F95D53E92CB4(a)microsoft.com...
> > Hello:
> >
> > Below is my query, and I need help with concatenating. I am trying to get
> > each field in this query to be concatenated together. But, the results of
> > running this query displays a huge space between the field "CMTrxNum" and
> > "CASE WHEN
> > TRXAMNT....". How do I get rid of this spacing?
> >
> >
> > SELECT '2000038028654' + '0000' + CMTrxNum +
> > CASE WHEN TRXAMNT < 0 THEN LTRIM(STR(-1 * TRXAMNT, 20, 2))
> > ELSE LTRIM(STR(TRXAMNT, 20, 2)) END
> > + CONVERT(VARCHAR(8), TRXDATE, 112) +
> > CASE WHEN VOIDED = '1' THEN 'V' ELSE '' END
> > FROM CM20200
> > WHERE CHEKBKID = 'UPTOWN TRUST'
> > AND SOURCDOC IN ('PMPAY', 'PMCHK')
> >
> > SQL Programmer (it's just a name)
>
From: SQL Programmer on 25 May 2010 10:07
Thank you, Iain! This worked perfectly!

It's refreshing to know that there are those that will help out rather than
stalk.

SQL Programmer (it's just a name and let me know if I can refer CELKO for
counseling)

"Iain Sharp" wrote:

> On Tue, 25 May 2010 05:23:26 -0700 (PDT), --CELKO--
> <jcelko212(a)earthlink.net> wrote:
>
> >Again, fields and columns are nothing alike and formatting is done in
> >the front end and not the database. After all these months, you still
> >have no idea what RDBMS and SQL are about.
>
> However, to answer your query, rather than just ranting.
>
> SELECT '2000038028654' + '0000' + rtrim(CMTrxNum) +
> CASE WHEN TRXAMNT < 0 THEN LTRIM(STR(-1 * TRXAMNT, 20, 2))
> ELSE LTRIM(STR(TRXAMNT, 20, 2)) END
> + CONVERT(VARCHAR(8), TRXDATE, 112) +
> CASE WHEN VOIDED = '1' THEN 'V' ELSE '' END
> FROM CM20200
> WHERE CHEKBKID = 'UPTOWN TRUST'
> AND SOURCDOC IN ('PMPAY', 'PMCHK')
> .
>
From: --CELKO-- on 25 May 2010 14:25
On May 25, 8:38 am, SQL Programmer
<SQLProgram...(a)discussions.microsoft.com> wrote:
> I'm sorry that your parents didn't bother teaching you common courtesy.  
> Examples of what you just wrote come from upbringing, I guess.
>
> "--CELKO--" wrote:
> > Again, fields and columns are nothing alike and formatting is done in
> > the front end and not the database.  After all these months, you still
> > have no idea what RDBMS and SQL are about.
> > .

First  |  Prev  | 
Pages: 1 2
Prev: How to pivot this in SQL 2K
Next: Help with DateTimeOffset and Floor/Ceiling?