Case Statement Really Urgent Please

Why will this case statement not work?
It checks the length of a char field TRAN_TIME and pads it accordingly.
SELECT a.TRAN_TIME
FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock)
WHERE a.POLICY_CODE = b.Policy_desc
AND a.TRAN_DT >= '20051221'
AND (CASE len(a.TRAN_TIME) When 5 THEN '0' + a.TRAN_TIME When 4 Then '00' +
a.TRAN_TIME ELSE a.TRAN_TIME END) > '172206'
AND a.TRAN_TIME > '172206'
and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','GEI
/QMP/2294534',
'GEI/QMP/2294527','GEI/QMV/0263087')or what is that padding function
lpad or something'
"marcmc" wrote:

> Why will this case statement not work?
> It checks the length of a char field TRAN_TIME and pads it accordingly.
> SELECT a.TRAN_TIME
> FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock)
> WHERE a.POLICY_CODE = b.Policy_desc
> AND a.TRAN_DT >= '20051221'
> AND (CASE len(a.TRAN_TIME) When 5 THEN '0' + a.TRAN_TIME When 4 Then '00'
+
> a.TRAN_TIME ELSE a.TRAN_TIME END) > '172206'
> AND a.TRAN_TIME > '172206'
> and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
> 'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','G
EI/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')|||SELECT a.TRAN_TIME
FROM
QUINN_ST..get_bcp_h_cvcmis a(nolock),
QUINN_CT..Rec_Pol_WH b(nolock)
WHERE a.POLICY_CODE =3D b.Policy_desc
AND a.TRAN_DT >=3D '20051221'
AND (
CASE len(a.TRAN_TIME)
When 5 THEN '0' + a.TRAN_TIME
When 4 Then '00' + a.TRAN_TIME
ELSE a.TRAN_TIME END
) > '172206'
AND a.TRAN_TIME > '172206'
and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','GE=
=ADI/QMP/2294534',
'GEI/QMP/2294527','GEI/QMV/0263087')
Syntactically it looks OK, but there could be some conversion issue
depending on your datatypes of the table (which you didn=B4t sned in a
ddl script with the issue)
BTW: What do you mean by lpad ?
HTH, jens Suessmeyer.|||lpad was an oracle padding func i was thinking of, how can i pad one or two
zeroes to the left of lets say the char '93826 '
'
"Jens" wrote:

> SELECT a.TRAN_TIME
> FROM
> QUINN_ST..get_bcp_h_cvcmis a(nolock),
> QUINN_CT..Rec_Pol_WH b(nolock)
> WHERE a.POLICY_CODE = b.Policy_desc
> AND a.TRAN_DT >= '20051221'
> AND (
> CASE len(a.TRAN_TIME)
> When 5 THEN '0' + a.TRAN_TIME
> When 4 Then '00' + a.TRAN_TIME
> ELSE a.TRAN_TIME END
> ) > '172206'
> AND a.TRAN_TIME > '172206'
> and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
> 'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','G
E_I/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')
> Syntactically it looks OK, but there could be some conversion issue
> depending on your datatypes of the table (which you didn′t sned in a
> ddl script with the issue)
> BTW: What do you mean by lpad ?
> HTH, jens Suessmeyer.
>|||e.g., pad resulting length of 6
right(replicate('0',6) + convert(varchar, '93826'), 6)
marcmc wrote:
> lpad was an oracle padding func i was thinking of, how can i pad one or tw
o
> zeroes to the left of lets say the char '93826 '
> '
> "Jens" wrote:
>|||Thats easy:
DECLARE @.SOMEVALUE VARCHAR(20)
SET @.SOMEVALUE = '998'
SET @.SOMEVALUE = RIGHT('0000000' + @.SOMEVALUE,7)
PRINT @.SOMEVALUE
HTH, Jens Suessmeyer.|||ah - missed the char, have to trim it first
right(replicate('0',6) + convert(varchar, rtrim('93826 ')), 6)
Trey Walpole wrote:
> e.g., pad resulting length of 6
> right(replicate('0',6) + convert(varchar, '93826'), 6)
> marcmc wrote:
>|||combining Trey's and Jen's suggestions...
If your input is character data, you should probably trim spaces from both
sides unless you know and can rely on the incoming data.
RIGHT('000000' + LTRIM(RTRIM(@.SOMEVALUE)), 6)
if you need it to be dynamic, use the replicate version
RIGHT(REPLICATE('0', @.PadSize) + LTRIM(RTRIM(@.SomeValue)), @.PadSize)
if it's numeric data,
RIGHT(REPLICATE('0', @.PadSize) + CONVERT(VARCHAR, @.SomeNumeric), @.PadSize)
if you prefer CAST instead of CONVERT, that works too.
Hope that helps,
Joe
"Jens" wrote:

> Thats easy:
> DECLARE @.SOMEVALUE VARCHAR(20)
> SET @.SOMEVALUE = '998'
> SET @.SOMEVALUE = RIGHT('0000000' + @.SOMEVALUE,7)
> PRINT @.SOMEVALUE
>
> HTH, Jens Suessmeyer.
>