Prev: How to pivot this in SQL 2K
Next: Help with DateTimeOffset and Floor/Ceiling?
From: SQL Programmer on 25 May 2010 08:13
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: --CELKO-- on 25 May 2010 08:23
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.
From: Dan Guzman on 25 May 2010 08:46
> 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: Iain Sharp on 25 May 2010 09:09
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: SQL Programmer on 25 May 2010 09:38
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.
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: How to pivot this in SQL 2K
Next: Help with DateTimeOffset and Floor/Ceiling?