Case Statement Really Urgent Please

22 Dec 2005 4:53 PM

marcmc

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

22 Dec 2005 5:00 PM

marcmc

or what is that padding function
lpad or something??

Show quote

"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','GEI/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')

22 Dec 2005 5:13 PM

Jens

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

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.

22 Dec 2005 5:29 PM

marcmc

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 '
??

Show quote

"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','GEÂ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.
>
>

22 Dec 2005 5:37 PM

Jens

Thats easy:

DECLARE @SOMEVALUE VARCHAR(20)
SET @SOMEVALUE = '998'

SET @SOMEVALUE = RIGHT('0000000' + @SOMEVALUE,7)
PRINT @SOMEVALUE

HTH, Jens Suessmeyer.

22 Dec 2005 6:52 PM

Joe from WI

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

Show quote

"Jens" wrote:

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

22 Dec 2005 5:38 PM

Trey Walpole

e.g., pad resulting length of 6

right(replicate('0',6) + convert(varchar, '93826'), 6)

marcmc wrote:
Show quote

> 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','GEÂ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.
>>
>>

22 Dec 2005 5:43 PM

Trey Walpole

ah - missed the char, have to trim it first

right(replicate('0',6) + convert(varchar, rtrim('93826 ')), 6)

Trey Walpole wrote:
Show quote

> 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 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','GEÂ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.
>>>
>>>