Prev: Order By String Problem
Next: deleting a fields content
From: SQL Programmer on 23 May 2010 22:37
Hello:

Below is my query, and I need help with two things.

First, I am trying to get each field in this query to be concatenated
together. But, there is a space between the field "CMTrxNum" and "CASE WHEN
TRXAMNT....". How do I get rid of this spacing?

Secondly, I need for the two fields that contain the phrases
"CAST(-1*TRXAMNT as varchar)" and "CAST(TRXAMNT as varchar)" to be two
decimal places. How do I do this?

select '2000038028654' + '0000' + CMTrxNum + CASE WHEN TRXAMNT <0 THEN
CAST(-1 * TRXAMNT as varchar)
ELSE CAST(TRXAMNT as varchar) END
+ convert(varchar, TRXDATE, 112)
+ CASE WHEN VOIDED = '1' then 'V' ELSE '' END
from CM20200
where CHEKBKID = 'UPTOWN TRUST' and SOURCDOC = 'PMPAY' or SOURCDOC = 'PMCHK'

SQL Programmer (it's just a name)
From: Plamen Ratchev on 23 May 2010 23:06
It is good practice to always specify the VARCHAR length. Try this:

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');

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Order By String Problem
Next: deleting a fields content